统计instant中各数据库大小的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
use master
go
create procedure dbo.uspGetDBSizes
as
if OBJECT_ID('tempdb.dbo.#DB_SIZE') is not null drop table #DB_SIZE
create table #DB_SIZE
(datebase_name varchar(1000),dbsize bigint,logsize bigint,database_size varchar(1000),[unallocated space] varchar(1000))

if OBJECT_ID('tempdb.dbo.#SQL') is not null drop table #SQL
select rw=ROW_NUMBER() over( order by name) , Tsql='insert #DB_SIZE(datebase_name,dbsize,logsize) select '''+name+''',dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
	, logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
	from ['+name+'].dbo.sysfiles' into #SQL from master.dbo.sysdatabases

declare @iPoint int=1
declare @tsql varchar(max)
declare @DBCount int
select @DBCount=max(rw) from #SQL
while @iPoint<=@DBCount
begin
	select @tsql=tsql from #SQL where rw=@iPoint
	exec (@tsql)
	select @iPoint=@iPoint+1
end

update a set database_size = ltrim(str((convert (dec (15,2),dbsize) + convert (dec (15,2),logsize)) 
			* 8192 / 1048576,15,2) + ' MB') from #DB_SIZE a
select * from #DB_SIZE order by dbsize+logsize desc