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
No comments:
Post a Comment