ProjectSMM.com
Gonzo TechNet
How to list MS SQL database file types, properties, and sizes
This script will return a resultset listing the names, properties, and sizes of the database files for the database it is executed against.
It can be run in either SQL 2000 or 2005.
The script will check for the existence of "sys.database_files".
If it exists, it will be presumed that the current server is SQL 2005, otherwise, it will be presumed to be SQL 2000.
Use at your own risk, plagerize as neccessary, don't eat yellow snow...
/*
===========================================================
DATABASE File properties and sizes
Script to list database files/properties/sizes
===========================================================
*/
SET NOCOUNT ON
GO
IF object_id('sys.database_files') IS NOT NULL
BEGIN
--SQL 2005
SELECT
[DBName] = db_name()
,[DBFileID] = a.file_id
,[DBFileName] = a.name
,[FileType] = a.type_desc
,[FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2))
,[UsedSpaceMB] = convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2))
,[UnusedSpaceMB] = convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))
FROM sys.database_files AS a
ORDER BY [FileType],[DBFileName]
END
ELSE
BEGIN
--SQL 2000
SELECT
[DBName] = db_name()
,[DBFileID] = a.fileid
,[DBFileName] = a.name
,[FileType]
= CASE
--MUST check for LOG first!
WHEN (status & 0x40) > 0 THEN 'Log'
WHEN (status & 0x2) > 0 THEN 'Data'
ELSE 'Unknown'
END
,[FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2))
,[UsedSpaceMB] = convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2))
,[UnusedSpaceMB] = convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))
FROM sysfiles AS a
ORDER BY [FileType],[DBFileName]
END
GO