The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_table_statistics2_rowset(nvarchar @table_name, nvarchar @table_schema
, nvarchar @table_catalog
, nvarchar @stat_name
, nvarchar @stat_schema
, nvarchar @stat_catalog)
MetaData:
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, 16, 1); -- '~~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 catalog_default not null, tblschema sysname collate catalog_default not null, tblname sysname collate catalog_default not null, statname sysname collate catalog_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 local 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
No comments:
Post a Comment