How to execute SQL against all DBs on a Server
sp_MSForEachDB
One option is sp_MSForEachDB. It's undocumented but useful nonetheless
DECLARE @command varchar(1000)
SELECT @command =
'USE [?] UPDATE Table1 SET Field1 = ''ninjas'' WHERE Field2 = ''pirates'''
EXEC sp_MSforeachdb @command
A search of the interwebs has many more examples too
Note: Being an unsupported function (which has some known bugs) you may want to write your own version (thanks @Pradeep)
The SQL example above would need to be restructured as:
DECLARE @findKeySQL nvarchar(2000)
DECLARE @searchKey nvarchar(20)
SET @searchKey = lower('%remote%')
SET @findKeySQL = 'IF ''[?]'' NOT IN (''[master]'', ''[model]'',
''[msdb]'', ''[tempdb]'')
select
so.name,
so.type,
@@ServerName as Server,
''?'' as DBName
from
[?].dbo.sysobjects so with (nolock)
join [?].sys.all_sql_modules sc with (nolock) on so.id = sc.object_id
where (lower(sc.definition) like ''' + @searchKey + ''')
group by so.name, so.type
order by so.type, so.name'
EXEC sp_MSForEachDB @findKeySQL
NOTES:
- ? is replaced in the query as the database name, so structure the query to explicitly define which DB it is to query against
- modified to use sys.all_sql_modules as holds complete module text (syscomments could split the keyword when reach the spanning over rows)
Just my $0.05: SQL Multi Script (execution of multiple scripts against multiple SQL Servers).
SSMS Tools Pack does this nicely and it is free for database servers prior to 2012. The feature: "Run on Multiple Targets" - http://www.ssmstoolspack.com/Features?f=6