重写系统存储过程：sp_spaceused

重写系统存储过程：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

 

 

 

调用方法：

 

Use Test
Go

Exec sp_TableSpace null,'test1'
Exec sp_TableSpace null