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
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:
'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
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
Here is the script.
set nocount on
create table ##c (
declare @name varchar(30),
select @table = 'counterpartyMappings' /*** Change table name here ***/
declare curse insensitive cursor for
where id = object_id(@table) AND
status & 0x08 > 0 /*** Only bother with nullable columns ***/
fetch next from curse into @name
while @@fetch_status = 0
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
fetch next from curse into @name
end -- while fetch
select * from ##c order by notNullCount, columnName
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 + ')'
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
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 '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.
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.