03-SQLServer查看整个实例的总大小

1、脚本

SET NOCOUNT ON
declare @sql nvarchar(max)
declare @sql2 nvarchar(max)
declare @dbname nvarchar(50)
create table #db_log_size (database_name nvarchar(100),dbsize nvarchar(200),logsize nvarchar(200))
create table #result (database_name nvarchar(100),reservedpages nvarchar(200),usedpages nvarchar(200),pages nvarchar(200))


declare cur CURSOR for
select name from sys.databases where state=0
open cur
fetch next from cur into @dbname
while @@FETCH_STATUS = 0
begin
set @sql='
select 
'''+@dbname+''' as database_name,
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 '+@dbname+'.dbo.sysfiles'
insert into #db_log_size exec sp_executesql @sql

set @sql2='
select 
'''+@dbname+''' as database_name,
sum(a.total_pages) as reservedpages,
sum(a.used_pages) as usedpages,
sum(
CASE
When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
When a.type <> 1 and p.index_id < 2 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
) as pages
from '+@dbname+'.sys.partitions p join '+@dbname+'.sys.allocation_units a on p.partition_id = a.container_id
left join '+@dbname+'.sys.internal_tables it on p.object_id = it.object_id'
insert into #result exec sp_executesql @sql2

fetch next from cur into @dbname
end
close cur
deallocate cur

select 
d.database_name as '数据库名称',
ltrim(str((convert (dec (15,2),d.dbsize) + convert (dec (15,2),d.logsize)) * 8192 / 1048576,15,2) ) as 'dbsize',
ltrim(str((case when d.dbsize >= r.reservedpages then
(convert (dec (15,2),d.dbsize) - convert (dec (15,2),r.reservedpages)) 
* 8192 / 1048576 else 0 end),15,2) ) as 'unallocate_space',
ltrim(str(convert(dec(15,2),(convert (dec (15,2),r.reservedpages) + convert (dec (15,2),d.logsize)) 
/ (convert (dec (15,2),d.dbsize) + convert (dec (15,2),d.logsize))) * 100)) + '%' as '空间使用率'

into #test
from #db_log_size d 
inner join #result r on d.database_name = r.database_name

 

drop table #db_log_size
drop table #result

select CONVERT(VARCHAR(100),convert(dec (15,2),(sum(convert(dec (15,2),dbsize))/1024))) + ' GB' as 'dbsize' ,
CONVERT(VARCHAR(100),convert(dec (15,2),(sum(convert(dec (15,2),dbsize)-convert(dec (15,2),unallocate_space))/1024))) + ' GB' as 'use_size'

from #test
drop table #test

go

2、结果示例