重写系统存储过程:sp_spaceused
存储过程脚本:
Use
master
Go
If Object_id ( ' sp_TableSpace ' ) Is Not null
Drop Proc sp_TableSpace
Go
/*
查询数据库中的表、索引视图或SQL Server2005 Service Broker队列所使用的磁盘空间
Andy 2008-10-28
*/
Create Proc sp_TableSpace
(
@dbname sysname = null ,
@objectname sysname = null
)
As
Declare
@object_id int ,
@type char ( 2 ),
@sql nvarchar ( 4000 )
Set Nocount On
Set @dbname = Isnull ( @dbname , db_name ())
If @dbname Is Null
Begin
Raiserror 50001 N ' 无效的数据库名! '
Return 1
End
If @objectname Is Not Null
Begin
Set @sql = N ' Use ' + @dbname + ' ;
Select @object_id = object_id, @type = type From sys.objects Where object_id = object_id(@objectname)
If @type = '' SQ ''
Select @object_id = object_id From sys.internal_tables Where parent_id = @object_id and internal_type = 201
'
Exec sp_executesql @sql ,N ' @objectname sysname,@object_id int Output,@type char(2) Output ' , @objectname , @object_id Output, @type Output
If @object_id Is Null
Begin
Raiserror ( 15009 , - 1 , - 1 , @objectname , @dbname )
Return 1
End
IF @type NOT IN ( ' U ' , ' S ' , ' V ' , ' SQ ' , ' IT ' )
Begin
Raiserror ( 15234 , - 1 , - 1 )
Return 1
End
End
Set @sql = N ' Use ' + @dbname + ' ;
;With t1 As
(
Select c.name As object_name,
Sum(b.total_pages) As Reservedpages,
Sum(b.used_pages) As usedpages,
Sum(Case When b.type <> 1 Then b.used_pages When a.index_id < 2 Then b.data_pages Else 0 End) As pages,
Sum(Case When a.index_id < 2 And b.type = 1 Then a.rows Else 0 End) As [rowCount]
From sys.partitions As a
Inner Join sys.allocation_units As b On b.container_id=a.partition_id
Inner Join sys.objects As c On c.object_id=a.object_id
Where c.type In ( '' U '' , '' S '' , '' V '' , '' IT '' ) And
(c.name=@objectname Or @objectname Is null)
Group By c.name
Union All
Select d.name As object_name,
Sum(b.total_pages) As Reservedpages,
Sum(b.used_pages) As usedpages,
0 As pages,
0 As [rowCount]
From sys.partitions As a
Inner Join sys.allocation_units As b On b.container_id=a.partition_id
Inner Join sys.internal_tables c On c.object_id=a.object_id And c.internal_type In (202,204)
Inner Join sys.objects d On d.object_id=c.parent_id And d.type= '' SQ ''
Where d.name=@objectname Or @objectname Is null
Group By d.name
)
,t2 As
(Select object_name,Sum(Reservedpages) As Reservedpages,Sum(usedpages) As usedpages,Sum(pages) As pages,Sum([rowCount]) As [rowCount] From t1 Group By object_name)
Select object_name,
[rowCount] As rows,
Rtrim(str(reservedpages * 8192 / 1024.,15,0) + '' KB '' ) As reserved,
Rtrim(str(pages * 8192 / 1024.,15,0) + '' KB '' ) As data,
Rtrim(str((usedpages - pages) * 8192 / 1024.,15,0) + '' KB '' ) As index_size,
Rtrim(str((reservedpages - usedpages) * 8192 / 1024.,15,0) + '' KB '' ) As unused
From t2
'
Exec sp_executesql @sql ,N ' @objectname sysname ' , @objectname
Go
If Object_id ( ' sp_TableSpace ' ) Is Not null
Drop Proc sp_TableSpace
Go
/*
查询数据库中的表、索引视图或SQL Server2005 Service Broker队列所使用的磁盘空间
Andy 2008-10-28
*/
Create Proc sp_TableSpace
(
@dbname sysname = null ,
@objectname sysname = null
)
As
Declare
@object_id int ,
@type char ( 2 ),
@sql nvarchar ( 4000 )
Set Nocount On
Set @dbname = Isnull ( @dbname , db_name ())
If @dbname Is Null
Begin
Raiserror 50001 N ' 无效的数据库名! '
Return 1
End
If @objectname Is Not Null
Begin
Set @sql = N ' Use ' + @dbname + ' ;
Select @object_id = object_id, @type = type From sys.objects Where object_id = object_id(@objectname)
If @type = '' SQ ''
Select @object_id = object_id From sys.internal_tables Where parent_id = @object_id and internal_type = 201
'
Exec sp_executesql @sql ,N ' @objectname sysname,@object_id int Output,@type char(2) Output ' , @objectname , @object_id Output, @type Output
If @object_id Is Null
Begin
Raiserror ( 15009 , - 1 , - 1 , @objectname , @dbname )
Return 1
End
IF @type NOT IN ( ' U ' , ' S ' , ' V ' , ' SQ ' , ' IT ' )
Begin
Raiserror ( 15234 , - 1 , - 1 )
Return 1
End
End
Set @sql = N ' Use ' + @dbname + ' ;
;With t1 As
(
Select c.name As object_name,
Sum(b.total_pages) As Reservedpages,
Sum(b.used_pages) As usedpages,
Sum(Case When b.type <> 1 Then b.used_pages When a.index_id < 2 Then b.data_pages Else 0 End) As pages,
Sum(Case When a.index_id < 2 And b.type = 1 Then a.rows Else 0 End) As [rowCount]
From sys.partitions As a
Inner Join sys.allocation_units As b On b.container_id=a.partition_id
Inner Join sys.objects As c On c.object_id=a.object_id
Where c.type In ( '' U '' , '' S '' , '' V '' , '' IT '' ) And
(c.name=@objectname Or @objectname Is null)
Group By c.name
Union All
Select d.name As object_name,
Sum(b.total_pages) As Reservedpages,
Sum(b.used_pages) As usedpages,
0 As pages,
0 As [rowCount]
From sys.partitions As a
Inner Join sys.allocation_units As b On b.container_id=a.partition_id
Inner Join sys.internal_tables c On c.object_id=a.object_id And c.internal_type In (202,204)
Inner Join sys.objects d On d.object_id=c.parent_id And d.type= '' SQ ''
Where d.name=@objectname Or @objectname Is null
Group By d.name
)
,t2 As
(Select object_name,Sum(Reservedpages) As Reservedpages,Sum(usedpages) As usedpages,Sum(pages) As pages,Sum([rowCount]) As [rowCount] From t1 Group By object_name)
Select object_name,
[rowCount] As rows,
Rtrim(str(reservedpages * 8192 / 1024.,15,0) + '' KB '' ) As reserved,
Rtrim(str(pages * 8192 / 1024.,15,0) + '' KB '' ) As data,
Rtrim(str((usedpages - pages) * 8192 / 1024.,15,0) + '' KB '' ) As index_size,
Rtrim(str((reservedpages - usedpages) * 8192 / 1024.,15,0) + '' KB '' ) As unused
From t2
'
Exec sp_executesql @sql ,N ' @objectname sysname ' , @objectname
GO
调用方法:
Use
Test
Go
Exec sp_TableSpace null , ' test1 '
Exec sp_TableSpace null
Go
Exec sp_TableSpace null , ' test1 '
Exec sp_TableSpace null