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!

• By Joshua Jones
• 04/12/2010

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
begin
    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
end
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

etc…

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!