TSQL to find database size

Earlier today I had the need to calculate both the size of my SQL database files as well as the amount of used space in the aforementioned database files. While I was at it I added a percent used column. I found lots of posts out there some horribly complicated and some way too simple and not providing enough detail.

The FILEPROPERTY (http://technet.microsoft.com/en-us/library/ms188401.aspx) was the key here and what I used to simply calculate the amount of space used within the database files.

Also make sure you replace the "<your_database_name_here>" at the beginning of the query or make sure you have the appropriate database selected in SSMS.

-- Title: SQL - Get Database Size -- Description: Displays Database Size, Size Used, and Percent Used -- Author: David Silva USE <your_database_name_here> -- Returns database size as it would be displayed in database properties SELECT ROUND(SUM(size)/128.0,2)*0.000976562 As GB_size FROM sys.sysfiles -- Returns data for database size, file usage, %used. Can be exported to Excel for Pivot rollup SELECT ROUND((size)/128.0,2)*0.000976562 As GB_Size, name, [filename], ((convert(Float,FILEPROPERTY (name, 'SpaceUsed'))) * (8192.0/1048576)) * 0.000976562 As GB_Used, (((convert(Float,FILEPROPERTY (name, 'SpaceUsed'))) * (8192.0/1048576)) * 0.000976562)/(ROUND((size)/128.0,2)*0.000976562) As '% Used' FROM sys.sysfiles