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_MShelpindex(nvarchar @tablename, nvarchar @indexname
, int @flags)
MetaData:
create procedure sys.sp_MShelpindex @tablename nvarchar(517), @indexname nvarchar(258) = null, @flags int = null as -- -- @flags added for DaVinci uses. If the bit isn't set, use 6.5 -- -- -- -- sp_MShelpindex '%s', null, 1 -- -- create table #tempID ( cName nvarchar(132) COLLATE database_default NOT NULL, -- Index name -- cInx1 int NULL, cInx2 int NULL, cInx3 int NULL, cInx4 int NULL, cInx5 int NULL, cInx6 int NULL, cInx7 int NULL, cInx8 int NULL, cInx9 int NULL, cInx10 int NULL, cInx11 int NULL, cInx12 int NULL, cInx13 int NULL, cInx14 int NULL, cInx15 int NULL, cInx16 int NULL, -- 1 if DESC -- cC1 int NULL, cC2 int NULL, cC3 int NULL, cC4 int NULL, cC5 int NULL, cC6 int NULL, cC7 int NULL, cC8 int NULL, cC9 int NULL, cC10 int NULL, cC11 int NULL, cC12 int NULL, cC13 int NULL, cC14 int NULL, cC15 int NULL, cC16 int NULL -- 1 if Computed column -- ) create table #tempID2 ( cName nvarchar(132) COLLATE database_default NOT NULL, -- Index name -- cInx int NULL, -- Combined info -- cComputed int NULL -- 1 if on computed column(s) -- ) -- @flags is for daVinci -- if (@flags is null) select @flags = 0 set nocount on insert #tempID select i.name, indexkey_property(object_id(@tablename), i.indid, 1, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 2, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 3, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 4, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 5, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 6, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 7, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 8, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 9, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 10, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 11, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 12, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 13, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 14, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 15, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 16, N'isdescending'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 1), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 2), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 3), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 4), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 5), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 6), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 7), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 8), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 9), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 10), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 11), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 12), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 13), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 14), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 15), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 16), N'IsComputed') from dbo.sysindexes i where id = object_id(@tablename) and i.indid > 0 and i.indid < 255 and (@indexname is null or i.name = @indexname) and (indexkey_property(object_id(@tablename), i.indid, 1, N'isdescending') is not null) and (i.name is not null) order by i.indid -- Construct the bit -- declare @idx int, @isComputed int declare @Name nvarchar(132) declare @Inx_1 int, @Inx_2 int, @Inx_3 int, @Inx_4 int, @Inx_5 int, @Inx_6 int, @Inx_7 int, @Inx_8 int declare @Inx_9 int, @Inx_10 int, @Inx_11 int, @Inx_12 int, @Inx_13 int, @Inx_14 int, @Inx_15 int, @Inx_16 int declare @C_1 int, @C_2 int, @C_3 int, @C_4 int, @C_5 int, @C_6 int, @C_7 int, @C_8 int declare @C_9 int, @C_10 int, @C_11 int, @C_12 int, @C_13 int, @C_14 int, @C_15 int, @C_16 int declare hC cursor global for select * from #tempID open hC fetch next from hC into @Name, @Inx_1, @Inx_2, @Inx_3, @Inx_4, @Inx_5, @Inx_6, @Inx_7, @Inx_8, @Inx_9, @Inx_10, @Inx_11, @Inx_12, @Inx_13, @Inx_14, @Inx_15, @Inx_16, @C_1, @C_2, @C_3, @C_4, @C_5, @C_6, @C_7, @C_8, @C_9, @C_10, @C_11, @C_12, @C_13, @C_14, @C_15, @C_16 while (@@FETCH_STATUS = 0) begin -- descending? -- select @idx = 0x0000 select @idx = (case when (@Inx_1 = 1) then @idx | 0x0001 else @idx end), @idx = (case when (@Inx_2 = 1) then @idx | 0x0002 else @idx end), @idx = (case when (@Inx_3 = 1) then @idx | 0x0004 else @idx end), @idx = (case when (@Inx_4 = 1) then @idx | 0x0008 else @idx end), @idx = (case when (@Inx_5 = 1) then @idx | 0x0010 else @idx end), @idx = (case when (@Inx_6 = 1) then @idx | 0x0020 else @idx end), @idx = (case when (@Inx_7 = 1) then @idx | 0x0040 else @idx end), @idx = (case when (@Inx_8 = 1) then @idx | 0x0080 else @idx end), @idx = (case when (@Inx_9 = 1) then @idx | 0x0100 else @idx end), @idx = (case when (@Inx_10 = 1) then @idx | 0x0200 else @idx end), @idx = (case when (@Inx_11 = 1) then @idx | 0x0400 else @idx end), @idx = (case when (@Inx_12 = 1) then @idx | 0x0800 else @idx end), @idx = (case when (@Inx_13 = 1) then @idx | 0x1000 else @idx end), @idx = (case when (@Inx_14 = 1) then @idx | 0x2000 else @idx end), @idx = (case when (@Inx_15 = 1) then @idx | 0x4000 else @idx end), @idx = (case when (@Inx_16 = 1) then @idx | 0x8000 else @idx end) select @isComputed = 0 select @isComputed = (case when (@C_1 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_2 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_3 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_4 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_5 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_6 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_7 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_8 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_9 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_10 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_11 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_12 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_13 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_14 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_15 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_16 = 1) then @isComputed | 1 else @isComputed end) insert #tempID2 select @Name, @idx, @isComputed fetch next from hC into @Name, @Inx_1, @Inx_2, @Inx_3, @Inx_4, @Inx_5, @Inx_6, @Inx_7, @Inx_8, @Inx_9, @Inx_10, @Inx_11, @Inx_12, @Inx_13, @Inx_14, @Inx_15, @Inx_16, @C_1, @C_2, @C_3, @C_4, @C_5, @C_6, @C_7, @C_8, @C_9, @C_10, @C_11, @C_12, @C_13, @C_14, @C_15, @C_16 end close hC deallocate hC set nocount off if (@flags <> 0) begin -- daVinci is calling -- select i.name, i.status, i.indid, i.OrigFillFactor, IndCol1 = index_col(@tablename, i.indid, 1), IndCol2 = index_col(@tablename, i.indid, 2), IndCol3 = index_col(@tablename, i.indid, 3), IndCol4 = index_col(@tablename, i.indid, 4), IndCol5 = index_col(@tablename, i.indid, 5), IndCol6 = index_col(@tablename, i.indid, 6), IndCol7 = index_col(@tablename, i.indid, 7), IndCol8 = index_col(@tablename, i.indid, 8), IndCol9 = index_col(@tablename, i.indid, 9), IndCol10 = index_col(@tablename, i.indid, 10), IndCol11 = index_col(@tablename, i.indid, 11), IndCol12 = index_col(@tablename, i.indid, 12), IndCol13 = index_col(@tablename, i.indid, 13), IndCol14 = index_col(@tablename, i.indid, 14), IndCol15 = index_col(@tablename, i.indid, 15), IndCol16 = index_col(@tablename, i.indid, 16) , SegName = s.groupname , FullTextKey = IndexProperty(object_id(@tablename), i.name, N'IsFulltextKey') , Descending = t.cInx , Computed = t.cComputed , IsTable = OBJECTPROPERTY(object_id(@tablename), N'IsTable') from (dbo.sysindexes i inner join dbo.sysfilegroups s on i.groupid = s.groupid ), #tempID2 t where id = object_id(@tablename) and i.indid > 0 and i.indid < 255 and (@indexname is null or i.name = @indexname) and (INDEXPROPERTY(object_id(@tablename), i.name, N'IsStatistics') <> 1) and (INDEXPROPERTY(object_id(@tablename), i.name, N'IsAutoStatistics') <> 1) and (INDEXPROPERTY(object_id(@tablename), i.name, N'IsHypothetical') <> 1) and i.name = t.cName order by i.indid end else begin -- select (case when (i.status & 0x0040) != 0 then substring(i.name, 9, (datalength(i.name)/2)-17) else i.name end), i.status, i.indid, i.OrigFillFactor, -- select i.name, i.status, i.indid, i.OrigFillFactor, IndCol1 = index_col(@tablename, i.indid, 1), IndCol2 = index_col(@tablename, i.indid, 2), IndCol3 = index_col(@tablename, i.indid, 3), IndCol4 = index_col(@tablename, i.indid, 4), IndCol5 = index_col(@tablename, i.indid, 5), IndCol6 = index_col(@tablename, i.indid, 6), IndCol7 = index_col(@tablename, i.indid, 7), IndCol8 = index_col(@tablename, i.indid, 8), IndCol9 = index_col(@tablename, i.indid, 9), IndCol10 = index_col(@tablename, i.indid, 10), IndCol11 = index_col(@tablename, i.indid, 11), IndCol12 = index_col(@tablename, i.indid, 12), IndCol13 = index_col(@tablename, i.indid, 13), IndCol14 = index_col(@tablename, i.indid, 14), IndCol15 = index_col(@tablename, i.indid, 15), IndCol16 = index_col(@tablename, i.indid, 16) , SegName = s.groupname , FullTextKey = IndexProperty(object_id(@tablename), i.name, N'IsFulltextKey') , Descending = t.cInx , Computed = t.cComputed , IsTable = OBJECTPROPERTY(object_id(@tablename), N'IsTable') from (dbo.sysindexes i inner join dbo.sysfilegroups s on i.groupid = s.groupid ), #tempID2 t where id = object_id(@tablename) and i.indid > 0 and i.indid < 255 and (@indexname is null or i.name = @indexname) and i.name = t.cName order by i.indid -- order by i.name -- end
No comments:
Post a Comment