How to get Databases List from all the SQL Server Instances - SQL Server / TSQL Tutorial

Scenario: 

You are working as SQL Server developer, you are asked to get the list of databases with total number of databases on each of SQL Server Instance. You have a server in which all the SQL Server Instances are added as linked server.

Solution:

As SQL Server Instances are added as linked server, that made our life easy. We can write a script that can get the list of SQL Server Instances and then run the query on each SQL Server Instance to get the database Names.


In final Select query , I also added one more column, InstanceDBCount, this will provide us number of databases per instance.


--Declare Variables
DECLARE @Instance NVARCHAR(128)

IF Object_id('tempdb..##Result') IS NOT NULL
    DROP TABLE ##result

CREATE TABLE ##result (
    [ServerName] VARCHAR(128)
    ,[DatabaseName] VARCHAR(128)
    )

DECLARE cur CURSOR
FOR
SELECT NAME
FROM sys.servers

OPEN cur

FETCH NEXT
FROM cur
INTO @instance

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(max) = NULL

    SET @SQL = 'Insert into ##Result(
          [ServerName],
          [DatabaseName])
      
          select ''' + @Instance + '''
          ,name from  [' + @Instance + '].master.Sys.Databases 
          Where database_id>4'

    --Print SQL to See the queries generated by Dynamic SQL
    PRINT @SQL

    --Execute SQL Query generated above
    EXEC (@SQL)

    FETCH NEXT
    FROM cur
    INTO @Instance
END

CLOSE cur

DEALLOCATE cur

SELECT ServerName
    ,DatabaseName
    ,(
        SELECT count(*) AS DBCount
        FROM ##result I
        WHERE I.ServerName = O.ServerName
        ) AS InstanceDBCont
FROM ##Result O


I don't have Linked Server added to my instance, I executed above code on my local instance and got below output
How to execute Query on Multiple Linked Servers in SQL Server to get Database Names and Counts per SQL Server Instance

No comments:

Post a Comment