SQL Server Databases size usage details

Set nocount on

Declare @cmd varchar(1000)

set @cmd=' USE [?]; SELECT getdate() Report_Date , db_name() DBName,a.type_desc File_Type,

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,DBName varchar(200),File_Type varchar(20),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