如何对服务器上的所有数据库执行 SQL

一尘不染

如何对服务器上的所有数据库执行 SQL

sql-server

我有一些标准 SQL，可以在单个服务器上针对多个数据库运行，以帮助我诊断问题：

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

如何对单个服务器上的所有数据库执行此操作？（除了一次手动连接一个并执行之外）

阅读 112

2022-11-04

共1个答案

一尘不染

sp_MSForEachDB

一种选择是sp_MSForEachDB。它没有记录，但仍然有用

DECLARE @command varchar(1000) 
SELECT @command = 
    'USE [?] UPDATE Table1 SET Field1 = ''ninjas'' WHERE Field2 = ''pirates''' 
EXEC sp_MSforeachdb @command

搜索互联网也有更多示例

注意：作为一个不受支持的功能（有一些已知的错误），您可能想要编写自己的版本（感谢@Pradeep）

上面的 SQL 示例需要重组为：

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

笔记：

? 在查询中被替换为数据库名称，因此构造查询以明确定义要查询的数据库
修改为使用 sys.all_sql_modules 作为保存完整的模块文本（syscomments可以在到达跨行时拆分关键字）

2022-11-04