SQL Query to find database size of all databases in server

In some situations we might need to find the sizes of all databases in the server. In case we dont have masterfile access we can use the following query:


DECLARE @DBsize_sql VARCHAR(4000)
DECLARE @DBsize_table TABLE (DBName VARCHAR(200), DbFileName VARCHAR(250), CurrentSizeMB numeric(18,4), FreeSpaceMB numeric(18,4))
SET @DBsize_sql='Use ? SELECT DB_NAME() AS DbName, Name AS DbFileName, size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files'
INSERT @DBsize_table
EXEC sp_MSforeachdb @command1=@DBsize_sql
SELECT * FROM @DBsize_table
ORDER BY DbFileName