SQL SERVER隱藏系統函式sp_columns_100_rowset和sp_table_statistics2_rowset

edwardking888發表於2011-08-26
原文網址 : http://blog.itpub.net/8183550/viewspace-705837/
SQLServer函式
今天突然發現2個隱藏系統函式sp_columns_100_rowset和sp_table_statistics2_rowset

sp_columns_100_rowset定義如下：
  
create procedure sys.sp_columns_100_rowset  
(  
    @table_name     sysname = NULL,  
    @table_schema   sysname = NULL,  
    @column_name    sysname = NULL  
)  
as  
    select  
        TABLE_CATALOG           = s_cv.TABLE_CATALOG,  
        TABLE_SCHEMA            = s_cv.TABLE_SCHEMA,  
        TABLE_NAME              = s_cv.TABLE_NAME,  
        COLUMN_NAME             = s_cv.COLUMN_NAME,  
        COLUMN_GUID             = s_cv.COLUMN_GUID,  
        COLUMN_PROPID           = s_cv.COLUMN_PROPID,  
        ORDINAL_POSITION        = s_cv.ORDINAL_POSITION,  
        COLUMN_HASDEFAULT       = s_cv.COLUMN_HASDEFAULT,  
        COLUMN_DEFAULT          = s_cv.COLUMN_DEFAULT,  
        COLUMN_FLAGS            = s_cv.COLUMN_FLAGS,  
        IS_NULLABLE             = s_cv.IS_NULLABLE,  
        DATA_TYPE               = s_cv.DATA_TYPE, -- Used by Katmai+ clients  
        TYPE_GUID               = s_cv.TYPE_GUID,  
        CHARACTER_MAXIMUM_LENGTH= s_cv.CHARACTER_MAXIMUM_LENGTH,  
        CHARACTER_OCTET_LENGTH  = s_cv.CHARACTER_OCTET_LENGTH,  
        NUMERIC_PRECISION       = s_cv.NUMERIC_PRECISION,  
        NUMERIC_SCALE           = s_cv.NUMERIC_SCALE,  
        DATETIME_PRECISION      = s_cv.DATETIME_PRECISION,  
        CHARACTER_SET_CATALOG   = s_cv.CHARACTER_SET_CATALOG,  
        CHARACTER_SET_SCHEMA    = s_cv.CHARACTER_SET_SCHEMA,  
        CHARACTER_SET_NAME      = s_cv.CHARACTER_SET_NAME,  
        COLLATION_CATALOG       = s_cv.COLLATION_CATALOG,  
        COLLATION_SCHEMA        = s_cv.COLLATION_SCHEMA,  
        COLLATION_NAME          = s_cv.COLLATION_NAME,  
        DOMAIN_CATALOG          = s_cv.DOMAIN_CATALOG,  
        DOMAIN_SCHEMA           = s_cv.DOMAIN_SCHEMA,  
        DOMAIN_NAME             = s_cv.DOMAIN_NAME,  
        DESCRIPTION             = s_cv.DESCRIPTION,  
        COLUMN_LCID             = s_cv.COLUMN_LCID,  
        COLUMN_COMPFLAGS        = s_cv.COLUMN_COMPFLAGS,  
        COLUMN_SORTID           = s_cv.COLUMN_SORTID,  
        COLUMN_TDSCOLLATION     = s_cv.COLUMN_TDSCOLLATION,  
        IS_COMPUTED             = s_cv.IS_COMPUTED,  
        SS_XML_SCHEMACOLLECTION_CATALOGNAME = s_cv.SS_XML_SCHEMACOLLECTION_CATALOGNAME,  
        SS_XML_SCHEMACOLLECTION_SCHEMANAME  = s_cv.SS_XML_SCHEMACOLLECTION_SCHEMANAME,  
        SS_XML_SCHEMACOLLECTIONNAME         = s_cv.SS_XML_SCHEMACOLLECTIONNAME,  
        SS_UDT_CATALOGNAME      = s_cv.SS_UDT_CATALOGNAME,  
        SS_UDT_SCHEMANAME       = s_cv.SS_UDT_SCHEMANAME,  
        SS_UDT_NAME             = s_cv.SS_UDT_NAME,  
        SS_UDT_ASSEMBLY_TYPENAME= s_cv.SS_UDT_ASSEMBLY_TYPENAME,  
        SS_IS_SPARSE            = s_cv.SS_IS_SPARSE,  
        SS_IS_COLUMN_SET        = s_cv.SS_IS_COLUMN_SET  
  
    from  
        sys.spt_columns_view s_cv  
  
    where  
        (  
            (@table_schema is null and s_cv.TABLE_NAME = @table_name) or  
             s_cv.object_id = object_id(quotename(@table_schema) + '.' + quotename(@table_name))  
        ) and  
        (@column_name = s_cv.COLUMN_NAME or @column_name is null)  
  
    order by 1, 2, 3, 7  

是一個顯示錶列相關資訊的函式，列出了比較詳細的資訊。

sp_table_statistics2_rowset 主要用於顯示目標表的統計資訊，該函式定義如下：
  
