从多个服务器获取所有数据库名称

问题描述：

我们有多个SQL Server，其中大多数都是独立的。我需要创建一个存储过程/视图，将所有数据库名称插入到所有服务器的表中。从多个服务器获取所有数据库名称

有没有办法通过存储过程或视图来做到这一点？我没有任何PowerShell或.Net的经验。

这是我到目前为止。我只是无法弄清楚如何从服务器跳转到服务器，并将所有结果添加到真实的表格中。

CREATE TABLE ##temp 
(
DATABASE_NAME VARCHAR(100), 
DATABASE_SIZE INT, 
REMARKS VARCHAR(500) 
) 


INSERT into ##temp 
EXEC [sp_databases] 

--doing this to also get ServerName along with the db name. 
--When I insert into a real table, I'll seperate it into two columns plus remove "@[email protected]" 
update ##temp 
set DATABASE_NAME = (select @@SERVERNAME) + '@[email protected] ' + DATABASE_NAME 
where DATABASE_NAME not like '%@[email protected]%' 

select DATABASE_NAME from ##temp

你可以通过'Registered Servers'窗口对多个服务器执行查询 –

答

对于这种事情，至少有一台服务器与所有需要信息的服务器有链接连接是很好的。如果你这样做，那么你可以使用我写的这个小脚本：

-- (1) Create global temp table used to store results 
IF OBJECT_ID('tempdb..##databases') IS NOT NULL DROP TABLE ##databases; 
CREATE TABLE ##databases 
(
    serverDBID int identity, 
    serverName varchar(100), 
    databaseName varchar(100), 
    databaseSize decimal(20,6) 
); 

-- (2) Create and populate table variable used to collect server names 
DECLARE @servers TABLE(id int identity, serverName varchar(100)); 
INSERT @servers(serverName) 
SELECT name FROM sys.servers; 

-- (3) loop through each DB and collect database names into ##databases 
DECLARE @i int = 1, @serverName varchar(100), @db varchar(100), @sql varchar(8000); 
WHILE @i <= (SELECT COUNT(*) FROM @servers) 
BEGIN 
    SELECT @serverName = serverName FROM @servers WHERE id = @i; 

    SET @sql = 'INSERT ##databases(serverName, databaseName) SELECT '''[email protected]+ 
      ''', name FROM master.sys.databases'; 
    EXEC (@sql); 

    SET @i += 1; 
END; 

-- (4) Collect database sizes 
SET @i = 1; -- reset/re-use @i; 
WHILE @i <= (SELECT COUNT(*) FROM ##databases) 
BEGIN 
    SELECT @serverName = serverName, @db = databaseName 
    FROM ##databases 
    WHERE serverDBID = @i; 

    SET @sql = 
    'UPDATE ##databases 
    SET databaseSize = 
    (SELECT sum(size)/128. FROM ['[email protected]+'].['[email protected]+'].sys.database_files) 
    WHERE serverDBID = '+CAST(@i AS varchar(4))+';' 

    BEGIN TRY 
    EXEC (@sql); 
    END TRY 
    BEGIN CATCH 
    PRINT 'There was an error getting dbsize info for '[email protected]+' > '[email protected]; 
    END CATCH; 

    SET @i += 1; 
END; 

-- Final Output 
SELECT * FROM ##databases;

答

SQL Server Management Studio允许您使用Registered Servers功能执行query against multiple servers。这是添加在SQL Server 2008 as this tutorial shows，所以你不应该担心兼容性。

运行多台服务器的查询很简单：

从View菜单中，选择'注册的服务器。这将打开一个类似于显示单个服务器的对象的对象资源管理器的新窗口。
将所有服务器连接的详细信息将连接添加在Local Server Groups文件夹的本地服务器组文件夹
右键单击并选择New Query。您在此处输入的查询将运行所有注册的服务器。
要查找所有数据库运行select * from sys.databases或只是sp_databases

SSMS将收集来自所有服务器的结果，并在网格中显示它们。如果您希望结果转到单个服务器的表中，则必须将目标服务器作为链接服务器添加到所有其他服务器，并使用四部分名称来定位目标表，例如INSERT INTO myManagementServer.MyDb.dbo.ThatTable...

SQL服务器具有更强大的管理多台服务器的功能。您可以通过Central Management Server管理多台服务器，并通过策略将设置应用于多台服务器。这个功能也被加入2008年

在SQL Server 2008 R2的SQL Server Utility其中加入了去，甚至更远，并在管理仓库的报告收集诊断指标，从多台服务器，并将其存储性能数据。想象一下，能够看到例如多个服务器的存储和查询性能，或者过去X个月的可用空间趋势。

缺点是历史数据需要空间。收集它也需要向所有受监视的服务器添加一些存储过程，尽管这是自动完成的。

从多个服务器获取所有数据库名称

相关推荐