SQL Server Database Space Utilization | Check Database Size in SQL Server

Step 1:

create table #tempA
(fileid int,
file_Size numeric(18,2),
space_used numeric(18,2),
free_space numeric(18,2),
name varchar(100),
filename_ varchar(1000))

-----------------------------------------------------------
Step 2:

insert into #tempA
exec sp_MSforeachdb 'use [?]
select
a.FILEID,
[FILE_SIZE_MB] = 
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a'

-----------------------------------------------------------
Step 3:

select A.database_name,
SUM(Log_size_mb)Log_size_mb,
SUM(Row_size_mb)Row_size_mb,
Sum(Total_size_mb)Total_size_mb,
SUM(space_used)space_used_MB,SUM(B.free_space)free_space_mb 
from (SELECT 
database_name = DB_NAME(database_id),name
, Log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, Row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, Total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
--WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id,name)A
left join #tempA B
on A.name=B.name
group by A.database_name

drop table #tempA

-----------------------------------------------------------
OutPut: