T-SQL Tip: Retrieving Database File Sizes

by
The following script generates T-SQL statements that when ran, they return file size information for all the user databases on a SQL Server instance.

–Script that generates T-SQL providing size information for all database files on a SQL Server Instance
SELECT ‘SELECT ”’+[name]+”’ as DBName,cast(f.name as varchar(25)) as DBFileName,f.Filename as PhysicalFileName,cast (round((f.size*8)/1024.0,2) as decimal(18,2)) as FileSizeinMB FROM ‘+[name]+’..SYSFILES f’
FROM SYS.SYSDATABASES
WHERE [name] not in
(
‘master’,
‘tempdb’,
‘model’,
‘msdb’
)
GO

For more info, check out the following MSDN links:

Views: 2,161