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