Let SQL Server Write Code for You

A lot of DBAs would spend hours (and even days) sitting in front of their computer to write a stupendous amount of lines of code… Then take care of a lot of copy-paste and typing errors… Test the code and find errors… Fix the code… Run it again… And so on. All the while having to navigate within a huge forest of code.

I would presume that it doesn’t take a special kind of mind to stand up and say “just hold on a minute! Why am I working so hard with this mundane task?? Why can’t SQL Server do all of this for me??”

Because in fact… It can!

If you were working with SQL Server for more than a couple months, most probably you’ve heard about ‘Dynamic SQL’, right? I personally covered several topics about it in my blog through the years.

Also, I hope you heard about the huge list of ‘system tables’ and ‘catalog views’ that SQL Server has to offer, right?

And finally, you should have also heard that you can concatenate a list of values using queries, right?

If you haven’t yet, then now is your chance. Because we’re about to bring all of these elements together to make SQL Server do your mundane work for you! [cue applause]

Simple Concept Example: Auto-Generate Insertion Procedures

Here’s a basic scenario to demonstrate the concept for what I’m talking about.

The task goes like this:

You have a list of tables, and you need to generate a simple insertion procedure per each of the tables. Each stored procedure should receive as parameters the values for each of the table columns, except the IDENTITY column.

Alright, so first, let’s take one such table as an example and see how such a procedure should look like.

This is the table we’ll use for our example:

CREATE TABLE [dbo].[Invoices](
 [InvoiceID] int IDENTITY(1,1) PRIMARY KEY,
 [Title] nvarchar(50),
 [OrderDate] date,
 [Qty]  int,
 [Total] money
)

An insertion procedure for such a table would look like this:

CREATE PROCEDURE [InvoicesInsert]
 @Title nvarchar(50),
 @OrderDate date,
 @Qty  int,
 @Total money
AS

INSERT INTO [dbo].[Invoices] ([Title], [OrderDate], [Qty], [Total])
VALUES(@Title, @OrderDate, @Qty, @Total)

All right, now let’s try and break it down and isolate the per-table dynamic parts:

CREATE PROCEDURE [{TableName}Insert]
 {ColumnParametersListWithTypes}
AS

INSERT INTO [{TableName}] ({ColumnsList})
VALUES({ColumnParametersList})

Yeah, this looks much simpler. We’ll call this our ‘template’.

Now let’s review each dynamic part and see how we can generate them:

1. {TableName} This one should be a no-brainer, seeing as we’ll be receiving this as a pre-set value.
2. {ColumnsList} This one should be fairly simple to get. We can use the sys.columns catalog view to get the list of columns per each table. We’ll just need to concatenate them all with commas between them and we’re set. Also, the is_identity column in the sys.columns catalog view will help us determine which of the columns has an IDENTITY property.
3. {ColumnParametersList} This one is very similar to {ColumnsList}, except we just need to add the @ symbol before each column.
4. {ColumnParametersListWithTypes} This one is a little trickier, because it needs to include the relevant data type of each column, and not just the name. No fear, though! The sys.types catalog view, and some CASE WHEN scripting will help us here.

Right, so let’s go right in and see how we can implement this:

DECLARE @ColumnsList NVARCHAR(MAX);
DECLARE @ColumnParametersList NVARCHAR(MAX);
DECLARE @ColumnParametersListWithTypes NVARCHAR(MAX);

SELECT
@ColumnsList = ISNULL(@ColumnsList + N', ', N'') + QUOTENAME(c.name),
@ColumnParametersList = ISNULL(@ColumnParametersList + N', ', N'') + '@' + REPLACE(c.name, ' ', ''),
@ColumnParametersListWithTypes = ISNULL(@ColumnParametersListWithTypes + N', ', N'') + '@' + REPLACE(c.name, ' ', '')
+ N' ' 
+ CASE WHEN t.name IN
('char','varchar','nchar','nvarchar','varbinary','binary')
THEN t.name + '(' +
	CASE WHEN c.max_length=-1 THEN 'MAX'
	ELSE CONVERT(VARCHAR(4),
			CASE WHEN t.name IN ('nchar','nvarchar')
			THEN c.max_length/2 ELSE c.max_length END )
	END + ')'
