To get Database size in MB for SQL

Weekly Server Report for SQL

T-SQL:


IF OBJECT_ID('tempdb..#T','U') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T (DBName nvarchar(500),SizeinMB nvarchar(100),FreeSpaceinMB nvarchar(100))
GO

INSERT INTO #T 
EXEC sp_MSforeachdb 'USE ? SELECT ''?'' DBName,
sum(size/128.0) [Size in MB],sum(size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)[Free Space in MB]
FROM sys.master_files
where DB_NAME(database_id)=db_name()
group by DB_NAME(database_id)'
GO 

SELECT * FROM #T ORDER BY DBName