Thursday, April 12, 2012

TSQL Script to return files and Free Space

Here is the script

 

;WITH c AS (
SELECT  g.name AS [FileGroupName]
       ,s.type_desc
       ,s.physical_name AS [FileName]
       ,s.size * CONVERT(FLOAT, 8) AS [Size]
       ,CAST(CASE s.type
               WHEN 2 THEN 0
               ELSE CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8)
             END AS FLOAT) AS [UsedSpace]
FROM    sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ( ( s.type = 2
                                        OR s.type = 0
                                      )
                                      AND s.database_id = DB_ID()
                                      AND ( s.drop_lsn IS NULL )
                                    )
                                    AND ( s.data_space_id = g.data_space_id )
)
SELECT *, [size] - [UsedSpace] RemainingSpace
FROM c 

0 comments:

Post a Comment