How to get the Size of Databases in MS SQL Server

To get the size of SQL Server Databases on an instance run the below query. This query has three sections, you may run this query once or you can run each section one by one. The result set will give you the name of Database, Size in MBs and Size in GBs. 

/*Create a temp Table*/
USE MASTER GO CREATE TABLE #FileSize
(
  dbName NVARCHAR(128), 
  FileName NVARCHAR(128), 
  type_desc NVARCHAR(128), 
  CurrentSizeMB DECIMAL(10, 2), 
  FreeSpaceMB DECIMAL(10, 2)
);
/*Insert into the table from system stored procedure*/
INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB)
exec sp_msforeachdb 'use [?]; 

 SELECT DB_NAME() AS DbName, 

        name AS FileName, 

        type_desc,

        size/128.0 AS CurrentSizeMB,  

        size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB

FROM sys.database_files

WHERE type IN (0,1);';
/*Select the result from the Table*/
SELECT 
  DBName, 
  sum(CurrentSizeMB) as Current_SizeInMBs, 
  sum(CurrentSizeMB)/ 1024 as CurrentSizeInGBs 
FROM 
  #FileSize
WHERE 
  dbName NOT IN (
    'distribution', 'master', 'model', 
    'msdb', 'ReportServerTempDB', 'ReportServer', 
    'tempdb'
  ) 
Group by 
  DBName