This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing one T-SQL script to find all Databases from the all the SQL Server Instances.
This script is very helpful for Database Administrators, who is managing different instances of SQL Server.
Using this single T-SQL script, we can get the all Databases from all the running SQL Server Instances.
Below is a script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
DECLARE @InstanceName NVARCHAR(128) CREATE TABLE #Databases ( [ServerName] VARCHAR(128) ,[DatabaseName] VARCHAR(128) ) DECLARE SQLCur CURSOR FOR SELECT NAME FROM sys.servers OPEN SQLCur FETCH NEXT FROM SQLCur INTO @InstanceName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @SQL VARCHAR(MAX) = NULL SET @SQL = ' INSERT INTO #Databases ([ServerName],[DatabaseName]) SELECT ''' + @InstanceName + ''' ,name FROM [' + @InstanceName + '].master.Sys.Databases WHERE database_id > 4 ' EXEC (@SQL) FETCH NEXT FROM SQLCur INTO @InstanceName END CLOSE SQLCur DEALLOCATE SQLCur SELECT ServerName ,DatabaseName ,( SELECT count(*) AS DBCount FROM #Databases AS I WHERE I.ServerName = O.ServerName ) AS InstanceDBCont FROM #Databases O GO DROP TABLE #Databases GO |
Leave a Reply