Ok this is more just a little trick with Dynamic sql, but it saves you tons of code and you avoid having to use cursors or single row iterations. You can use it anywhere you would a cursor. More set based operations…Horray!!!!
For this example we will do backups
declare @sql nvarchar(max) -- used for dynamic sql
declare @fileDate varchar(20) -- used for file name
select @fileDate = convert(varchar(20),getdate(),112) -- used for file name
set @sql = 'declare @fileName varchar(256)' + char(13)-- we need a declare statement for filename
select @sql = @sql + char(13)+
'set @fileName = ''c:/backup/'+ name + '_' + @fileDate + '.BAK''' + char(13)+
'backup database '+ [name] + ' to disk = @fileName '+ char(13)
from sys.sysdatabases
where name not in ('master','model','msdb','tempdb')
exec sp_executesql @sql
Here is a snippit from http://www.mssqltips.com/tip.asp?tip=1070 that illustrates a simple cursor alternative
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:Backup'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
When you compare the two and look at the profiler results it is an extreme performance increase as well
dbcc dropcleanbuffers
dbcc freeproccache
go
declare @sql nvarchar(max) -- used for dynamic sql
declare @fileDate varchar(20) -- used for file name
select @fileDate = convert(varchar(20),getdate(),112) -- used for file name
set @sql = 'declare @fileName varchar(256)' + char(13)-- we need a declare statement for filename
select @sql = @sql + char(13)+
'set @fileName = ''c:/backup/'+ name + '_' + @fileDate + '.BAK''' + char(13)+
'backup database '+ [name] + ' to disk = @fileName '+ char(13)
from sys.sysdatabases
where name not in ('master','model','msdb','tempdb')
print( @sql )
go
dbcc dropcleanbuffers
dbcc freeproccache
go
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:Backup'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
Print ('BACKUP DATABASE @name TO DISK = ' + @fileName)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
When you run this and profile the results you can see the duration, reads and cpu use are dramatically less.
