Command to find database size

Command to find database size 

Below Command provides you the output of each files(Total ,free,used,) associated with database

 SELECT [name]
,CONVERT(NUMERIC(10,2),ROUND([size]/128.,2)) AS [Size] 
,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],'SpaceUsed')/128.,2)) AS [Used] 
,CONVERT(NUMERIC(10,2),ROUND(([size]-FILEPROPERTY([name],'SpaceUsed'))/128.,2)) AS [Unused]
,max_size/128 as Max_size_MB
FROM [sys].[database_files]
go

OUTPUT:

-------------------------------------------------------------------------------------------------------------

Below Command provides you the output of each DB (Datafile & logfile)

SELECT DatabaseName,DataSize,LogSize,DataSize+LogSize AS TotalSize, RecoveryType,AutoShrink  
FROM   
(SELECT DBID,  
CASE Sum(size*8/1024)   
    WHEN 0 THEN 1   
    ELSE Sum(size*8/1024)   
END AS DataSize  
FROM master..sysaltfiles  
WHERE GroupID <> 0  
GROUP BY DBID) q1  
INNER JOIN  
(SELECT DBID,  
CASE Sum(size*8/1024)   
    WHEN 0 THEN 1   
    ELSE Sum(size*8/1024)  
END AS LogSize  
FROM master..sysaltfiles  
WHERE GroupID = 0  
GROUP BY DBID) q2   
ON q1.DBID = q2.DBID  
INNER JOIN  
(SELECT DBID, [name] AS DatabaseName,  
CONVERT(varchar(100),DATABASEPROPERTYEX([Name], 'Collation')) AS Collation,  CONVERT(varchar(100),DATABASEPROPERTYEX([Name], 'Recovery')) AS RecoveryType,  
CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoClose'))  
    WHEN 0 THEN 'NO'  
    WHEN 1 THEN 'Yes'  
END  AS AutoClose,  
CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoShrink'))  
    WHEN 0 THEN 'NO'  
    WHEN 1 THEN 'Yes'  
END AS AutoShrink  
FROM master.dbo.sysdatabases) q3  
ON q1.DBID = q3.dbid  

ORDER BY DatabaseName

OUTPUT:

-------------------------------------------------------------------------------------------------------------

Below Command provides you the output of each DB (Total , Used ,Free)

exec sp_msforeachdb
'use [?];
select DB_NAME() AS ''DbName'',
sum(size)/128.0 AS ''File_Size_MB'',
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as ''Space_Used_MB'',
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS ''Free_Space_MB'',
sum(round((CAST(FILEPROPERTY(name, ''SpaceUsed'') AS float)/size)* 100 ,2)) AS  ''Percentage Used''
from sys.database_files  where type=0 and Db_Name() not in (''master'') group by type'

OUTPUT:

-------------------------------------------------------------------------------------------------------------