获取所有库的大小

if OBJECT_ID('tempdb..#tmp') is not null drop table #tmpCREATE table #tmp(dbName nvarchar(50),total_MB decimal(18,2),logicName nvarchar(50),size_MB decimal(18,2),[fileName] nvarchar(500))EXEC sp_MSforeachdb N'if "?" not in ("master","tempdb","model","msdb")begininsert into #tmp(dbName,logicName,size_MB,[fileName])select "?",name as logicName, convert(float,size) * (8192.0/1024.0)/(1024) as size_MB,f.filenamefrom ?.dbo.sysfiles fend';with cte as (select dbName,sum(size_MB) as total_MB from #tmp group by dbName)update #tmp set total_MB=b.total_MB from cte as b where b.dbName=#tmp.dbNameselect * from #tmp order by total_MB descdrop table #tmp