Running SQL cross servers
1 min read

Running SQL cross servers

If you work with more than one database and have the constant need to run the same query across multiple servers, the undocumented stored procedure sp_MSforeachdb can be useful; see below example comparing stored procedures across servers using a simple checksum to tell if they are the same exact contents. sp_msforeachtable is another oldie but goodie that can be used for do an action per table or examine table properties easily – such as examining table properties that are not exposed from sys.tables.If you are using SQL Server 2008 there is a more fully supported way to do this and many other nice admin features: Multiple Server Query Execution in SQL Server 2008. Example of sp_MSforeachdb to test if stored procedure contents are exactly the same across databases:

declare @sql varchar(max), @procname sysnameset
@procname = 'cspExampleList'
set @sql = ' use ? select db_name(), sp.name, checksum(sc.text) from sys.procedures sp join syscomments sc on sc.id = sp.object_id where name = 'set @sql = @sql + '''' + @procname + ''''-- select @sqlcreate table #Results (dbname varchar(max), procname varchar(max), chksum int)
insert into #Resultsexec sp_MSforeachdb @sql
select * from #Results
drop table #Results

Enjoying these posts? Subscribe for more