SQL Tidbits
Have you ever had a need to see storage information for all your databases in a SQL Server? As they say there are many ways to skin a cat all based on your need. Back in the day I was taught to use
SYS.SYSFILES, its handy and it does give you what you need but suppose you wanted a picture of your entire SQL Server? Then this solution is not viable that’s when we divert to
SYS.MASTER_FILES according to MSDN
http://msdn.microsoft.com/en-us/library/ms186782.aspx this system view contain a row per file of a database as stored in the Master database simply put as contains all the information you need to know regarding your database(s) storage questions.
Here is a query that sums it all up excluding the system databases.
SELECT DB_NAME([database_id]) AS N'database_name'
, [name] AS N'logical_name'
, [Physical_Name]
,(size*8)/1024 as 'Physical_File_Size (MB)'
, SUM((size*8)/1024) Over(PARTITION by [database_id]) as 'DatabaseSize (MB)'
, [growth]
, [is_percent_growth]
FROM SYS.MASTER_FILES WHERE [database_id] > 4;
I hope this saves someone a headache.