SQL Server游标 - 循环通过多个服务器并执行查询 [英] SQL Server cursor - loop through multiple servers and execute query

问题描述

我有以下代码（游标）：

  DECLARE @SN VARCHAR 
 DECLARE @sql NVARCHAR（MAX）; 
 DECLARE C CURSOR LOCAL FAST_FORWARD 
 FOR SELECT DISTINCT（SERVERNAME）FROM INSTALLATION 
其中DATABASETYPE ='MsSql'AND SERVERNAME IN（'x'）; 
 
 OPEN C; 
 
 FETCH NEXT FROM C INTO @SN; 
 WHILE（@@ FETCH_STATUS = 0）
 BEGIN 
 PRINT @SN; 
  - 你可以循环这里为每个数据库，如果你定义什么是
 SELECT名称
从master.dbo.sysdatabases 
 WHERE名称不在（'master' ，'model'，'msdb'，'tempdb'）; 
 SET @sql = N'SELECT TOP 1 NAME FROM TABLE'; 
 
 EXEC sp_executesql @sql; 
 FETCH NEXT FROM C INTO @SN; 
 END 
 CLOSE C; 
 DEALLOCATE C; 
  

我想能够循环访问每个服务器并对某些执行select语句）的数据库。

查询是这样的：

  SELECT TOP 1 NAME FROM TABLE 
  

我正在运行游标的服务器具有链接的所有其他服务器。

解决方案

  DECLARE @SN VARCHAR 
 
 DECLARE C CURSOR LOCAL FAST_FORWARD 
 FOR SELECT DISTINCT（SERVERNAME）FROM TABLE 
其中SERVERNAME NOT IN（'SRV1'，'SRV2'，'SRV3'）; 
 
 OPEN C; 
 
 FETCH NEXT FROM C INTO @SN; 
 WHILE（@@ FETCH_STATUS = 0）
 BEGIN 
 PRINT @SN; 
  - 你可以循环这里为每个数据库，如果你定义什么是
 SET @sql = N'SELECT * FROM'+ @SN +'.master.dbo.TABLE;'; 
 EXEC sys.sp_executesql @sql; 
 FETCH NEXT FROM C INTO @SN; 
 END 
 CLOSE C; 
 DEALLOCATE C; 
  

更改：

1. 没有理由在这里使用默认游标选项 - 全局，可更新，动态，可滚动等。背景。




2. 作为习惯/最佳做法，使用 sp_executesql ，而不是 EXEC（）。虽然在这种情况下它并不重要，但在其他情况下可能很重要，所以我更喜欢始终以相同的方式编码。 背景。




3. 此外，请务必使用分号终止您的陈述。你必须，最终。 背景< a>。

