T-SQL Query ----->Need to find total Number of DBs and size of all DBs.

Question

I am trying to find the following info:-

How to get it?

Answer 1

Hi @chuck DM
Please check this query:

-- (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 serverName  
      ,COUNT(DISTINCT databaseName) AS [Total Numbers of DBs]  
      ,SUM(databaseSize) AS [Size of All DBs]  
FROM ##databases  
GROUP BY serverName  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

By the column "ServerName" in your screenshot I guess you want to get the information about database size over several SQL Server?
Create a CMS = Central Management Server
https://learn.microsoft.com/en-us/sql/ssms/register-servers/create-a-central-management-server-and-server-group?view=sql-server-ver16
then you can query in SSMS the informations over all registered SQL Server.