Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MShelpindex

  No additional text.


Syntax
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) t
hen @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 @i
dx | 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), @isComput
ed = (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


 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash