Introduction
Many times I happen to find myself in a situation where, as a DBA, I need to write some long and cumbersome code (usually for maintenance purposes).
For example:
- Write a script to copy a bunch of tables from one database to another.
- Import the tables from one database to another database.
- Rebuild a bunch of indexes.
- Update a bunch of statistics.
- Write scripts for querying a lot of tables.
- Write scripts to backup or restore a lot of databases.
- And so on and so forth.
A lot of DBAs would spend hours (and even days) sitting in front of their computer and 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 wouldn’t think that it takes 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 this blog of mine (namely, SQL Injection among other things).
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 maniacal laughter now]
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] @Titlenvarchar(50), @OrderDatedate, @Qtyint, @Totalmoney 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:
- {TableName}
This one should be a no-brainer, seeing as we’ll be receiving this as a pre-set value.
- {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.
- {ColumnParametersList}
This one is very similar to {ColumnsList}, except we just need to add the @ symbol before each column.
- {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 @ColumnParametersListWithTypesThis script uses concatenation and queries from a couple catalog views, to create 3 lists of values.
If we put ‘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)
GOSuccess! So now we just need to implement this logic in a cursor which traverses several tables, and we’re done. You can download the full sample script from here:
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 a more complex example of a real-life scenario that I’ve recently 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 TableNameNote 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:
| TableName | PKColumn | PKColumnType |
| FileStorage | [ID] | int |
| InvoiceHeader | [InvoiceId] | int |
| InvoiceRows | [RowID] | int |
| Invoices | [InvoiceID] | int |
| SimpleFileStorage | [ID] | int |
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 created 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 it will be automatically done 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 download the full sample script here:
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 SQL Server Books Online.
Resources
Here are a bunch of resources for reading up on available system tables and catalog views:
- System Tables: http://msdn.microsoft.com/en-us/library/ms179932.aspx
- System Views: http://msdn.microsoft.com/en-us/library/ms177862.aspx
- Catalog Views: http://msdn.microsoft.com/en-us/library/ms174365.aspx
- Object Catalog Views: http://msdn.microsoft.com/en-us/library/ms189783.aspx
- Dynamic Management Views (DMVs): http://msdn.microsoft.com/en-us/library/ms188754.aspx
And here are some resources on the various T-SQL methodologies that can help us in building dynamic SQL scripts:
- Table Values Constructor: http://msdn.microsoft.com/en-us/library/dd776382.aspx
- Dynamic SQL using sp_executesql: http://msdn.microsoft.com/en-us/library/ms188001.aspx
- Dynamic SQL guide by Erland Sommarskog: http://www.sommarskog.se/dynamic_sql.html
- Dynamic SQL execution on remote servers: http://www.mssqltips.com/sqlservertip/1757/dynamic-sql-execution-on-remote-sql-server-using-exec-at/
- How to create concatenated value lists: http://blog.sqlauthority.com/2007/05/06/sql-server-creating-comma-separate-values-list-from-table-udf-sp/
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
Got comments? Questions? Write them in the comments section below!
The post Let SQL Server Write Code for You appeared first on Madeira Data Solutions.