create procedure sys.sp_table_statistics2_rowset  
(  
    @table_name         sysname,  
    @table_schema       sysname = null,  
    @table_catalog      sysname = null,  
    @stat_name          sysname = null,  
    @stat_schema        sysname = null,  
    @stat_catalog       sysname = null  
)  
as  
begin  
    set nocount on  
  
    -- check in parameters  
    if ((@table_catalog is not null) and (db_name() <> @table_catalog)  
        or (@stat_catalog is not null) and (db_name() <> @stat_catalog))  
        begin   -- If qualifier doesn't match current database  
            raiserror 20001 '~~Rush_5~~'  
            return  
        end  
  
    declare @objid  int, @schid  int  
  
    select @schid = schema_id(@table_schema)  
    select @objid = object_id(  
                        isnull(quotename(@table_catalog ), '') + '.' +  
                        isnull(quotename(@table_schema), '')   + '.' +  
                        quotename(@table_name))  
  
    --- create temp table  
    create table #spstattab  
    (  
        tblcatalog  sysname collate database_default not null,  
        tblschema   sysname collate database_default not null,  
        tblname     sysname collate database_default not null,  
        statname    sysname collate database_default not null  
    )  
  
    -- I don't like this copy&paste stuff, but here it boosts performance by 50%.  
    if @table_name is not null  
    begin  
        insert into #spstattab  
        select  
            tblcatalog  = db_name(),  
            tblschema   = schema_name(o.schema_id),  
            tblname     = o.name,  
            statname    = st.name  
        from  
            sys.all_objects o inner join  
            sys.stats st on  
                (  
                    @objid = o.object_id and  
                    st.object_id = o.object_id and  
                    o.type in ('U') and  
                    (@stat_name is null or @stat_name = st.name) and  
                    (@stat_schema is null or @stat_schema = schema_name(o.schema_id))  
                )  
    end  
    else  
    begin  
        insert into #spstattab  
        select  
            tblcatalog  = db_name(),  
            tblschema   = schema_name(o.schema_id),  
            tblname     = o.name,  
            statname    = st.name  
        from  
            sys.all_objects o inner join  
            sys.stats st on  
                (  
                    (@table_schema is null or @schid = o.schema_id) and  
                    st.object_id = o.object_id and  
                    o.type in ('U') and  
                    (@stat_name is null or @stat_name = st.name) and  
                    (@stat_schema is null or @stat_schema = schema_name(o.schema_id))  
                )  
    end  
  
    -- Fast forward CURSOR OVER THE temp table, apply the order clause here but not  
    -- in the code above used for filling temp table with data.  
    declare ff_csr cursor fast_forward for  
        select tblcatalog, tblschema, tblname, statname from #spstattab order by 1,2,3,4  
  
    declare  
        @tblcatalog sysname,  
        @tblschema  sysname,  
        @tblname    sysname,  
        @statname   sysname,  
        @qtbl       nvarchar(4000),  
        @rowsetcount    int  
  
    set @rowsetcount = 0  
  
    open ff_csr  
    fetch ff_csr into @tblcatalog, @tblschema, @tblname, @statname  
    while @@fetch_status >= 0  
    begin  
        set @rowsetcount = @rowsetcount + 1  
        set @qtbl = quotename(@tblcatalog) + '.' + quotename(@tblschema) + '.' + quotename(@tblname)  
  
        --  
        -- This is the real stuff.  
        --  
        dbcc show_statistics(@qtbl, @statname) with stat_header join density_vector  
  
        fetch ff_csr into @tblcatalog, @tblschema, @tblname, @statname  
    end  
    close ff_csr  
    deallocate ff_csr  
  
    --- drop temp table  
    drop table #spstattab  
    if @rowsetcount = 0  
    begin  
        select  
            TABLE_CATALOG = convert(sysname, null),  
            TABLE_SCHEMA = convert(sysname, null),  
            TABLE_NAME = convert(sysname, null),  
            STATISTICS_CATALOG = convert(sysname, null),  
            STATISTICS_SCHEMA = convert(sysname, null),  
            STATISTICS_NAME = convert(sysname, null),  
            STATISTICS_TYPE = convert(smallint,0),  
            COLUMN_NAME = convert(sysname, null),  
            COLUMN_GUID = convert(uniqueidentifier, null),  
            COLUMN_PROPID = convert(int, null),  
            ORDINAL_POSITION = convert(int, null),  
            SAMPLE_PCT = convert(smallint, null),  
            LAST_UPDATE_TIME = convert(datetime, null),  
            NO_OF_RANGES = convert(int, null),  
            COLUMN_CARDINALITY = convert(bigint, null),  
            TUPLE_CARDINALITY = convert(bigint, null),  
            TABLE_CARDINALITY = convert(bigint, null),  
            AVG_COLUMN_LENGTH = convert(int, null)  
        where 1=0  
    end  
  
    set nocount off  
end  



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8183550/viewspace-705837/，如需轉載，請註明出處，否則將追究法律責任。

相關文章