September 16, 2011 at 2:27 pm
I would like to run a series of SQL commands against each database on a server with a dynamic set of databases. I constructed a select that gives me a list of the required commands in the correct order.
What is the best way to execute the list of SQL commands?
Two options I was playing with are to put the select in a cursor and run SP_EXECUTESQL on each command, one at a time, or write a script and run XP_CMDSHELL . But there must be better ways.
Thank you for any assistance.
September 16, 2011 at 3:06 pm
There is an undocumented procedure called sp_MSforeachDB. There's quite a bit of information on Google about how to use it. e.g. http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx
Behind the scenes it's doing nothing magic, though. Just creating a cursor and looping through each database, so if you want to do that yourself instead, then fine.
September 16, 2011 at 11:40 pm
Hi
Check this link.
http://qa.sqlservercentral.com/Forums/Topic475278-338-1.aspx#bm477297
Thanks
Shatrughna
Shatrughna
September 17, 2011 at 8:42 am
If you have your database independent command sting in a varchar(??) variable @command, you can do something like
use master
go
begin
declare @crlf varchar(2)=char(13)+char(10)
declare @sql varchar(max) = ''
select @sql = @sql+'use '+name+@crlf+@command+@crlf+'go'+@crlf
from master.sys.databases
where source_database_id is null and is_in_standby=0 and is_read_only=0
and name not in ('distribution','master','model','msdb','resource','tempdb')
exec(@sql)
end
You may want to modify the where clause, depending on what your command does: for example if it just reads the database, maybe you don't want the condition on is_read_only; or you may not want to exclude all the system databases; and so on. Also, if your command contains characters outside the base set you will need to declare the sql variable as nvarchar(max) instead of varchar(max).
Tom
September 17, 2011 at 12:27 pm
Tom.Thomson (9/17/2011)
use master
go
begin
declare @crlf varchar(2)=char(13)+char(10)
declare @sql varchar(max) = ''
select @sql = @sql+'use '+name+@crlf+@command+@crlf+'go'+@crlf
from master.sys.databases
where source_database_id is null and is_in_standby=0 and is_read_only=0
and name not in ('distribution','master','model','msdb','resource','tempdb')
exec(@sql)
end
Thanks for the code Tom. Copying it for future use 🙂
Didnt know we can use crlf for new line in SQL!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply