[SQL]Database Files Sizes in All Databases

SQL has lots ways to get information that you needed. On the other way, they will confuse you. (Sys.databases, sys.database_files, and dbo.sysfiles)

Because you cannot remember which way is fit your situation or when your boss comes to you and he says: “I want the result right now!!”

What are you gonna do?

There are 3 system tables that you are able to oversee the Database Files info

Sys.databases, sys.database_files, and dbo.sysfiles

• If you want to see separated size of mdf and ldf

A. Using sys.databases combined with sys.database_files

•  If you want just see total size sum of mdf and ldf

B. Using sys.databases combined with dbo.sysfiles

Below are two examples to show the results

--A.Using sys.databases combined with sys.database_files
IF OBJECT_ID('tempdb..#FileSizes') IS NOT NULL
 DROP TABLE #FILESIZES;
CREATE TABLE #FileSizes ([DBName] sysname, [File Name] varchar(max), [Physical Name] varchar(max),
[Size] DECIMAL(12,2))
DECLARE @SQLSTATEMENT nvarchar(max)
SET @SQLSTATEMENT = ''
SELECT @SQLSTATEMENT = @SQLSTATEMENT + 'USE' + QUOTENAME(name) + '
INSERT INTO #FileSizes
SELECT ' + QUOTENAME(name,'''') + ', [Name], [Physical_Name], ((size*8.0)/1024.0) FROM sys.database_files ' 
FROM sys.databases
EXECUTE (@SQLSTATEMENT)
SELECT J.DBName AS [DBName], J.[File Name] AS [FileLogicalName], J.[Physical Name] AS [PhysicalName], J.Size AS [PhysicalSize_MB],'http://msdn.microsoft.com/en-us/library/ms178534.aspx' AS [HelpURL] from #FileSizes J ORDER BY [DBName], [File Name]

--B.Using sys.databases combined with dbo.sysfiles
DECLARE @SQLSTATEMENT varchar(max)
SELECT @SQLSTATEMENT =coalesce(@SQLSTATEMENT + char(13) + 'UNION ALL 
' ,'') + 'SELECT ''' + [name] + ''' AS [DBNAME],' + 
'sum(size * 8 /1024.0) AS [TOTALDBSize_MB] FROM ' + quotename(name) + '.dbo.sysfiles' 
FROM sys.databases
ORDER BY [name]
EXECUTE (@SQLSTATEMENT)

-----------------------------------------------------------------------------------
Or you can use the undocumented Store Procedure sp_MsForEachDB