如何对名称存储在表中的多个数据库执行T-SQL？

Question

我在同一台服务器上有多个数据库（SqlServer 2005），具有相同的架构但不同的数据。

我有一个额外的数据库，其中有一个表存储上述数据库的名称。

因此，我需要做的是迭代这些数据库名称，并实际“切换”到每个数据库名称（使用 [dbname]）并执行 T-SQL 脚本。我说清楚了吗？

我举个例子（根据真实情况进行简化）：

CREATE TABLE DatabaseNames
(
   Id   int,
   Name varchar(50)
)
INSERT INTO DatabaseNames SELECT 'DatabaseA'
INSERT INTO DatabaseNames SELECT 'DatabaseB'
INSERT INTO DatabaseNames SELECT 'DatabaseC'

假设DatabaseA、DatabaseB、DatabaseC是真实存在的数据库。 假设我需要在这些数据库上创建一个新的 SP。我需要一些脚本来循环这些数据库并执行我指定的 T-SQL 脚本（可能存储在 varchar 变量或其他位置）。

有什么想法吗？

Answer 1

您应该能够使用 sp_MSforeachdb 无文档存储过程来执行此操作。

Answer 2

此方法要求您将要在每个数据库上执行的 SQL 脚本放在变量中，但应该可行。

DECLARE @SQLcmd varchar(MAX)
SET @SQLcmd ='Your SQL Commands here'

DECLARE @dbName nvarchar(200)
DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT dbName FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
    FETCH c INTO @dbName 
    IF @@fetch_status <> 0 BREAK
    EXEC('USE [' + @dbName + '] ' + @SQLcmd )
END
CLOSE c

另外，正如一些人指出的那样。如果您想要运行一个需要成为批处理中唯一的命令的命令，那么这种方法是有问题的。

对于这种情况，有一个替代方案，但它需要比许多 DBA 可能希望您拥有的权限更多的权限，并且要求您将 SQL 放入单独的文本文件中。

DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT dbName FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
    FETCH c INTO @dbName 
    IF @@fetch_status <> 0 BREAK
     exec master.dbo.xp_cmdshell 'osql -E -S '+ @@SERVERNAME + ' -d ' + @dbName + '  -i c:\test.sql'
END
CLOSE c
DEALLOCATE c

Answer 3

使用 USE 命令并重复您的命令

。 此处了解如何将 USE 与参数一起使用

Answer 4

我知道这个问题已有 5 年历史了，但我通过 Google 找到了这个问题，所以其他人也可能如此。

我推荐sp_msforeachdb系统存储过程。您不需要创建任何其他存储过程或游标。

鉴于您的数据库名称表已创建：

EXECUTE sp_msforeachdb '
USE ? 

IF DB_NAME() 
    IN( SELECT name DatabaseNames )
BEGIN

    SELECT 
              ''?'' as 'Database Name'  
            , COUNT(*)
    FROM
            MyTableName
    ;

END
'

我这样做是为了总结我从具有相同安装的数据库架构的多个不同站点恢复的许多数据库中的计数。

例子：
-- 在所有站点存档数据库中重复执行 SQL 命令。

PRINT       'Database Name'
+ ',' +     'Site Name'
+ ',' +     'Site Code'
+ ',' +     '# Users'
+ ',' +     '# Seats'
+ ',' +     '# Rooms'
...  and so on...
+ ',' +     '# of days worked'
;


EXECUTE sp_msforeachdb 'USE ?

IF DB_NAME() 
    IN( SELECT name FROM sys.databases  WHERE name LIKE ''Site_Archive_%'' )
BEGIN

DECLARE  @SiteName      As Varchar(100);
DECLARE  @SiteCode      As Varchar(8);

DECLARE  @NumUsers  As Int
DECLARE  @NumSeats  As Int
DECLARE  @NumRooms  As Int
... and so on ...

SELECT  @SiteName = OfficeBuildingName FROM  Office
...

SELECT @NumUsers = COUNT(*)  FROM   NetworkUsers
...

PRINT       ''?'' 
+ '','' +     @SiteName 
+ '','' +     @SiteCode
+ '','' +     str(@NumUsers)
...
+ '','' +     str(@NumDaysWorked) ;
END
'

最棘手的部分是单引号 '

Answer 5

最简单的方法是这样的：

DECLARE @stmt nvarchar(200)
DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT 'USE [' + Name + ']' FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
    FETCH c INTO @stmt
    IF @@fetch_status <> 0 BREAK
    SET @stmt = @stmt + ' ' + @what_you_want_to_do
    EXEC(@stmt)
END
CLOSE c
DEALLOCATE c

但是，显然它不适用于需要成为批处理中的第一个语句的语句，例如 CREATE PROCEDURE。为此，您可以使用 SQLCLR。创建并部署一个这样的类：

public class StoredProcedures {
    [SqlProcedure(Name="exec_in_db")]
    public static void ExecInDb(string dbname, string sql) {
        using (SqlConnection conn = new SqlConnection("context connection=true")) {
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand()) {
                cmd.CommandText = "USE [" + dbname + "]";
                cmd.ExecuteNonQuery();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }
    }
}

然后你可以这样做

DECLARE @db_name nvarchar(200)
DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT Name FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
    FETCH c INTO @@db_name
    IF @@fetch_status <> 0 BREAK
    EXEC exec_in_db @db_name, @what_you_want_to_do
END
CLOSE c
DEALLOCATE c

Answer 6

我想这在 TSQL 中通常是不可能的，因为正如其他人指出的那样，

• 您首先需要使用 USE 语句来更改数据库，

• 后跟要执行的语句，尽管未指定，但该语句是 DDL 语句，必须位于批处理中的第一个语句。

• 此外，要执行的字符串中不能有 GO。

我找到了一个调用 sqlcmd 的命令行解决方案：

for /f "usebackq" %i in 
    (`sqlcmd -h -1 -Q 
     "set nocount on select name from master..sysdatabases where status=16"`)
    do
        sqlcmd -d %i -Q "print db_name()"

示例代码使用当前的 Windows 登录名从 Master 查询所有活动数据库（替换为您自己的连接并查询数据库），并对找到的每个数据库执行文字 TSQL 命令。 （换行符只是为了清楚起见）

查看 sqlcmd 的 命令行参数。您也可以向它传递一个 TSQL 文件。

如果您希望允许手动选择数据库，请查看 SSMS 工具包。