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 Adding Google G Suite Migration in Exchange Admin Center

    Microsoft's Exchange Admin Center will be getting the ability to move Google G Suite calendar, contacts and e-mail data over to the Office 365 service "in the coming weeks."

  • Qualcomm Back in Datacenter Fray with AI Chip

    The chip maker joins a crowded field of vendors that are designing silicon for processing AI inference workloads in the datacenter.

  • Microsoft To Ship Surface Hub 2S Conference Device in June

    Microsoft on Wednesday announced a June U.S. ship date for one of its Surface Hub 2S conferencing room products, plus a couple of other product milestones.

  • Kaspersky Lab Nabs Another Windows Zero-Day

    Kaspersky Lab this week described more about a zero-day Windows vulnerability (CVE-2019-0859) that its researchers recently discovered, and how PowerShell was used by the exploit.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.