Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems:

    MAX(case when sc.text like '%remote%' then '' ELSE 'N' END) AS Relevant,
    @@ServerName as Server,
    DB_Name() as DBName 
    sysobjects so with (nolock)
    join syscomments sc with (nolock) on =
where (sc.text like '%emote%')
group by, so.type
order by so.type,

How can I execute this against all databases on a single server? (besides manually connecting to one at a time and executing)

share|improve this question
You may find this alternative of ms_foreachdb useful as well. – Pradeep Adiga Jan 26 '11 at 13:59
up vote 31 down vote accepted


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]'')
            @@ServerName as Server,
            ''?'' as DBName 
            [?].dbo.sysobjects so with (nolock)
            join [?].sys.all_sql_modules sc with (nolock) on = sc.object_id
        where (lower(sc.definition) like ''' + @searchKey + ''')
        group by, so.type
        order by so.type,'

EXEC sp_MSForEachDB @findKeySQL


  1. ? is replaced in the query as the database name, so structure the query to explicitly define which DB it is to query against
  2. modified to use sys.all_sql_modules as holds complete module text (syscomments could split the keyword when reach the spanning over rows)
share|improve this answer
sp_MSforeachdb can be flaky sometimes, so look at the link provided in Pradeep's answer. – Eric Humphrey - lotsahelp Feb 18 '11 at 4:37

Just my $0.05: SQL Multi Script (execution of multiple scripts against multiple SQL Servers).

share|improve this answer

SSMS Tools Pack does this nicely and it is free for database servers prior to 2012. The feature: "Run on Multiple Targets" -

share|improve this answer

For the past couple of months I have used a third party tool called xSQL Script Executor. It is free for personal use, and for me, it has made deploying scripts on multiple targets very easy.

share|improve this answer
Please update this post to disclose your affiliation with that company / product. – srutzky 2 days ago

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.