WHEN t.name IN ('decimal','numeric')
THEN t.name + '('
+ CONVERT(VARCHAR(4),c.precision) + ','
+ CONVERT(VARCHAR(4),c.Scale)
+ ')'
ELSE t.name
END

FROM sys.columns AS c
INNER JOIN sys.types AS t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = object_id(@TableName)
AND c.is_identity = 0
ORDER BY c.column_id

PRINT @ColumnsList
PRINT @ColumnParametersList
PRINT @ColumnParametersListWithTypes

This script uses concatenation and queries from a couple catalog views, to create 3 lists of values.

If we input ‘Invoices’ in our @TableName variable, we’ll get the following output:

[Title], [OrderDate], [Qty], [Total]
@Title, @OrderDate, @Qty, @Total
@Title nvarchar(50), @OrderDate date, @Qty int, @Total money

Pretty neat, huh?

Right, so now let’s bring it all together by building the relevant TSQL commands:

DECLARE @DropCommand NVARCHAR(MAX)
DECLARE @CreateCommand NVARCHAR(MAX)

SET @DropCommand =
N'IF OBJECT_ID(''' + QUOTENAME(@TableName + 'Insert') + N''') IS NOT NULL
DROP PROCEDURE ' + QUOTENAME(@TableName + 'Insert')

SET @CreateCommand =
N'CREATE PROCEDURE ' + QUOTENAME(@TableName + 'Insert') + N'
' + @ColumnParametersListWithTypes + N'
AS
INSERT INTO ' + QUOTENAME(@TableName) + N' (' + @ColumnsList + N')
VALUES (' + @ColumnParametersList + N')'

PRINT @DropCommand
PRINT 'GO'
PRINT @CreateCommand
PRINT 'GO'

And the output for our table would be:

IF OBJECT_ID('[InvoicesInsert]') IS NOT NULL
 DROP PROCEDURE [InvoicesInsert]
GO
CREATE PROCEDURE [InvoicesInsert]
 @Title nvarchar(50), @OrderDate date, @Qty int, @Total money
AS
INSERT INTO [Invoices] ([Title], [OrderDate], [Qty], [Total])
VALUES (@Title, @OrderDate, @Qty, @Total)
GO

Great success! So now we just need to implement this logic in a cursor which traverses several tables, and we’re done. You can see the full sample script here:

DECLARE @TableName SYSNAME

DECLARE TablesCur CURSOR
LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT name
FROM sys.tables
WHERE name IN
(
'InvoiceHeader',
'InvoiceRows',
'Invoices'
)

OPEN TablesCur

FETCH NEXT FROM TablesCur INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @ColumnsList NVARCHAR(MAX);
DECLARE @ColumnParametersList NVARCHAR(MAX);
DECLARE @ColumnParametersListWithTypes NVARCHAR(MAX);

SET @ColumnsList = NULL
SET @ColumnParametersList = NULL
SET @ColumnParametersListWithTypes = NULL

SELECT
@ColumnsList = ISNULL(@ColumnsList + N', ', N'') + QUOTENAME(c.name),
@ColumnParametersList = ISNULL(@ColumnParametersList + N', ', N'') + '@' + REPLACE(c.name, ' ', ''),
@ColumnParametersListWithTypes = ISNULL(@ColumnParametersListWithTypes + N', ', N'') + '@' + REPLACE(c.name, ' ', '')
+ N' ' + CASE
WHEN t.name IN
('char','varchar','nchar','nvarchar','varbinary','binary')
THEN t.name + '('+
CASE WHEN c.max_length=-1 THEN 'MAX'
ELSE CONVERT(VARCHAR(4),
CASE WHEN t.name IN ('nchar','nvarchar')
THEN c.max_length/2 ELSE c.max_length END )
END + ')'

WHEN t.name IN ('decimal','numeric')
THEN t.name + '('
+ CONVERT(VARCHAR(4),c.precision) + ','
+ CONVERT(VARCHAR(4),c.Scale)
+ ')'
ELSE t.name END
FROM sys.columns AS c
INNER JOIN sys.types AS t
ON c.user_type_id = t.user_type_id
where
c.object_id = object_id(@TableName)
AND c.is_identity = 0
ORDER BY c.column_id

DECLARE @DropCommand NVARCHAR(MAX)
DECLARE @CreateCommand NVARCHAR(MAX)

SET @DropCommand =
N'IF OBJECT_ID(''' + QUOTENAME(@TableName + 'Insert') + N''') IS NOT NULL
DROP PROCEDURE ' + QUOTENAME(@TableName + 'Insert')

SET @CreateCommand =
N'CREATE PROCEDURE ' + QUOTENAME(@TableName + 'Insert') + N'
' + @ColumnParametersListWithTypes + N'
AS
INSERT INTO ' + QUOTENAME(@TableName) + N' (' + @ColumnsList + N')
VALUES (' + @ColumnParametersList + N')'

PRINT @DropCommand
PRINT 'GO'
PRINT @CreateCommand
PRINT 'GO'

FETCH NEXT FROM TablesCur INTO @TableName
END

CLOSE TablesCur
DEALLOCATE TablesCur

Note that you can use similar methods to generate UPDATE and DELETE procedures as well, just to name a few. The catalog views sys.indexes and sys.index_columns will be helpful for determining the primary key column(s) per each table.

Real-Life Case Study: Copy a List of Tables via Linked Server

So now let’s dive right in, and review an even more complex example of a real-life scenario that I’ve personally encountered:

Company X has requested to build a script that would incrementally copy a list of tables from one SQL Server to another using a Linked Server connection. The incremental copy should be done using a numerical incremental primary column which exists in every table. Also, if a table doesn’t exist yet in the destination database, it should be automatically created.

There are several key elements of SQL Server which we’re going to leverage for our benefit here:

• Dynamic SQL
• System Catalog Views
• Concatenation
• Values Constructor

Here’s what I did:

First, I initialized the following cursor:

DECLARE @Table SYSNAME, @PKColumn SYSNAME, @PKColumnType SYSNAME;
DECLARE Cur CURSOR FOR
SELECT
 TableName
 , PKColumn  = QUOTENAME(c.name)
 , PKColumnType = t.name
   + CASE WHEN t.name IN ('decimal','numeric')
              THEN '('+ CONVERT(VARCHAR(4),c.precision)+','
                      + CONVERT(VARCHAR(4),c.Scale)+')'
              ELSE '' END
FROM
(VALUES
 ('Table1'),
 ('Table2'), 
 ('Table3'), 
 ('Table4'), 
 ('Table5')
) AS A(TableName)
INNER JOIN
 sys.indexes AS i
ON
 i.object_id = OBJECT_ID(TableName)
INNER JOIN
 sys.index_columns AS ic
ON
 i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
INNER JOIN
 sys.columns AS c
ON
 i.object_id = c.object_id
AND ic.column_id = c.column_id
INNER JOIN
 sys.types AS t
ON
 t.system_type_id = c.system_type_id
WHERE
 i.is_primary_key = 1
ORDER BY TableName

Note how this cursor makes use of the ‘Values Constructor’ by specifically preparing a list of values to be used as table names, and then joins it with various catalog views in order to find the name of the primary key column per each table. Also note that I don’t necessarily know the data type of each primary column in every table, it can be either Int or Bigint or Decimal or Numeric. If a column is Decimal/Numeric, its scale and precision would also need to be known (that’s the reason for the CASE statement above).

Here’s a sample result of such a cursor from my sandbox database:

Now that I have myself a list of table names, the names of the primary key column per each, and even its type, I’m ready to start building the commands themselves.

First, I found some ready-made dynamic table creation script on the internet, cleaned it up a bit, and adjusted it for my own script (it’s the same one I used for generating the column types in the previous example):

OPEN Cur
FETCH NEXT FROM Cur INTO @Table, @DoChunks, @PKColumn, @PKColumnType

WHILE @@FETCH_STATUS = 0
BEGIN
-- if table not exists, create it
DECLARE @CMD NVARCHAR(MAX), @ColumnInsertionList NVARCHAR(MAX)
SET @CMD = NULL;
SET @ColumnInsertionList = NULL;

SELECT @CMD = ISNULL(@CMD + N',
', N'') + QUOTENAME(c.name)
+ N' '
+ CASE
WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN t.name + '('+
CASE WHEN c.max_length=-1 THEN 'MAX'
ELSE CONVERT(VARCHAR(4),
CASE WHEN t.name IN ('nchar','nvarchar')
THEN c.max_length/2 ELSE c.max_length END )
END +')'
WHEN t.name IN ('decimal','numeric')
THEN t.name + '('+ CONVERT(VARCHAR(4),c.precision)+','
+ CONVERT(VARCHAR(4),c.Scale)+')'
ELSE t.name END
+ CASE WHEN c.is_nullable=0 THEN ' NOT NULL' ELSE ' NULL' END
+ CASE WHEN c.default_object_id 0
THEN ' DEFAULT '+object_Definition(c.default_object_id) ELSE '' END
+ CASE WHEN c.collation_name IS NULL THEN ''
WHEN c.collation_name
(SELECT collation_name FROM sys.databases
WHERE name=DB_NAME()) COLLATE Latin1_General_CI_AS
THEN COALESCE(' COLLATE '+c.collation_name,'') ELSE '' END

, @ColumnInsertionList = ISNULL(@ColumnInsertionList + N', ', N'') + QUOTENAME(c.name)
FROM
sys.columns AS c
INNER JOIN
sys.types AS t
ON
t.system_type_id = c.system_type_id
WHERE object_id = OBJECT_ID(@Table)
ORDER BY column_id ASC

SET @CMD = N'USE MyTargetDB;
IF OBJECT_ID(''' + @Table + N''') IS NULL
CREATE TABLE ' + QUOTENAME(@Table) + N'(
' + @CMD + N'
);'
EXECUTE (@CMD) AT [MyTargetLinkedServer]

Note several important things here:

• The variable @CMD is populated by concatenating values into it [ note the use of ISNULL(@CMD + N', ', N'') + QUOTENAME(c.name) ]
• The variable @ColumnInsertionList is populated similarly to the previous example.
• Note the use of system tables here again for getting the column configurations.
• Note the use of EXECUTE (@CMD) AT [MyTargetLinkedServer], which is used for executing the dynamic SQL command on a linked server (called “MyTargetLinkedServer” here).

Here’s a sample output of such a script:

USE MyTargetDB;
IF OBJECT_ID('InvoiceHeader') IS NULL
 CREATE TABLE [InvoiceHeader](
 [InvoiceId] int NOT NULL,
 [SalesPerson] nvarchar(MAX) NULL,
 [SalesPerson] sysname NULL,
 [Job] nvarchar(MAX) NULL,
 [Job] sysname NULL,
 [ShippingMethod] nvarchar(MAX) NULL,
 [ShippingMethod] sysname NULL,
 [ShippingTerms] nvarchar(MAX) NULL,
 [ShippingTerms] sysname NULL,
 [DeliveryDate] datetime NULL,
 [PaymentTerms] nvarchar(MAX) NULL,
 [PaymentTerms] sysname NULL,
 [DueDate] datetime NULL,
 [InvoiceNumber] nvarchar(50) NULL,
 [InvoiceNumber] sysname NULL,
 [InvoiceDate] nvarchar(50) NULL,
 [InvoiceDate] sysname NULL,
 [CustomerID] nvarchar(50) NULL,
 [CustomerID] sysname NULL,
 [ExpireDate] nvarchar(50) NULL,
 [ExpireDate] sysname NULL
 );

See how SQL Server generated the creation script for me? And I can do this for as many tables as I want!

Next up, is building the incremental copy script:

-- Copy data into table by Chunks
SET @CMD = N'
DECLARE @ChunkStart ' + @PKColumnType + N' = 0
DECLARE @ChunkEnd ' + @PKColumnType + N'
DECLARE @ChunkFinish ' + @PKColumnType + N'

SELECT @ChunkStart = ISNULL(MAX(' + @PKColumn + N'), 0)
FROM [MyTargetLinkedServer].MyTargetDB.dbo.' + QUOTENAME(@Table) + N'

SELECT @ChunkFinish = MAX(' + @PKColumn + N'), @ChunkStart = CASE WHEN @ChunkStart = 0 THEN MIN(' + @PKColumn + N')-1 ELSE @ChunkStart END
FROM ' + QUOTENAME(@Table) + N'

WHILE @ChunkStart < @ChunkFinish
BEGIN
SET @ChunkEnd = @ChunkStart + @ChunkInterval;

INSERT INTO [MyTargetLinkedServer].MyTargetDB.dbo.' + QUOTENAME(@Table) + N'(' + @ColumnInsertionList + N')
SELECT ' + @ColumnInsertionList + N'
FROM ' + QUOTENAME(@Table) + N'
WHERE
' + @PKColumn + N' > @ChunkStart
AND ' + @PKColumn + N' <= @ChunkEnd

SELECT @ChunkStart = MIN(' + @PKColumn + N')
FROM ' + QUOTENAME(@Table) + N'
WHERE ' + @PKColumn + N' >= @ChunkEnd
END
'

DECLARE @Params NVARCHAR(MAX) = N'@ChunkInterval bigint'
EXEC sp_executesql @CMD, @Params, 10000

This is a rather standard way of incrementally copying data from one table to another using an incremental primary key column.

Note that this dynamic script is built using the @ColumnInsertionList which we have prepared earlier using concatenation and system tables, and the @PKColumn variable which we got from the cursor.

Here’s a sample output of such a script:

DECLARE @ChunkStart int = 0
DECLARE @ChunkEnd int
DECLARE @ChunkFinish int
SELECT @ChunkStart = ISNULL(MAX([InvoiceId]), 0)
FROM [MyTargetLinkedServer].MyTargetDB.dbo.[InvoiceHeader]
SELECT @ChunkFinish = MAX([InvoiceId]), @ChunkStart = CASE WHEN @ChunkStart = 0 THEN MIN([InvoiceId])-1 ELSE @ChunkStart END
FROM [InvoiceHeader]
WHILE @ChunkStart < @ChunkFinish
BEGIN
SET @ChunkEnd = @ChunkStart + @ChunkInterval;
INSERT INTO [MyTargetLinkedServer].MyTargetDB.dbo.[InvoiceHeader]([InvoiceId], [SalesPerson], [SalesPerson], [Job], [Job], [ShippingMethod], [ShippingMethod], [ShippingTerms], [ShippingTerms], [DeliveryDate], [PaymentTerms], [PaymentTerms], [DueDate], [InvoiceNumber], [InvoiceNumber], [InvoiceDate], [InvoiceDate], [CustomerID], [CustomerID], [ExpireDate], [ExpireDate])
SELECT [InvoiceId], [SalesPerson], [SalesPerson], [Job], [Job], [ShippingMethod], [ShippingMethod], [ShippingTerms], [ShippingTerms], [DeliveryDate], [PaymentTerms], [PaymentTerms], [DueDate], [InvoiceNumber], [InvoiceNumber], [InvoiceDate], [InvoiceDate], [CustomerID], [CustomerID], [ExpireDate], [ExpireDate]
FROM [InvoiceHeader]
WHERE
[InvoiceId] > @ChunkStart
AND [InvoiceId] <= @ChunkEnd
SELECT @ChunkStart = MIN([InvoiceId])
FROM [InvoiceHeader]
WHERE [InvoiceId] >= @ChunkEnd
END

Once again, SQL Server generated the full script for me and now it can be done automatically per each table, and all I had to write is one template!

Next, we simply close the loop and clean up the cursor object:

 FETCH NEXT FROM Cur INTO @Table, @PKColumn, @PKColumnType
END

CLOSE Cur
DEALLOCATE Cur

Nothing to explain here if you’re already familiar with cursors.

That’s it! You can see the full script below:

DECLARE @ChunkInterval BIGINT = 10000;

DECLARE @Table SYSNAME, @PKColumn SYSNAME, @PKColumnType SYSNAME;
DECLARE Cur CURSOR FOR
SELECT
TableName
, PKColumn = QUOTENAME(c.name)
, PKColumnType = t.name
+ CASE WHEN t.name IN ('decimal','numeric')
THEN '('+ CONVERT(VARCHAR(4),c.precision)+','
+ CONVERT(VARCHAR(4),c.Scale)+')'
ELSE '' END
FROM
(VALUES
('InvoiceHeader'),
('InvoiceRows'),
('Invoices'),
('SimpleFileStorage'),
('FileStorage')
) AS A(TableName)
INNER JOIN
sys.indexes AS i
ON
i.object_id = OBJECT_ID(TableName)
INNER JOIN
sys.index_columns AS ic
ON
i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
INNER JOIN
sys.columns AS c
ON
i.object_id = c.object_id
AND ic.column_id = c.column_id
INNER JOIN
sys.types AS t
ON
t.system_type_id = c.system_type_id
WHERE
i.is_primary_key = 1
ORDER BY TableName

OPEN Cur
FETCH NEXT FROM Cur INTO @Table, @PKColumn, @PKColumnType

WHILE @@FETCH_STATUS = 0
BEGIN
-- if table not exists, create it
DECLARE @CMD NVARCHAR(MAX), @ColumnInsertionList NVARCHAR(MAX)
SET @CMD = NULL;
SET @ColumnInsertionList = NULL;

SELECT @CMD = ISNULL(@CMD + N',
', N'') + QUOTENAME(c.name)
+ N' '
+ CASE
WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN t.name + '('+
CASE WHEN c.max_length=-1 THEN 'MAX'
ELSE CONVERT(VARCHAR(4),
CASE WHEN t.name IN ('nchar','nvarchar')
THEN c.max_length/2 ELSE c.max_length END )
END +')'

WHEN t.name IN ('decimal','numeric')
THEN t.name + '('
+ CONVERT(VARCHAR(4),c.precision) + ','
+ CONVERT(VARCHAR(4),c.Scale)
+ ')'
ELSE t.name END
+ CASE WHEN c.is_nullable=0 THEN ' NOT NULL' ELSE ' NULL' END
+ CASE WHEN c.default_object_id 0
THEN ' DEFAULT '+object_Definition(c.default_object_id) ELSE '' END
+ CASE WHEN c.collation_name IS NULL THEN ''
WHEN c.collation_name
(SELECT collation_name FROM sys.databases
WHERE name=DB_NAME()) COLLATE Latin1_General_CI_AS
THEN COALESCE(' COLLATE '+c.collation_name,'') ELSE '' END

, @ColumnInsertionList = ISNULL(@ColumnInsertionList + N', ', N'') + QUOTENAME(c.name)
FROM
sys.columns AS c
INNER JOIN
sys.types AS t
ON
t.system_type_id = c.system_type_id
WHERE object_id = OBJECT_ID(@Table)
ORDER BY column_id ASC

SET @CMD = N'USE MyTargetDB;
IF OBJECT_ID(''' + @Table + N''') IS NULL
CREATE TABLE ' + QUOTENAME(@Table) + N'(
' + @CMD + N'
);'

RAISERROR(@CMD,0,1) WITH NOWAIT;

EXECUTE (@CMD) AT [MyTargetLinkedServer]

-- Copy data into table by Chunks
SET @CMD = N'
DECLARE @ChunkStart ' + @PKColumnType + N' = 0
DECLARE @ChunkEnd ' + @PKColumnType + N'
DECLARE @ChunkFinish ' + @PKColumnType + N'

SELECT @ChunkStart = ISNULL(MAX(' + @PKColumn + N'), 0)
FROM [MyTargetLinkedServer].MyTargetDB.dbo.' + QUOTENAME(@Table) + N'

SELECT @ChunkFinish = MAX(' + @PKColumn + N'), @ChunkStart = CASE WHEN @ChunkStart = 0 THEN MIN(' + @PKColumn + N')-1 ELSE @ChunkStart END
FROM ' + QUOTENAME(@Table) + N'

WHILE @ChunkStart < @ChunkFinish
BEGIN
SET @ChunkEnd = @ChunkStart + @ChunkInterval;

INSERT INTO [MyTargetLinkedServer].MyTargetDB.dbo.' + QUOTENAME(@Table) + N'(' + @ColumnInsertionList + N')
SELECT ' + @ColumnInsertionList + N'
FROM ' + QUOTENAME(@Table) + N'
WHERE
' + @PKColumn + N' > @ChunkStart
AND ' + @PKColumn + N' <= @ChunkEnd

SELECT @ChunkStart = MIN(' + @PKColumn + N')
FROM ' + QUOTENAME(@Table) + N'
WHERE ' + @PKColumn + N' >= @ChunkEnd
END
'
RAISERROR(@CMD,0,1) WITH NOWAIT;

DECLARE @Params NVARCHAR(MAX) = N'@ChunkInterval bigint'
EXEC sp_executesql @CMD, @Params, @ChunkInterval

FETCH NEXT FROM Cur INTO @Table, @PKColumn, @PKColumnType
END

CLOSE Cur
DEALLOCATE Cur

Conclusion

In this case study I wrote about 100 lines of code which generated for me about a thousand lines of code that I didn’t need to write and manage on my own. The benefit here is HUGE.

This is just a couple examples of something you can do to make SQL Server write tons of lines of code for you.

The possibilities are truly endless. You can prepare such ‘templates’ for yourself that generate and execute code, and use it and re-use it on several environments, almost without any changes to your scripts (because it’ll be automatically generated using the database metadata).

Microsoft SQL Server exposes for you a lot of different system tables (catalog views) which you can leverage for your own benefit. They are all documented at the Microsoft Documentation.

Resources

Here are a bunch of resources for reading up on available system tables and catalog views:

• System Tables
• System Catalog Views
• Object Catalog Views
• Dynamic Management Views (DMVs)

And here are some resources on the various T-SQL methodologies that can help us in building dynamic SQL scripts:

• Table Values Constructor
• Dynamic SQL using sp_executesql
• Dynamic SQL execution on remote servers
• How to create concatenated value lists

And here are some of my other blog posts also talking about similar dynamic SQL methodologies:

• Generate MERGE statements for your tables
• Fully Dynamic Search Conditions
• Dynamic Stored Procedure Execution
• Fix Orphaned Users Easily
• Roll Forward Transaction Log Backups

Got comments? Questions? Write them in the comments section below!

This article was originally published by Eitan Blumin on August 2014, at www.madeiradata.com