create table#temp1
([数据库名] varchar(50),[数据库大小(MB)] dec (15,2),[未分配空间(MB)] dec (15,2),[保留(MB)] dec (15,2),[数据(MB)] dec (15,2),[索引(MB)] dec (15,2),[未使用(MB)] dec (15,2))insert into#temp1execsp_msforeachdb'use ?;
select
db_name(),
convert(dec (15,2),(convert(dec (15,2),dbsize) + convert (dec (15,2),logsize)) * 8192 / 1048576),
convert(dec (15,2),(case when dbsize >= reservedpages then (convert (dec (15,2),dbsize) - convert (dec (15,2),reservedpages)) * 8192 / 1048576 else 0 end)),
convert(dec (15,2),reservedpages * 8192 / 1048576.),
convert(dec (15,2),pages * 8192 / 1048576.),
convert(dec (15,2),(usedpages - pages) * 8192 / 1048576.),
convert(dec (15,2),(reservedpages - usedpages) * 8192 / 1048576.)
FROM
(
SELECT sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) AS dbsize
, sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) AS logsize
from dbo.[sysfiles]
) a
LEFT JOIN
(
select sum(a.total_pages) AS reservedpages,
SUM(a.used_pages) AS usedpages,
SUM(
CASE
-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
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
) AS pages
from [sys].[partitions] p join [sys].[allocation_units] a on p.partition_id &#61; a.container_id
left join [sys].[internal_tables] it on p.object_id &#61; it.object_id
) b ON 1&#61;1&#39;
select * from #temp1 ORDER BY [数据库大小(MB)] desc
drop table #temp1