Hi Gian,

The query worked for me. I have used sp_msforeachdb to get all database size. Below is the query.

drop table #temp1

create table #temp1

(name varchar(50),

database_size varchar(50),

Freespace varchar(50))

insert into #temp1(name,database_size,Freespace)

exec sp_msforeachdb

'use ?;SELECT database_name = db_name()

,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')

,''unallocated space'' = ltrim(str((

CASE

WHEN dbsize >= reservedpages

THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576

ELSE 0

END

), 15, 2) + '' MB'')

FROM (

SELECT dbsize = sum(convert(BIGINT, CASE

WHEN type = 0

THEN size

ELSE 0

END))

,logsize = sum(convert(BIGINT, CASE

WHEN type <> 0

THEN size

ELSE 0

END))

FROM sys.database_files

) AS files

,(

SELECT reservedpages = sum(a.total_pages)

,usedpages = sum(a.used_pages)

,pages = sum(CASE

WHEN it.internal_type IN (

202

,204

,211

,212

,213

,214

,215

,216

)

THEN 0

WHEN a.type <> 1

THEN a.used_pages

WHEN p.index_id < 2

THEN a.data_pages

ELSE 0

END)

FROM sys.partitions p

INNER JOIN sys.allocation_units a

ON p.partition_id = a.container_id

LEFT JOIN sys.internal_tables it

ON p.object_id = it.object_id

) AS partitions'

select * from #temp1

Thanks a lot.

😀