CODEWITHSUNDEEP
t-sqlsql-server-2008-r2ssrs-2008-r2
NonProgrammer
NonProgrammer

Reputation: 1387

Get all database names from multiple servers

We have multiple SQL Servers and most of them are standalone. I am in need of creating a stored procedure / view that would insert all database names into a table from all servers.

Is there a way to do this via a stored procedure or a view? I do not have any powershell or .Net experience.

Here's what I have so far. I just can't figure out how to 'jump' from server to server and add all my results into a real table.

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 "@_!_@"
update ##temp
set DATABASE_NAME =   (select  @@SERVERNAME ) + '@_!_@ ' + DATABASE_NAME
where DATABASE_NAME not like '%@_!_@%' 

select DATABASE_NAME from ##temp

Upvotes: 0

Views: 1939

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

For this kind of thing it's good to have at least one server that has a linked connection to all the servers you need information for. If you do then you can use this little script I just wrote:

-- (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 '''+@serverName+
             ''', 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 ['+@serverName+'].['+@db+'].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 '+@serverName+' > '+@db;
  END CATCH;

  SET @i += 1;
END;

-- Final Output
SELECT * FROM ##databases;

Upvotes: 1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131364

SQL Server Management Studio allows you to execute a query against multiple servers using the Registered Servers feature. This was added in SQL Server 2008 as this tutorial shows so you shouldn't worry about compatibility.

Running multi-server queries is easy:

  1. From the View menu, select `Registered Servers. This will open a new window similar to the Object Explorer that displays the objects of a single server.
  2. Add connections for all your servers connection details in the Local Server Groups folder
  3. Right-click on the Local Server Groups folder and select New Query. The query you enter here will run an all registered servers.
  4. To find all databases run select * from sys.databases or just sp_databases

SSMS will collect the results from all servers and display them in a grid. If you want the results to go to a single server's table though, you'll have to add the target server as a linked server to all others and use a four-part name to target the target table, eg INSERT INTO myManagementServer.MyDb.dbo.ThatTable...

SQL Server has even more powerful features for managing multiple servers. You can administer multiple servers through a Central Management Server and apply settings to multiple servers through policies. That feature was also added in 2008.

In SQL Server 2008 R2 the SQL Server Utility was added which goes even farther and collects diagnostics, metrics, performance data from multiple servers and stores it in a management warehouse for reporting. Imagine being able to see eg storage and query performance for multiple servers, or free space trends for the last X months.

The drawbacks are that historical data needs space. Collecting it also requires adding some stored procedures to all monitored servers, although this is done automatically.

Upvotes: 2

Related Questions