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:

1
2
3
4
5
6
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