top

EXEC sys.[sp_spaceused] 的行数经过两次封装

EXEC sys.[sp_spaceused] 的行数经过两次封装

EXEC sys.[sp_spaceused] @objname = N'', -- nvarchar(776)
    @updateusage = '' -- varchar(5)
复制代码
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO
create procedure sys.sp_spaceused --- 2003/05/19 14:00
@objname nvarchar(776) = null,        -- The object we want size on.
@updateusage varchar(5) = false        -- Param. for specifying that
                    -- usage info. should be updated.
as

declare @id    int            -- The object id that takes up space
        ,@type    character(2) -- The object type.
        ,@pages    bigint            -- Working variable for size calc.
        ,@dbname sysname
        ,@dbsize bigint
        ,@logsize bigint
        ,@reservedpages  bigint
        ,@usedpages  bigint
        ,@rowCount bigint

/*
**  Check to see if user wants usages updated.
*/

if @updateusage is not null
    begin
        select @updateusage=lower(@updateusage)

        if @updateusage not in ('true','false')
            begin
                raiserror(15143,-1,-1,@updateusage)
                return(1)
            end
    end
/*
**  Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin

    select @dbname = parsename(@objname, 3)

    if @dbname is not null and @dbname <> db_name()
        begin
            raiserror(15250,-1,-1)
            return (1)
        end

    if @dbname is null
        select @dbname = db_name()

    /*
    **  Try to find the object.
    */
    SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)

    -- Translate @id to internal-table for queue
    IF @type = 'SQ'
        SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue

    /*
    **  Does the object exist?
    */
    if @id is null
        begin
            raiserror(15009,-1,-1,@objname,@dbname)
            return (1)
        end

    -- Is it a table, view or queue?
    IF @type NOT IN ('U ','S ','V ','SQ','IT')
    begin
        raiserror(15234,-1,-1)
        return (1)
    end
end

/*
**  Update usages if user specified to do so.
*/

if @updateusage = 'true'
    begin
        if @objname is null
            dbcc updateusage(0) with no_infomsgs
        else
            dbcc updateusage(0,@objname) with no_infomsgs
        print ' '
    end

set nocount on

/*
**  If @id is null, then we want summary data.
*/
if @id is null
begin
    select @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 dbo.sysfiles

    select @reservedpages = sum(a.total_pages),
        @usedpages = sum(a.used_pages),
        @pages = sum(
                CASE
                    -- XML-Index and FT-Index and semantic index internal tables are not considered "data", but is part of "index_size"
                    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
                    When a.type <> 1 and p.index_id < 2 Then a.used_pages
                    When p.index_id < 2 Then a.data_pages
                    Else 0
                END
            )
    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
        left join sys.internal_tables it on p.object_id = it.object_id

    /* unallocated space could not be negative */
    select 
        database_name = db_name(),
        database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
            * 8192 / 1048576,15,2) + ' MB'),
        'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then
            (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
            * 8192 / 1048576 else 0 end),15,2) + ' MB')

    /*
    **  Now calculate the summary data.
    **  reserved: sum(reserved) where indid in (0, 1, 255)
    ** data: sum(data_pages) + sum(text_used)
    ** index: sum(used) where indid in (0, 1, 255) - data
    ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
    */
    select
        reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
        data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),
        index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
        unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
end

/*
**  We want a particular object.
*/
else
begin
    /*
    ** Now calculate the summary data. 
    *  Note that LOB Data and Row-overflow Data are counted as Data Pages for the base table
    *  For non-clustered indices they are counted towards the index pages
    */
    SELECT 
        @reservedpages = SUM (reserved_page_count),
        @usedpages = SUM (used_page_count),
        @pages = SUM (
            CASE
                WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                ELSE 0
            END
            ),
        @rowCount = SUM (
            CASE
                WHEN (index_id < 2) THEN row_count
                ELSE 0
            END
            )
    FROM sys.dm_db_partition_stats
    WHERE object_id = @id;

    /*
    ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
    */
    IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)) > 0 
    BEGIN
        /*
        **  Now calculate the summary data. Row counts in these internal tables don't 
        **  contribute towards row count of original table.
        */
        SELECT 
            @reservedpages = @reservedpages + sum(reserved_page_count),
            @usedpages = @usedpages + sum(used_page_count)
        FROM sys.dm_db_partition_stats p, sys.internal_tables it
        WHERE it.parent_id = @id AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) AND p.object_id = it.object_id;
    END

    SELECT 
        name = OBJECT_NAME (@id),
        rows = convert (char(20), @rowCount),
        reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
        data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
        index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
        unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')

end


return (0) -- sp_spaceused
GO
复制代码

/*
** Now calculate the summary data.
* Note that LOB Data and Row-overflow Data are counted as Data Pages for the base table
* For non-clustered indices they are counted towards the index pages
*/

再封装

SELECT * FROM sys.dm_db_partition_stats
复制代码
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO
CREATE VIEW sys.dm_db_partition_stats AS
    SELECT c.partition_id, i.object_id, i.index_id, c.partition_number,
    c.in_row_data_page_count, c.in_row_used_page_count, c.in_row_reserved_page_count,
    c.lob_used_page_count, c.lob_reserved_page_count,
    c.row_overflow_used_page_count, c.row_overflow_reserved_page_count,
    c.used_page_count, c.reserved_page_count, c.row_count
    FROM sys.indexes$ i CROSS APPLY OpenRowSet(TABLE PARTITIONCOUNTS, i.object_id, i.index_id, i.rowset) c
GO
复制代码

c.row_count
sys.indexes$

 

oracle的数据字典视图

例如 v$instance可以查询有关实例的信息

 

用DAC连接sqlserver,查询sys.indexes$表的内容

SELECT * FROM sys.indexes$ 

 

不用DAC连接进去的话会报对象名无效

消息 208,级别 16,状态 1,第 1 行
对象名 'sys.indexes$' 无效。

 

posted @   桦仔  阅读(488)  评论(0编辑  收藏  举报
编辑推荐:
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示