SQL Server：在所有数据库上运行脚本

Question

我有一个带有函数（在脚本末尾删除的函数）和临时表定义以及变量来处理数据的SQL脚本，为数据库提供了一个单行结果。

它可以工作，但我需要运行并获取1000个数据库的所有结果。似乎不可能分别运行脚本。

我找到了“sp_MSForEachDB”单命令行示例，但我有整个脚本，无法使用这种方式使它起作用。

有没有办法在所有数据库上运行脚本？

谢谢回复

- MS.

---

这个脚本到底是做什么的？你说它会删除和创建函数，但也会创建临时表（所以它们不会持久存在，这意味着这不是一个永久性的事情）。你的实际目标是什么？这可能是一个xy问题。 - Thom A

XY问题 - Shakeer Mirza

函数和临时表只是用于处理数据，这没有任何问题。我的实际目标是通过运行单个查询脚本从数据库中获取结果。但我有一千个数据库，我想把所有的结果都汇总在一起。 - MS.

但是那个脚本告诉你什么呢？显然它正在返回某种数据，那么它所做的工作是什么？我敢猜测，这可以在不创建临时表和函数的情况下实现。就像@hakeerMirza给你提供的链接所说的那样；与其问我们如何完成你的解决方案Y，不如告诉我们你的问题X。 - Thom A

正如之前所说，尽量提供更多关于您实际目标的信息。我的假设是正确的吗？所有这些数据库都存在不同的服务器中？您是否知道，通过“注册服务器”，您可以一次触发针对所有服务器的脚本？请提供更多背景信息... - Shnugo

如果这不是机密或者有成千上万行代码，你可以提供你的SQL代码... - Shnugo

Answer 1

我并不是说这是个好主意，因为我们并不知道你正在运行的脚本具体做了什么，但这应该可以工作。我并不羡慕你把所有单引号放在正确位置的痛苦，但这是使用未记录、不支持的存储过程所付出的代价。

DECLARE @sqlText varchar(max); 

SET @sqlText = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
  BEGIN 
    USE ? 
    EXEC(''CREATE <functions, tables, what have you>'') 
  END';

EXECUTE sp_MSforeachdb @sqlText;

- Eric Brandt

Answer 2

我正在寻找一种方法，使用单独保存的查询文件来运行脚本，针对同一服务器下的一组特定数据库。为此，我将sql文件的内容读入变量，然后在游标的每次迭代中使用sp_executesql命令来运行它：

/*
    INSTRUCTIONS:
        * Save the sql file in a place that is accessible by the server
        * Change the path to match path of the directory!
        * Apply filters to the databases you are targeting
        (OPTIONAL) Try printing your command before executing it!
        (OPTIONAL) Inserting into a temp table requires you to define the same columns! (see below)
*/

CREATE TABLE #temp
    (Id INT, FOO DATETIME2(7), BAR VARCHAR(100))

/* !!! Change the file path !!! (must be accessible by the server) */
DECLARE @FileContents VARCHAR(MAX)
SELECT @FileContents = BulkColumn
FROM OPENROWSET(BULK 'E:\PathToYourFile\MyFile.sql', SINGLE_CLOB) x;

/* Instantiate a cursor to loop over each database */
DECLARE @DB_Name VARCHAR(100) 
DECLARE @Command VARCHAR(MAX) 
DECLARE database_cursor CURSOR FOR 
SELECT name 
FROM MASTER.sys.databases
WHERE database_id > 4 AND state = 0 /*Online*/ AND
/* !!! apply a filter to target specific databases !!! */
    name LIKE '%ConsistentNamingConvention%'

/* Open and retrieve the first entry */
OPEN database_cursor 
FETCH NEXT FROM database_cursor INTO @DB_Name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    /* Use the database from the name on the cursor. Then run the command saved above */
    SELECT @Command = 'USE ' + QUOTENAME(@DB_Name) + ';' + CHAR(13)+CHAR(10) --add 2 newlines
    + CHAR(13)+CHAR(10)
    + @FileContents

    /* View the command (uncomment) */
    --PRINT @Command

    /* run the file contents against the specific database! */
    EXEC sp_executesql @Command 

    /* iterate the cursor to the next database name */
    FETCH NEXT FROM database_cursor INTO @DB_Name 
END 
CLOSE database_cursor;
DEALLOCATE database_cursor;

SELECT * FROM #Temp
DROP TABLE #Temp

假设 MyFile.sql 使用 INSERT INTO #Temp！(SELECT INTO 不起作用)

说明：

1. 将 SQL 文件保存在 SQL 服务器可以访问的位置
2. 更改文件路径以指向您的文件 (第 16 行)。不幸的是，这必须是一个字符串文字，而不是一个变量
3. 在 CURSOR 声明下过滤您要处理的数据库 (第 26 行)

同样，这假设您感兴趣的所有数据库都在同一台服务器上。

- Austin Arnett

Answer 3

嗨，在 SSMS 中，您可以使用 sqlcmd 命令运行所选数据库的脚本。 例如，我想在两个不同的数据库中运行我的脚本，为此我已将我的脚本保存在本地。并使用:r"C:\temp\test1.sql"来调用它。

我还在思考另一种使用循环完成的方法。

启用 SQL 模式：SSMS > 查询 > SQLCMD 模式

use test
:r  "C:\temp\test1.sql"
use S2VTST6
:r  "C:\temp\test1.sql"

- Jigar Parekh