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