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