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