来自多个SQL服务器的数据库名称

Question

我被一条SQL查询卡住了。如何一次性从多个SQL服务器获取所有数据库的名称？我在一个表中有服务器IP。

- Karbas

---

我认为你不可能在一个查询中完成这个任务。你可能需要编写一个脚本或应用程序来循环遍历每个IP地址。 - Ash Burlaczenko

@AshBurlaczenko 请看一下，我用 WHILE 循环完成了它。如果需要，循环可以替换为 UNION。 - Kaf

Answer 1

您可以使用以下方法获取数据库信息。

SELECT * from sys.databases

如果您想从其他服务器了解这个信息，纯粹的T-SQL语言需要链接服务器。

更多信息

• MSDN - 链接服务器
• MSDN - sp_addlinkedserver (Transact-SQL)

评论后更新

您说您不能链接服务器。另一种选择是创建一个.NET CLR对象并将它们嵌入到服务器中。

然后，您可以连接到其他服务器，而无需将它们链接在一起，并执行SQL语句。

更多信息

• MSDN - 在SQL Server 2005中使用CLR集成

- dknaack

---

好的，我甚至尝试创建了一个 SSIS 包来解决这个问题。但是它也没有任何动态更改服务器的选项...而且我们需要在生产服务器上运行此程序。所以我不应该链接服务器。 - Karbas

@user920431 我的回答已经扩展了。 - dknaack

Answer 2

这将返回当前服务器上的所有数据库

EXEC sp_databases

如果您知道其他服务器的IP地址，您可以使用sp_addlinkedserver将它们链接起来并获得所需的结果。 编辑：以下是一个完整的答案，如上所述，但使用sys.databases。

DECLARE @T AS TABLE (dbName VARCHAR(250),ipAddress VARCHAR(20))
DECLARE @SERVERS AS TABLE (Id INT IDENTITY(1,1),serverIp VARCHAR(20),
                           rmUser VARCHAR(20),rmPassword VARCHAR(20))
DECLARE @ipAdd AS VARCHAR(20),@user AS VARCHAR(20), @pw AS VARCHAR(20),@rowCount AS INT

SELECT @rowCount = 1

--ADD THE LIST OF SERVERS WITH LOGIN USER NAME AND PASSWORDS HERE
INSERT INTO @SERVERS (serverIp,rmUser,rmPassword) 
    VALUES ('serverIp1','rmuser1','rmpassword1'), ('serverIp2','rmuser2','rmpassword2')

WHILE EXISTS(SELECT Id FROM @SERVERS WHERE Id = @rowCount)
BEGIN
SELECT @ipAdd = serverIp, @user= rmUser, @pw = rmPassword 
    FROM @SERVERS WHERE Id =  @rowCount

    --Link the server and add login
EXEC sp_addlinkedserver @ipAdd,N'SQL Server';
EXEC sp_addlinkedsrvlogin @rmtsrvname=@ipAdd, 
                          @useself='false',
                          @rmtuser=@user,
                          @rmtpassword=@pw

     --INSERT results into a temp view and then into @T
 EXEC('CREATE VIEW vTemp AS SELECT name FROM ['+@ipAdd+'].master.sys.databases')
 INSERT INTO @T  SELECT name,@ipAdd FROM vTemp

     --Drop view
 DROP VIEW vTemp

     --Drop login and link to the remort server
 EXEC sp_droplinkedsrvlogin @ipAdd, NULL
 EXEC sp_dropserver @ipAdd, NULL;

     SELECT @rowCount = @rowCount +1

END

--FINALLY YOUR RESULTS
SELECT * FROM @T

- Kaf

---

是的，这个可以运行。我需要和我的上级确认是否可以添加或删除链接服务器... :) - Karbas