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_statistics(nvarchar @table_name, nvarchar @table_owner
, nvarchar @table_qualifier
, nvarchar @index_name
, char @is_unique
, char @accuracy)
MetaData:
create procedure sys.sp_statistics ( @table_name sysname, -- Wildcard pattern matching IS NOT supported. @table_owner sysname = null, -- Wildcard pattern matching IS NOT supported. @table_qualifier sysname = null, @index_name sysname = '%', -- Wildcard pattern matching is supported. @is_unique char(1) = 'N', @accuracy char(1) = 'Q' ) as set nocount on declare @table_id int -- quotename() returns up to 258 chars declare @full_table_name nvarchar(517) -- 258 + 1 + 258 if @table_qualifier is not null begin if db_name() <> @table_qualifier begin -- If qualifier doesn't match current database raiserror (15250, -1,-1) return end end if @accuracy not in ('Q','E') begin raiserror (15251,-1,-1,'accuracy','''Q'' or ''E''') return end if @table_owner is null begin -- If unqualified table name select @full_table_name = quotename(@table_name) end else begin -- Qualified table name if @table_owner = '' begin -- If empty owner name select @full_table_name = quotename(@table_owner) end else begin select @full_table_name = quotename(@table_owner) + '.' + quotename(@table_name) end end -- Get Object ID select @table_id = object_id(@full_table_name) -- Rows for indexes. select TABLE_QUALIFIER = db_name(), TABLE_OWNER = schema_name(o.schema_id), TABLE_NAME = o.name, NON_UNIQUE = convert(smallint, case when x.is_unique = 0 then 1 -- Nonunique index else 0 -- Unique index end), INDEX_QUALIFIER = o.name, INDEX_NAME = x.name, TYPE = isnull(convert(smallint, -- workaround engine limitation, see SQL BU 373271 case when x.index_id > 1 then 3 -- Non-Clustered else 1 -- Clustered index end), sysconv(smallint,1)), SEQ_IN_INDEX = convert(smallint, s_ic.key_ordinal), COLUMN_NAME = index_col(@full_table_name, x.index_id, s_ic.key_ordinal), -- For indexes return whether ASC or DESC and for statistics return null [COLLATION] = convert(char(1), case when s_ic.is_descending_key = 1 then 'D' -- DB_COLLATION_DESC else 'A' -- DB_COLLATION_ASC end), CARDINALITY = convert(int, case when x.index_id > 1 then null -- Non-Clustered else 0x7FFFFFFF & p.rows -- Clustered index end), PAGES = convert(int, case when x.index_id > 1 then null -- Non-Clustered else p.data_pages -- Clustered index end), FILTER_CONDITION = convert(varchar(128),null) from sys.objects o inner join -- Yukon doesn't expose indexes on its system objects, so only user objects are useful. sys.indexes x on ( x.object_id = o.object_id and (x.is_unique = 1 or @is_unique <> 'Y') and x.is_hypothetical = 0 ) inner join sys.index_columns s_ic on ( s_ic.object_id = o.object_id and s_ic.index_id = x.index_id and s_ic.key_ordinal > 0 ) left join sys.index_counts p on ( p.object_id = x.object_id and p.index_id = x.index_id ) where o.object_id = @table_id and (@index_name = '%' or x.name like @index_name) -- matching name union all -- Add row for table statistics. select TABLE_QUALIFIER = db_name(), TABLE_OWNER = schema_name(o.schema_id), TABLE_NAME = o.name, NON_UNIQUE = convert(smallint, null), INDEX_QUALIFIER = convert(sysname, null), INDEX_NAME = convert(sysname, null), TYPE = isnull(convert(smallint, 0), sysconv(smallint,0)), -- Statistics for a table. SEQ_IN_INDEX = convert(smallint, null), COLUMN_NAME = convert(sysname, null), [COLLATION] = convert(char(1), null), CARDINALITY = convert (int, 0x7FFFFFFF & p.rows), PAGES = p.data_pages, FILTER_CONDITION = convert(varchar(128),null) from sys.objects o inner join -- Yukon doesn't expose indexes on its system objects, so only user objects are useful. sys.indexes x on ( x.object_id = o.object_id and (x.index_id = 0 or x.index_id = 1) -- If there are no indexes then table stats are in a row with index_id =0 ) left join sys.index_counts p on ( p.object_id = x.object_id and p.index_id = x.index_id ) where o.object_id = @table_id order by 4, 7, 6, 8
No comments:
Post a Comment