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

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

最后

以上就是精明菠萝为你收集整理的统计instant中各数据库大小的存储过程的全部内容，希望文章能够帮你解决统计instant中各数据库大小的存储过程所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。