SQL Server: 19c Server Footprint Script

Hi Everyone, 

Here is the script that can be run against SQL server to get the DB server foot prints on storage. 

SET NOCOUNT ON;

DECLARE @DBTBL TABLE (ID INT IDENTITY(1,1), DBNAME SYSNAME)

DECLARE @DBSIZESTBL TABLE (SERVERNAME VARCHAR(25),FILEID INT,FILESIZEINGB DECIMAL(10,2),SPACEUSEDINGB DECIMAL(10,2),FREESPACEINGB DECIMAL(10,2),DATABASENAME VARCHAR(128),LOGICALFILENAME VARCHAR(25),FILENAME VARCHAR(250))

INSERT INTO @DBTBL SELECT NAME FROM SYS.DATABASES

DECLARE @SQL1 VARCHAR(MAX)

DECLARE @MINID INT,@MAXID INT;

SELECT TOP 1 @MINID = ID FROM @DBTBL ORDER BY ID ;

SELECT TOP 1 @MAXID = ID FROM @DBTBL ORDER BY ID DESC;

WHILE @MINID<=@MAXID AND @MINID>0

BEGIN

SELECT @SQL1 = 'USE ['+DBNAME+'];

SELECT substring(cast(@@SERVERNAME as varchar),0,25) SERVERNAME,

    a.FILEID,

    CONVERT(decimal(12,2),ROUND(a.size/128.000,2))/1024 as [FILESIZEINGB] ,

    CONVERT(decimal(12,2),ROUND(fileproperty(a.name,''SpaceUsed'')/128.000,2))/1024 as [SPACEUSEDINGB],

    CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2))/1024 as [FREESPACEINGB],

'''+DBNAME+''' AS [DATABASENAME],

    a.name as [LOGICALFILENAME],

    a.FILENAME as [FILENAME]

FROM

    ['+DBNAME+'].dbo.sysfiles a

'

FROM @DBTBL WHERE ID = @MINID;

INSERT INTO @DBSIZESTBL

EXEC (@SQL1);

SET @MINID=@MINID+1;

END

SELECT * FROM @DBSIZESTBL;

SELECT SUM(FILESIZEINGB) FILESIZEINGB_TOTAL,SUM(SPACEUSEDINGB) SPACEUSEDINGB_TOTAL,SUM(FREESPACEINGB) FREESPACEINGB_TOTAL, LEFT(FILENAME,3) FILEDRIVE FROM @DBSIZESTBL

GROUP BY LEFT(FILENAME,3)

SELECT DATABASENAME, SUM(FILESIZEINGB) FILESIZEINGB_TOTAL,SUM(SPACEUSEDINGB) SPACEUSEDINGB_TOTAL,SUM(FREESPACEINGB) FREESPACEINGB_TOTAL FROM @DBSIZESTBL

GROUP BY DATABASENAME

Output of the script is going to look like below screenshot.