Dynamic SQL Beginner's Guide

Dynamic SQL allows you to write SQL that will then write and execute more SQL for you. This can be a great time saver because you can:

• Automate repetitive tasks.
• Write code that will work in any database or server.
• Write code that dynamically adjusts itself to changing conditions

Most dynamic SQL is driven off the system tables, and all the examples I use here will use system tables, but if you have suitable data in user tables, you can write dynamic SQL from the contents of these tables, too.

I have tried to code the example scripts in such a way that they would work in versions of SQL Server from 6 right up to 2000, but the reader should be aware that there are often better ways of doing things in later versions of SQL Server. Users of version 7 onwards should look to using the information_schema rather than directly accessing the system tables.

Example 1--Generate table level statements quickly

Click here for code example 1.

These two statements generate Update Stats and DBCC commands for each user table in the database. You can cut and paste the results into a SQL window and run them, or save the output to a file for later use.

For the first example there is an even quicker way of achieving this--you can use the built-in SP sp_msforeachtable like this:

sp_msforeachtable 
   'update statistics ?'

The system stored procedure loops through the tables in the current database and executes the command "Update statistics" with each table name substituted where you see the '?' character.

However, if you try to use sp_msforeachtable with the DBCC command it will fail, because the DBCC command will not accept the dbo. ownership qualification that sp_msforeachtable uses.

Example 2--Executing dynamic SQL automatically

You can use cursors and the EXEC or sp_executesql commands to execute your dynamic SQL at the same time as you generate it. These commands take a char or varchar parameter that contains a SQL statement to execute, and runs the command in the current database. This script will execute the DBCC REINDEX command for all the user tables in the current database.

Click here for code example 2.

This example makes a handy template for producing and executing any dynamic SQL code. If you were to replace the lines in red above with the following code fragment, you end up with a utility to revoke permissions on all user tables.

Example 3--Null columns script

This script is a little more complex, and because it can take time to run against large tables, you should not run it in production databases.

I wrote this script to help me search for redundant columns in legacy databases. I use it to check whether nullable columns are actually used in a table. It uses the syscolumns system table to enumerate all the nullable columns in a given table and print out how many rows contain NULL and how many contain real data.

The syscolumns table lists all the columns in all the tables in your databases, and also lists the parameters for all the stored procedures. Each entry in syscolumns has a status code, and the bit that represents hexadecimal 8 shows if the column is nullable or not. The script also uses a Case statement trick to produce crosstab data, which you can read about here.

You need to enter the name of the table you want to check in the script where commented.

Here is the script.

set nocount on
go
create table ##c (
       columnName varchar(30),
       nullCount int,
       notNullCount int
)
declare @name varchar(30),
        @sql varchar(255),
        @table varchar(30)


select @table = 'counterpartyMappings' /*** Change table name here ***/

declare curse insensitive cursor for
    select name
    from syscolumns
    where id = object_id(@table) AND
    status & 0x08 > 0 /*** Only bother with nullable columns ***/

open curse
fetch next from curse into @name
while @@fetch_status = 0
begin
    select @sql =
    'insert ##c '+
    'select min("' + @name + '") as col1,'+
        'sum(case when ' + @name + ' IS NULL then 1 else 0 end),'+
        'sum(case when ' + @name + ' IS NULL then 0 else 1 end) '+
    'from ' + @table
    exec (@sql)

    fetch next from curse into @name

end -- while fetch

close curse
deallocate curse

select * from ##c order by notNullCount, columnName
go

drop table ##c

Example 4--Cross-database dynamic SQL

This short query interrogates the master database table sysdatabases, and executes a DBCC command against every database. This sort of thing is especially useful for carrying out database maintenance and backups with earlier versions of SQL Server where the maintenance wizards are not very well developed, or where you want to do some kind of non-standard task across all databases.

select 'dbcc newalloc (' + name + ')'
  from master..sysdatabases
  where name not in ('tempdb', 'pubs')

If you were to expand this routine using the cursor method above you could write a code block that would, for example, dynamically back up all databases, with new and deleted databases catered for automatically.

Again, you can cut and paste this code fragment into the cursor routine in example two.

As with sp_msforeachtable, there is a shorthand version you can sometimes make use of to execute SQL in multiple databases: the stored procedure sp_msforeachdb:

sp_msforeachdb 'dbcc newalloc (?)'

A Word of Warning

You need to be very careful with dynamic SQL. Too much dynamic SQL going on can drag down the performance of SQL Server because it cannot store optimization details or execution plans for dynamically generated SQL. Locking and blocking problems may also arise.

Further Reading

The key to being able to write good dynamic SQL is having a good understanding of the system tables--once you know what they contain, you will realize that there is a wealth of information that can be used to generate useful dynamic SQL utilities. If you are not too familiar with the contents of the systems tables, I suggest you start with Books Online.