主页 / dba / 问题 / 907
Accepted
Andrew Bickerton
Asked: 2011-01-27 05:03:46 +0800 CST

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

  • 772

我有一些标准 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

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

sql-server

6 个回答

  1. Best Answer

    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

    笔记：

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

  2. 只是我的 0.05 美元：SQL 多脚本（针对多个 SQL Server 执行多个脚本）。

    • 9

  3. SSMS 工具包很好地做到了这一点，它对 2012 年之前的数据库服务器是免费的。功能：“在多个目标上运行”- http://www.ssmstoolspack.com/Features?f=6

    • 3

  4. 还有一种方法可以在单个半合并结果集中给出输出。首先打开注册服务器并在本地服务器组下创建一个新组，然后为每个数据库注册一次服务器，在每种情况下将默认数据库设置为所需的。

    完成后，右键单击您的组并选择新查询。打开的查询窗口将有“多个”，您通常会在状态栏上看到服务器名称。在此窗口中运行的任何查询都将在该组中的每个已注册服务器上运行。结果的第一列将是注册服务器的名称。结果集将按第一列进行分段，并且按遗嘱排序仅在该分段内操作。

    当您经常需要在多台服务器上运行相同的 SQL 时，这是非常强大但被忽视的功能。

    • 2

  5. 我的公司开发了一个名为xSQL Script Executor的工具。它是免费供个人使用的，对我来说，它使在多个目标上部署脚本变得非常容易。

    • 1

  6. 我开发了这个工具：https ://github.com/andreujuanc/TakoDeploy

    我还在上面写了几行，但它现在相当稳定。我已经将它用于生产数据库并且工作起来就像一个魅力。

    • 0

相关问题