现在我们有一些关于您的实际需求的信息，我建议这一点代码。哦，看看，没有光标（好吧，没有显示的游标声明和所有的脚手架）。

  SET NOCOUNT ON; 
 
 DECLARE @dbs TABLE（SERVERNAME SYSNAME，DBNAME SYSNAME）; 
 
 DECLARE @sql NVARCHAR（MAX）= N' 
 
  - 首先，我们得到每个服务器上的数据库：
 
 SELECT @sql + = N'SELECT'''+ SERVERNAME +'''，name FROM'
 + QUOTENAME（SERVERNAME）+'.master.sys.databases 
 WHERE database_id> 4 
 AND name NOT IN（N'somedb''，N''someotherdb''）;'
 FROM dbo.INSTALLATION 
 WHERE DATABASETYPE ='MsSql'
 AND SERVERNAME IN（'x'）; 
 
 INSERT @dbs EXEC sys.sp_executesql @sql; 
 
 SELECT @sql = N''; 
 
  - 现在，构建一个命令在每个数据库上下文中运行：
 
 SELECT @sql + = N'
 EXEC'+ QUOTENAME（SERVERNAME）+' 。'
 + QUOTENAME（DBNAME）+'.sys.sp_executesql @sql;'
 FROM @dbs; 
 
  - 可以随意更改第三个参数：
 
 EXEC sys.sp_executesql @sql，N'@ sql NVARCHAR（MAX）'，
 N' SELECT @@ SERVERNAME，DB_NAME（），actual_columns FROM dbo.table_name;'; 
  

如果table_name不存在，这会失败，所以如果你想方便错误处理。但这应该让你开始。

此外，请注意，并始终使用架构前缀。 背景。

I have the following code (cursor):

DECLARE @SN VARCHAR(20);
DECLARE @sql NVARCHAR(MAX); 
DECLARE C CURSOR LOCAL FAST_FORWARD
  FOR SELECT DISTINCT(SERVERNAME) FROM INSTALLATION 
    where DATABASETYPE = 'MsSql' AND SERVERNAME IN ('x');

OPEN C;

FETCH NEXT FROM C INTO @SN;
WHILE (@@FETCH_STATUS = 0)
BEGIN 
    PRINT @SN;
    -- you could loop here for each database, if you'd define what that is
   SELECT name 
FROM master.dbo.sysdatabases 
WHERE name not in ('master','model','msdb','tempdb');
    SET @sql = N'SELECT TOP 1 NAME FROM TABLE ';

    EXEC sp_executesql @sql;
    FETCH NEXT FROM C INTO @SN;
END 
CLOSE C;
DEALLOCATE C;

I would like to be able to loop through every server and execute a select statement on some (not all) of their databases.

The query is something like:

SELECT TOP 1 NAME FROM TABLE 

The server from where I am running the cursor has all the others as linked servers.

解决方案

DECLARE @SN VARCHAR(20);

DECLARE C CURSOR LOCAL FAST_FORWARD
  FOR SELECT DISTINCT(SERVERNAME) FROM TABLE 
  where SERVERNAME NOT IN ('SRV1','SRV2','SRV3');

OPEN C;

FETCH NEXT FROM C INTO @SN;
WHILE (@@FETCH_STATUS = 0)
BEGIN 
    PRINT @SN;
    -- you could loop here for each database, if you'd define what that is
    SET @sql = N'SELECT * FROM ' + @SN + '.master.dbo.TABLE;';
    EXEC sys.sp_executesql @sql;
    FETCH NEXT FROM C INTO @SN;
END 
CLOSE C;
DEALLOCATE C;

Changes:

1. There is no reason to use the default cursor options here - global, updatable, dynamic, scrollable, etc. Background.

2. As a habit / best practice, use sp_executesql and not EXEC(). While it doesn't really matter in this case, it can matter in others, so I'd prefer to always code the same way. Background.

3. Also, please get in the habit of terminating your statements with semi-colons. You'll have to, eventually. Background.

EDIT

Now that we have a little more information about your actual requirements, I suggest this bit of code. Oh, and look, no cursors (well, no explicit cursor declarations and all the scaffolding that comes with them)!

SET NOCOUNT ON;

DECLARE @dbs TABLE(SERVERNAME SYSNAME, DBNAME SYSNAME);

DECLARE @sql NVARCHAR(MAX) = N'';

-- first, let's get the databases on each server:

SELECT @sql += N'SELECT ''' + SERVERNAME + ''', name FROM '
 + QUOTENAME(SERVERNAME) + '.master.sys.databases
   WHERE database_id > 4 
   AND name NOT IN (N''somedb'',N''someotherdb'');' 
 FROM dbo.INSTALLATION 
   WHERE DATABASETYPE = 'MsSql' 
   AND SERVERNAME IN ('x');

INSERT @dbs EXEC sys.sp_executesql @sql;

SELECT @sql = N'';

-- now, build a command to run in each database context:

SELECT @sql += N'
  EXEC ' + QUOTENAME(SERVERNAME) + '.'
  + QUOTENAME(DBNAME) + '.sys.sp_executesql @sql;'
  FROM @dbs;

-- feel free to change the 3rd parameter here:

EXEC sys.sp_executesql @sql, N'@sql NVARCHAR(MAX)', 
  N'SELECT @@SERVERNAME, DB_NAME(), actual_columns FROM dbo.table_name;';

This will fail if table_name doesn't exist, so you may still have some work to do if you want to facilitate error handling. But this should get you started.

Also, please be conscious of, and consistently use, the schema prefix. Background.

这篇关于SQL Server游标 - 循环通过多个服务器并执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！