Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Eitan Blumin

Eitan Blumin is a SQL Server database expert and a senior consultant at Madeira SQL Server Services. He has more than 10 years of experience in all fields of the SQL Server DBA role, including but not limited to: Database design, management, development, tuning, replication, backup management, security management, SSIS, SSRS, encryption and more. Eitan also has 10 years of experience in ASP web development, and some experience in a wide variety of development environments such as PHP, C, C++, C#, VB, Java, Perl, Assembler and more.

Let SQL Server Write Code for You

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]
	@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 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)
GO

Success! 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:

zip

 

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 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:

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:

zip

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:

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

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

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

The post Let SQL Server Write Code for You appeared first on .

Comments

Leave a comment on the original post [www.madeirasql.com, opens in a new window]

Loading comments...