Find the Space Utilization of all the databases in SQL Server

Find the Space Utilization of all the databases in SQL Server

Below query will give the space utilization of all the databases in a particular instance of SQL Server:

declare @cmd varchar(1000)

set @cmd=' USE [?]; SELECT

getdate() Report_Date ,a.file_id as [FileId],

a.name as [LogicalName],

CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)) as [FileSize(MB)],

CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'''

+'SpaceUsed'+''')/128.000,2)) as [SpaceUsed(MB)], CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'''

+'SpaceUsed'+'''))/128.000,2)) as [FreeSpace(MB)], round((CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'''

+'SpaceUsed'+''')/128.000,2))/ CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))*100,2) as [Pct_SpaceUsed],

round((CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'''

+'SpaceUsed'+'''))/128.000,2))/ CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))*100,2) as [Pct_FreeSpace]

FROM sys.database_files a LEFT OUTER JOIN sys.data_spaces b

ON a.data_space_id = b.data_space_id'

Create table #DBFilesDetails(Report_Date datetime,FileID int,LogicalName varchar(400),[FileSize(MB)] decimal(20,2),[SpaceUsed(MB)] decimal(20,2), [FreeSpace(MB)]

decimal(20,2),[Pct_Spaceused] decimal(20,2),[Pct_FreeSpace] decimal(20,2)) ;

insert #DBFilesDetails

EXEC sp_MSforeachdb @cmd ;

select * from #DBFilesDetails ;

--Drop table #DBFilesDetails ;

/* Drop the table by removing the comment in the above line after the output is saved */

/* Run the query by selecting from "declare in the first line to last "select Line " */

Hope the above query helps