create procedure [dbo].[P__SpaceUsedView]
as
begin
if not exists (select 0 from tempdb..sysobjects where id = object_id(N'[dbo].[#temptableSpaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table #temptableSpaceinfo --创建临时结果存储表
(
nameinfo varchar(50) ,
rowsinfo int , reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20)
)
if not exists (select 0 from dbo.sysobjects where id = object_id(N'[dbo].[_MonitorDailySpaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table _MonitorDailySpaceinfo --创建汇总结果存储表
(
MonitorID int identity(1,1) not null,
MonitorDate datetime,
DBName nvarchar(100),
nameinfo varchar(200) ,
rowsinfo int , reserved int ,
datainfo int ,
index_size int ,
unused int
)
declare @databasename varchar(255) --表名称
declare @cmdsql nvarchar(max)
set @cmdsql = ''
DECLARE Info_cursor CURSOR FOR
SELECT name FROM sys.databases where name not in ('tempdb') -- tempdb 不支持该语法
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @databasename
WHILE @@FETCH_STATUS = 0
begin
set @cmdsql = ''
set @cmdsql = isnull(@cmdsql,'') + N'INSERT INTO #temptableSpaceinfo EXEC '+@databasename+'.sys.sp_MSforeachtable ''sp_spaceused ''''?'''''';'+char(13)+char(10);
execute sp_executesql @cmdsql
--显示表信息
insert into _MonitorDailySpaceinfo
([MonitorDate],[DBName] ,[nameinfo] ,[rowsinfo],
[reserved] ,[datainfo] ,[index_size] ,[unused])
select getdate(),@databasename, nameinfo,
rowsinfo,
cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) as reservedKB,
cast(left(ltrim(rtrim(datainfo)) , len(ltrim(rtrim(datainfo)))-2) as int) as datainfoKB,
cast(left(ltrim(rtrim(index_size)) , len(ltrim(rtrim(index_size)))-2) as int) as index_sizeKB,
cast(left(ltrim(rtrim(unused)) , len(ltrim(rtrim(unused)))-2) as int) as unusedKB
from tempdb..#temptableSpaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
delete from tempdb..#temptableSpaceinfo
FETCH NEXT FROM Info_cursor INTO @databasename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
/*
select
[MonitorID],
[MonitorDate],
[DBName],
[nameinfo],
[rowsinfo],
[reserved]/1024/1024 as [reservedGB],
[datainfo]/1024/1024 as [datainfoGB],
[index_size]/1024/1024 as [index_sizeGB],
[unused]/1024/1024 as [unused_sizeGB]
from [dbo].[_MonitorDailySpaceinfo]
where [reserved]/1024/1024 > 0
*/
end
