Smarter T-SQL: Generate, Then Execute

In part 1, we looked at generating T-SQL style code. In part 2, let's get this thing to run!

Last time, I showed how a single SELECT statement can be built to create an executable string of T-SQL. This time, let's look at generating T-SQL, as well as executing it in the same batch.

Consider this script:

set nocount on
declare @DBName sysname,
@sql varchar(800),
@min int,
@max int,
@backuppath varchar(1000),
@datestamp varchar(8),
@mm varchar(2),
@hh varchar(2)
set @backuppath = '\\nasdevice\BackupDirectory\'
create table #dbtemp
(    ID int IDENTITY(1,1),
    DBName sysname,
    RecoveryModel varchar(100)
insert into #dbtemp (DBName, RecoveryModel)
SELECT [name],
cast(DATABASEPROPERTYEX([name],'Recovery') as varchar(8000)) AS recovery_model_desc
FROM sys.databases
where [name] not in ('model', 'tempdb')
set @min = (select min(ID) from #dbtemp)
set @max = (select max(ID) from #dbtemp)
while @min <= @max
    set @datestamp = (select convert(varchar(12), getdate(), 112))
    set @hh = REPLICATE('0', 2-LEN(CONVERT(varchar(2), datepart(HH, GETDATE())))) + CONVERT(varchar(2), datepart(HH, GETDATE()))
    set @mm = REPLICATE('0', 2-LEN(CONVERT(varchar(2), datepart(N, GETDATE())))) + CONVERT(varchar(2), datepart(N, GETDATE()))
    set @DBName = (select dbname from #dbtemp where ID = @min)
    set @sql = 'BACKUP DATABASE ' + @DBName + ' TO  DISK = N'''+ @backuppath + @DBName + '_backup_' + @datestamp + @hh + @mm + '.bak'' WITH  RETAINDAYS = 1, NOFORMAT, NOINIT,  NAME = N''' + @DBName + '_backup_' + @datestamp + ''', SKIP, REWIND, NOUNLOAD,  STATS = 10'
    print @sql
    set @min = @min + 1
drop table #dbtemp

At first glance, this may seem complicated, but it's really very simple. After declaring all of the necessary variables, we're creating a temp table (this could be a table variable or a cursor, for that matter) to use for cycling through all the databases on a server. Next, we're using a WHILE loop to iterate through all of the values in the temp table.
Here's where it gets interesting. Besides all of the fun with breaking down the time and date into a string that we can use, what we're doing is setting a string value to @sql:

set @sql = 'BACKUP DATABASE ' + @DBName + ' TO  DISK = N'''+ @backuppath + @DBName + '_backup_' + @datestamp + @hh + @mm + '.bak'' WITH  RETAINDAYS = 1, NOFORMAT, NOINIT,  NAME = N''' + @DBName + '_backup_' + @datestamp + ''', SKIP, REWIND, NOUNLOAD,  STATS = 10'

Then, using print @sql we're actually seeing what the value of that variable is for each iteration. Here's the results on my machine:

BACKUP DATABASE master TO  DISK = N'\\nasdevice\BackupDirectory\master_backup_201004121335.bak' WITH  RETAINDAYS = 1, NOFORMAT, NOINIT,  NAME = N'master_backup_20100412', SKIP, REWIND, NOUNLOAD,  STATS = 10
BACKUP DATABASE msdb TO  DISK = N'\\nasdevice\BackupDirectory\msdb_backup_201004121335.bak' WITH  RETAINDAYS = 1, NOFORMAT, NOINIT,  NAME = N'msdb_backup_20100412', SKIP, REWIND, NOUNLOAD,  STATS = 10
BACKUP DATABASE Play TO  DISK = N'\\nasdevice\BackupDirectory\Play_backup_201004121335.bak' WITH  RETAINDAYS = 1, NOFORMAT, NOINIT,  NAME = N'Play_backup_20100412', SKIP, REWIND, NOUNLOAD,  STATS = 10
BACKUP DATABASE sysutility_mdw TO  DISK = N'\\nasdevice\BackupDirectory\sysutility_mdw_backup_201004121335.bak' WITH  RETAINDAYS = 1, NOFORMAT, NOINIT,  NAME = N'sysutility_mdw_backup_20100412', SKIP, REWIND, NOUNLOAD,  STATS = 10


So we can see that we have a syntactically valid backup command for every database on my machine (except tempdb and model, because we omitted them in the initial filter). Now, if I want to execute each of these statements individually, I can simply copy the result set into a new query window and go. Otherwise, if I like the commands, I can simply add the line EXEC (@sql). This will execute the command in the @sql variable each time it is encountered in the loop.

Often, I will simply comment out the PRINT statement and add the EXEC statement right after it, so that if/when I troubleshoot this script, it's simply a matter of commenting and uncommenting those two lines.

This method can be combined with proper error handling to build all sorts of dynamic commands to be used on your systems. You could, in fact, combine this with the SQL Server 2008 Central Management features (including multi-server query) to quickly gather information across all of your databases.

As with any T-SQL programming, just be sure you aren't introducing severe overhead with these types of queries; running a single query against every database on a very large, high-traffic system could be counterproductive to your career. So be careful, but have fun!

About the Author

Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.


  • Microsoft Warns IT Pros on Windows Netlogon Fix Coming Next Month

    Microsoft on Thursday issued a reminder to organizations to ensure that their systems are properly patched for a "Critical"-rated Windows Netlogon vulnerability before next month's "update Tuesday" patch distribution arrives.

  • Microsoft Nudging Skype for Business Users to Teams

    Microsoft on Thursday announced some perks and prods for Skype for Business unified communications users, with the aim of moving them to the Microsoft Teams collaboration service instead.

  • How To Improve Windows 10's Sound and Video Quality

    Windows 10 comes with built-in tools that can help users get the most out of their sound and video hardware.

  • Microsoft Offers More 'Solorigate' Advice Using Microsoft 365 Defender Tools

    Microsoft issued yet another article with advice on how to use its Microsoft 365 Defender suite of tools to protect against "Solorigate" advanced persistent threat types of attacks in a Thursday announcement.

comments powered by Disqus