How to execute SQL against all DBs on a Server

Question

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

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

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

You may find this alternative of ms_foreachdb useful as well. — Nomad, Jan 26 '11 at 13:59

score 47 · Accepted Answer · edited Feb 18 '11 at 14:32

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)

sp_MSforeachdb can be flaky sometimes, so look at the link provided in Pradeep's answer. — Eric Humphrey - lotsahelp, Feb 18 '11 at 04:37
The sp_MSForEachDB seems good for a quick query. Is there a way I can get it to show which database each results came from? — Diskdrive, Sep 19 '16 at 01:37
@Diskdrive: add , ''?'' AS DBName to any select statement. As per my example — gbn, Sep 20 '16 at 06:59

score 9 · Answer 2 · answered Jan 28 '11 at 07:00

9

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

answered Jan 28 '11 at 07:00

garik

6,722
10
43
56

1

Answers like this, endorsing a commercial solution, should be banned SO! – Fandango68 Aug 02 '17 at 23:14
2

@Fandango68 I am not working for that company. I have just used so tool. No conflict of interests, what about you? ). – garik Aug 03 '17 at 08:48
4

@Fandango68 I would rather they were not. The best solutions should always be considered, and the best free solutions weighed against them. – Paul Nov 06 '17 at 14:41

score 3 · Answer 3 · answered Jul 29 '13 at 16:49

3

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

answered Jul 29 '13 at 16:49

EnocNRoll - AnandaGopal Pardue

131
2

Paul · Answer 4 · 2017-11-20T08:49:51.180

There is a further method that will give output in a single semi-merged result set. First open Registered Servers and create a new group under Local Server Groups then register your server once for each DB, in each case setting the default DB to the one desired.

Once complete right click on your group and select New Query. The query window that opens will have "multiple" where you would normally see a server name on the status bar. Any queries run in this window will operate on each registered server that was in the group. The first column of the results will be the name of the registered server. The result set will be fragmented by that first column and order by wills only operate within that fragment.

Very powerful but overlooked piece of functionality for when you routinely have to run the same SQL on multiple servers.

I use this a lot and it's a great feature in SSMS. The downside is that it is for manual use only, so not useful if you want to automate something to run regularly. — Sir Swears-a-lot, Jun 21 '18 at 02:55

Endi Zhupani · Answer 5 · 2016-08-24T14:31:40.113

1

My company has developed a tool called xSQL Script Executor. It is free for personal use, and for me, it has made deploying scripts on multiple targets very easy.

edited Aug 24 '16 at 14:31

answered Jul 19 '16 at 13:42

Endi Zhupani

131
5

This is a great program! Easy to use and intuitive. – AKDiscer Jul 28 '17 at 19:59

score 0 · Answer 6 · answered Oct 12 '16 at 00:45

0

I developed this tool: https://github.com/andreujuanc/TakoDeploy

Im still writing some lines on it, but its pretty stable by now. I have used it against production databases and works like a charm.

answered Oct 12 '16 at 00:45

Juan Carlos

101
4

How to execute SQL against all DBs on a Server

6 Answers6

sp_MSForEachDB

Linked