Posts

Showing posts with the label Script

Migrate SQLPrompt Snippets to VSCode

 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr...

SQL Script: Size of all tables on all databases

Image
Sometimes I get emails from dbas saying our database is filling up and I want to know where the biggest problems are. I'm sure there are some DBA tools to help with that, but I find a really quick easy way is to execute sp_executesql for every table in every database. Here's a script you can run to  get all table sizes on all databases . It basically loops over all database (except those you don't care about, e.g. master, msdn, etc.) and gets the table sizes for each table. At the end, you can then aggregate things by database, or drill into a specific database for table-level details. set nocount, xact_abort on declare @ExclDatabases nvarchar( max ) = 'master|msdb|tempdb|ssisdb|model' , @Schemas nvarchar( max ) = null drop table if exists #AllTabs create table #AllTabs ( DbName nvarchar(128), SchemaName nvarchar(128), TableName nvarchar(128), ObjectId int, RowCt bigint, ReservedKB bigint, DataKB bigint, IndexSizeKB...

SSIS File Access Denied in C# Script Task

 I had an SSIS package which, as its first step, needed to pull a list of files to process. I did that with a C# Script Task that did that with Directory.EnumerateFiles on a specific fileshare directory. When I executed it locally (either through the Visual Studio project, or through DTEXEC on my local machine) it worked just fine. However when I deployed it to an SSIS Catalog, it would throw an error saying  <Script Task name>: Error: Access to path "..." is denied Same result if I executed it through an agent job on the server. Turns out, I was able to execute it just fine if I were connected to the actual server the SQL Instance the SSIS Catalog runs on. Why does this happen? Hell if I know. But that's the way it is.  For the agent job, it just need to be created as the SA (or an account with a lot of permissions), which my DBA could do. So, if you can get permissions to temporarily log into your SQL box, you can at least verify it will work. Then, if you have yo...

Script: Dropping Temporal Tables

Temporal tables are awesome. But they're kind of a pain to work with during those initial stages of development when you're constantly dropping and re-creating objects whole. The process of dropping a temporal table isn't super complicated, just: Disable versioning on the main table Drop the main table Drop the history table To that end, I wrote this stored procedure, marked as a system object (using sp_msmarksystemobject) which you can build which will do those three tasks for you automatically. Even if you don't choose to use the full script, you can still de-construct it for the logic. use master go set nocount on go /***************************** PROC: dbo.sp_droptemporaltable create table Util.dbo.Test ( ID int primary key clustered, StartDate datetime2 generated always as row start, EndDate datetime2 generated always as row end, period for system_time (StartDate, EndDate) ) with (system_versioning = on (history_table = dbo.Test_History)) exec Uti...

Database Size Script

This just executes sp_spaceused on every table in a database, and plops the results into a temp table so you can query the data altogether. declare @TableName varchar(128), @RID int, @MaxRID int, @SQL nvarchar(max) if object_id('tempdb.dbo.#LoopSrc') is not null drop table #LoopSrc create table #LoopSrc ( RID int identity(1,1) primary key clustered, TableName varchar(128) ) if object_id('tempdb.dbo.#Tabs') is not null drop table #Tabs create table #Tabs ( TableName varchar(128), nRows int, nReserved as cast(replace(sReserved, ' KB', '') as int), nData as cast(replace(sData, ' KB', '') as int), nIndexSize as cast(replace(sIndexSize, ' KB', '') as int), nUnused as cast(replace(sUnused, ' KB', '') as int), sReserved varchar(30), sData varchar(30), sIndexSize varchar(30), sUnused varchar(30) ) /***************************** *** INSERT LOOP ITEMS...

Script Out SQL Row Constructors

A while back I got tired of having to type out the long strings to script out rows from a table, so I built a snippet which does it for you. It’s almost illegible to read, but it determines the columns of the table you choose and builds a string to output each row. I'll admit that this could be further refined to add in some more precise data type handling (perhaps casting date fields to a certain varchar format, or handling more obscure data types), but for most every day purposes, this does just fine. Also, when you copy/paste the results into a query window, you'll have to manually cut out the first "union all" to get it to run. These omissions are just me being lazy, but there's no reason they can't be done, and if you want to make them or wrap this in a procedure, I encourage you to do so. use go set nocount on go declare @string nvarchar(max) = ( --This creates a framework for each column in the chosen table, which then gets serialized and app...

Regular Expressions in SSMS

Image
Several times a year I get asked something like "Can SQL do regular expressions"? The answer is essentially no (I know, I know, CLRs). But while SQL statements can't make sure of regular expressions to search through table data, you CAN use them in the course of creating and editing SQL scripts. SQL Server Management Studio as well as Visual Studio, which the later versions of SQL Server use as their IDE support regular expressions in find-and-replace operations. If you're wholly unfamiliar with regular expressions, there are tons of resources out there to help you learn. Some good info can be found at www.regular-expressions.info . I personally bought a book called Introducing Regular Expressions which I found quite useful. It's not my intent to teach regular expression here, but to show you how to find a regex character you need and give you some find-and-replace regular expressions I use almost daily. First things first, SQL Server uses some funky notation...