Sunday 9 September 2012

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