T-SQL Tip: Retrieving Database File Sizes

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:

sys.sysdatabases

sys.sysfiles

Artemakis Artemiou

Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).

Cookies