use sybsystemprocs
go

sp_dropprocedure sp_checkindexes_format
go
sp_dropprocedure sp_checkindexes_load
go
sp_dropprocedure sp_checkindexes_print
go
sp_dropprocedure sp_checkindexes
go
sp_dropprocedure sp_indexes_format
go
sp_dropprocedure sp_indexes_printall
go
sp_dropprocedure sp_indexes_printtbl
go
sp_dropprocedure sp_indexes
go
sp_dropprocedure sp_indexes2
go
sp_dropprocedure sp_selectivity
go

set quoted_identifier on
go

------------ Temporary table needed for creation of sp_checkindexes subroutines

create table #chkindexes
   (name       varchar ( 30)  null,
    printname  varchar ( 30)  null,
    uid        int            not null,
    id         int            not null,
    indid      tinyint        not null,
    osysstat   smallint       not null,
    osysstat2  int            not null,
    istatus    smallint       not null,
    istatus2   int            not null,
    keyflag    tinyint        not null,
    keycnt     tinyint        not null,
    txt        varchar ( 75)  null,
    keys       varchar (255)  null,
    cacheflag  tinyint        not null,
    dpages     char (11)      not null,
    ipages     int            not null,
    ratio      int            not null,
    maxlen     smallint       not null,
    rowcnt     int            not null,
    indxcnt    tinyint        not null,
    sort       varchar ( 63)  null)
go

create procedure sp_checkindexes_format as
   set ansinull                      on
   set string_rtruncation            on

   declare @maxkeys   tinyint,
           @sequence  int

   select @sequence = 1

   while (@sequence < 8)
      begin
         update #chkindexes
         set txt = substring (ltrim (#chkindexes.txt  +  ' '  +  i.txt), 1, 75)
         from sybsystemprocs.dbo.uitype i
         where i.txt_seq = @sequence
           and i.type2   = 0
           and (((i.type <> (#chkindexes.istatus & i.type)  and  (i.dflt = 1)))  or
                 (i.type  = (#chkindexes.istatus & i.type)))

         select @sequence = @sequence + 1
      end

   update #chkindexes
   set txt = 'Primary ' + substring (txt, 16, 60)
   where txt like 'Primary Unique%'

   update #chkindexes
   set txt = stuff (txt, charindex ('Nonclustered', txt), 12, '(Clustered)')
   where (istatus2 & 512) = 512

   select @maxkeys  = 1 + max (keycnt),
          @sequence = 1
   from #chkindexes

   while (@sequence < @maxkeys)
      begin
         update #chkindexes
         set keys = substring (i.keys
                               +  ' '
                               +  index_col (i.name, i.indid, @sequence, i.uid)
                               +  ',',
                               1,
                               255)
         from syscolumns sc, sysindexes si, #chkindexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and si.keycnt       >= @sequence
           and sc.id            = i.id
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and sc.offset        > 0
           and (sc.status & 8) <> 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'ASC'

         update #chkindexes
         set keys = substring (i.keys
                               +  ' '
                               +  index_col (i.name, i.indid, @sequence, i.uid)
                               +  '*,',
                               1,
                               255)
         from syscolumns sc, sysindexes si, #chkindexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and si.keycnt       >= @sequence
           and sc.id            = i.id
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and sc.offset        > 0
           and (sc.status & 8) <> 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'DESC'

         update #chkindexes
         set keyflag = 1,
             keys    = substring (i.keys
                                  +  ' ['
                                  +  index_col (i.name, i.indid, @sequence, i.uid)
                                  +  '],',
                                  1,
                                  255)
         from syscolumns sc, sysindexes si, #chkindexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and si.keycnt       >= @sequence
           and sc.id            = i.id
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and sc.offset        < 0
           and (sc.status & 8) <> 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'ASC'

         update #chkindexes
         set keyflag = 1,
             keys    = substring (i.keys
                                  +  ' ['
                                  +  index_col (i.name, i.indid, @sequence, i.uid)
                                  +  '*],',
                                  1,
                                  255)
         from syscolumns sc, sysindexes si, #chkindexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and si.keycnt       >= @sequence
           and sc.id            = i.id
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and sc.offset        < 0
           and (sc.status & 8) <> 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'DESC'

         update #chkindexes
         set keyflag = 1,
             keys    = substring (i.keys
                                  +  ' ('
                                  +  index_col (i.name, i.indid, @sequence, i.uid)
                                  +  '),',
                                  1,
                                  255)
         from syscolumns sc, sysindexes si, #chkindexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and si.keycnt       >= @sequence
           and sc.id            = i.id
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and (sc.status & 8)  = 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'ASC'

         update #chkindexes
         set keyflag = 1,
             keys    = substring (i.keys
                                  +  ' ('
                                  +  index_col (i.name, i.indid, @sequence, i.uid)
                                  +  '*),',
                                  1,
                                  255)
         from syscolumns sc, sysindexes si, #chkindexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and si.keycnt       >= @sequence
           and sc.id            = i.id
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and (sc.status & 8)  = 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'DESC'

         select @sequence = @sequence + 1
      end

   update #chkindexes
   set printname = substring (i.name  +  ' ('  +  user_name (i.uid)  +  ')', 1, 30)
   from #chkindexes i
   where i.uid <> 1
      or 1      < (select count (distinct o.uid)
                   from sysobjects o
                   where o.name           = i.name
                     and (o.sysstat & 15) = 3)

   update #chkindexes
   set keys = substring (keys, 2, char_length (keys) - 2)

   update #chkindexes
   set name = printname

   update #chkindexes
   set sort = name  +  ' '  +  user_name (uid)

   update #chkindexes
   set printname = substring (i.printname + ' [' + a.char_value + ']', 1, 30),
       cacheflag = 1
   from #chkindexes i,
        sysattributes a
   where a.class        = 3             -- buffer manager
     and a.attribute    = 0             -- cache bindings
     and a.object_type  = 'I'           -- index binding
     and i.id           = a.object
     and i.indid        = a.object_info1

   update #chkindexes
   set printname = ' '
   from #chkindexes i1
   where cacheflag = 0
     and exists (select *
                 from #chkindexes i2
                 where i2.id    = i1.id
                   and i2.indid = i1.indid
                   and i2.indid > (select min (i3.indid)
                                   from #chkindexes i3
                                   where i3.id    = i2.id
                                     and i3.indid > 0))

   update #chkindexes
   set printname = stuff (printname,
                          1,
                          charindex ('[', printname) - 1,
                          space (charindex ('[', printname) - 1))
   from #chkindexes i1
   where cacheflag                  = 1
     and charindex ('[', printname) < 29
     and exists (select *
                 from #chkindexes i2
                 where i2.id    = i1.id
                   and i2.indid = i1.indid
                   and i2.indid > (select min (i3.indid)
                                   from #chkindexes i3
                                   where i3.id    = i2.id
                                     and i3.indid > 0))
go
sp_existsprocedure sp_checkindexes_format
go

create procedure sp_checkindexes_load as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   if (exists (select i.*
               from sysindexes i,
                    sysobjects o
               where (o.sysstat & 15) = 3
                 and i.indid          > 0
                 and i.indid          < 254
                 and i.id             = o.id))
      begin
         insert into #chkindexes
            select o.name, o.name, o.uid, i.id, i.indid, o.sysstat,
                   o.sysstat2, i.status, i.status2, 0, i.keycnt, NULL,
                   NULL, 0,
                   substring (convert (char (14),
                                       convert (money,
                                                data_pgs (i.id, i.doampg)),
                                       1),
                              1,
                              11),
                   data_pgs (i.id, i.ioampg),
                   (100 * data_pgs (i.id, i.ioampg)) / data_pgs (i.id, i.doampg),
                   0, 0, 0, NULL
            from sysindexes i,
                 sysobjects o
            where (o.sysstat & 15) = 3
              and i.id             = o.id
              and i.indid          = 1

         insert into #chkindexes
            select o.name, o.name, o.uid, i.id, i.indid, o.sysstat,
                   o.sysstat2, i.status, i.status2, 0, (i.keycnt - 1),
                   NULL, NULL, 0,
                   substring (convert (char (14),
                                       convert (money,
                                                data_pgs (d.id, d.doampg)),
                                       1),
                              1,
                              11),
                   data_pgs (i.id, i.ioampg),
                   (100 * data_pgs (i.id, i.ioampg)) / data_pgs (d.id, d.doampg),
                   0, 0, 0, NULL
            from sysindexes i,
                 sysindexes d,
                 sysobjects o
            where (o.sysstat & 15) = 3
              and i.id             = o.id
              and i.indid          > 1
              and i.indid          < 254
              and i.id             = d.id
              and d.indid          < 2
      end
   else
      begin
         print '    THERE ARE NO INDEXES ON USER TABLES.'

         print ' '
      end
go
sp_existsprocedure sp_checkindexes_load
go

create procedure sp_checkindexes_print as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @adjust_flag  tinyint,
           @cmdstring    varchar (255),
           @keyflag      tinyint,
           @mintablen    tinyint,
           @mintypelen   tinyint,
           @mintxtlen    tinyint,
           @typelen      tinyint,
           @tablen       tinyint,
           @txtlen       smallint

   select @tablen  = max (char_length (printname)),
          @typelen = max (char_length (txt)),
          @txtlen  = max (char_length (keys))
   from #chkindexes

   select @keyflag = 1
   from #chkindexes
   where keyflag = 1

   -- NOTE THAT IN ADDITION TO THE THREE COLUMN WIDTHS THAT THE
   -- TITLE WIDTH FOR THE LAST COLUMN IS A FORMAT FACTOR.

   if (@keyflag = 1)
      begin
         select @mintablen  = 23,
                @mintypelen = 25,
                @mintxtlen  = 27

         if (@tablen < 23)
            begin
               select @mintablen = @tablen
            end

         if (@typelen < 23)
            begin
               select @mintypelen = @typelen
            end

         if (@txtlen < 27)
            begin
               select @txtlen = 27
            end
      end
   else
      begin
         select @mintablen  = 27,
                @mintypelen = 29,
                @mintxtlen  = 19

         if (@tablen < 27)
            begin
               select @mintablen = @tablen
            end

         if (@typelen < 29)
            begin
               select @mintypelen = @typelen
            end

         if (@txtlen < 19)
            begin
               select @txtlen = 19
            end
      end

   while ((@tablen + @typelen + @txtlen) > 75)
      begin
         if (@tablen > @mintablen)
            begin
               select @tablen = @tablen - 1
            end

         if (@typelen > @mintypelen)
            begin
               select @typelen = @typelen - 1
            end

         if (@txtlen > @mintxtlen)
            begin
               select @txtlen = @txtlen - 1
            end
      end

   if (@adjust_flag = 1)
      begin
         select @tablen = (75 - @typelen - @txtlen)
      end

   select @cmdstring = 'select ''TABLE'' = substring (printname, 1,'
                       +  convert (varchar (2), @tablen)
                       +  '),'
                       +  '''INDEX TYPE'' = substring (txt, 1,'
                       +  convert (varchar (2), @typelen)
                       +  '),'

   if (@keyflag = 1)
      begin
         select @cmdstring = @cmdstring
                             +  '''(NULLABLE) / [VAR LEN] KEYS '' = substring (keys, 1,'
      end
   else
      begin
         select @cmdstring = @cmdstring  +  '''KEYS '' = substring (keys, 1,'
      end

   select @cmdstring = @cmdstring
                       +  convert (varchar (2), @txtlen)
                       +  ')'
                       +  ' from #chkindexes'
                       +  ' order by sort, indid'

   execute  (@cmdstring)
go
sp_existsprocedure sp_checkindexes_print
go

drop table #chkindexes
go


create procedure sp_checkindexes (@DENSITY  tinyint = 15) as
   set ansinull                      off
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    sp_checkindexes CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @adjust_flag   tinyint,
           @cmdstring     varchar (255),
           @cntr          varchar (  7),
           @indxtypelen   tinyint,
           @keyflag       tinyint,
           @keylen        tinyint,
           @minkeylen     tinyint,
           @minname       varchar ( 30),
           @mintablen     tinyint,
           @minuid        smallint,
           @objid         int,
           @subtypeflag   tinyint,
           @suspectindex  int,
           @tablen        tinyint

   create table #chkindexes
      (name       varchar ( 30)  null,
       printname  varchar ( 30)  null,
       uid        int            not null,
       id         int            not null,
       indid      tinyint        not null,
       osysstat   smallint       not null,
       osysstat2  int            not null,
       istatus    smallint       not null,
       istatus2   int            not null,
       keyflag    tinyint        not null,
       keycnt     tinyint        not null,
       txt        varchar ( 75)  null,
       keys       varchar (255)  null,
       cacheflag  tinyint        not null,
       dpages     char (11)      not null,
       ipages     int            not null,
       ratio      int            not null,
       maxlen     smallint       not null,
       rowcnt     int            not null,
       indxcnt    tinyint        not null,
       sort       varchar (63)   null)

   if ((user_id () = 1)  and
       (not exists (select *
                    from master.dbo.sysdatabases
                    where dbid            = db_id ()
                      and (status & 1024) = 1024)))
      begin
         print '    UPDATING INDEX STATISTICS ON SYSTEM TABLES.'

         print ' '

         update index statistics syscolumns

         update index statistics sysindexes

         update index statistics sysobjects
      end

   select @keyflag      = 0,
          @subtypeflag  = 0,
          @suspectindex = -32768                      -- Complement of bad status

   if (exists (select *
               from sysindexes
               where (status & @suspectindex) <> 0))
      begin
         print '    THERE ARE SUSPECT INDEXES.'

         print ' '

         select 'TABLE' = substring (o.name + '(' + user_name (o.uid) + ')',
                                     1,
                                     40),
                INDID   = i.indid
         from sysindexes i,
              sysobjects o
         where o.id                        = i.id
           and (i.status & @suspectindex) <> 0
         order by o.name, user_name (o.uid), i.indid
      end
   else
      begin
         print '    THERE ARE NO SUSPECT INDEXES ON ANY TABLE.'
      end

   print ' '

   execute  sp_checkindexes_load

   delete from #chkindexes
   where indid              <> 1
      or (istatus & 2)       = 2
      or (osysstat2 & 8192) <> 8192

   select @cntr = ltrim (substring (convert (char (10), convert (money, count (*)), 1), 1, 7))
   from #chkindexes

   if (@cntr <> '0')
      begin
         print '    THERE ARE %1! NON-UNIQUE ALLPAGES CLUSTERED INDEXES.', @cntr

         print ' '

         execute  sp_checkindexes_format

         select @mintablen = 6,
                @tablen    = max (char_length (name)),
                @keylen    = max (char_length (keys))
         from #chkindexes

         select @keyflag = 1
         from #chkindexes
         where keyflag = 1

         select @subtypeflag = 1
         from #chkindexes
         where (istatus & 16)  = 16
           and ((txt like '%Allow%')  or  (txt like '%Ignore%'))

         if (@subtypeflag = 1)
            begin
               select @indxtypelen = 11
            end
         else
            begin
               select @indxtypelen = 0
            end

         if (@keyflag = 1)
            begin
               select @minkeylen = 27
            end
         else
            begin
               select @minkeylen = 4
            end

         if (@keylen < @minkeylen)
            begin
               select @keylen = @minkeylen
            end

         while ((@tablen + @indxtypelen + @keylen) > 75)
            begin
               select @adjust_flag = 1

               if (@tablen > @mintablen)
                  begin
                     select @tablen = @tablen - 1
                  end

               if (@keylen > @minkeylen)
                  begin
                     select @keylen = @keylen - 1
                  end
            end

         if (@adjust_flag = 1)
            begin
               select @keylen = (75 - @tablen - @indxtypelen)
            end
         select @cmdstring = 'select ''TABLE'' = substring (name,1,'
                             +  convert (varchar (2), @tablen)
                             +  '),'

         if (@subtypeflag = 1)
            begin
               select @cmdstring = @cmdstring
                                   +  '''INDEX SUBTYPE'' = isnull (substring (txt, 11, 14), ''-''),'
            end

         if (@keyflag = 1)
            begin
               select @cmdstring = @cmdstring
                                   +  '''(NULLABLE) / [VAR LEN] KEYS'''
            end
         else
            begin
               select @cmdstring = @cmdstring
                                   +  'KEYS'
            end

         select @cmdstring = @cmdstring
                             +  '= substring (keys,1,'
                             +  convert (varchar (2), @keylen)
                             +  ')'
                             +  ' from #chkindexes'
                             +  ' where indid = 1'
                             +  ' and (istatus & 2) <> 2'
                             +  ' and (osysstat2 & 8192) = 8192'
                             +  ' order by name, indid'

         execute  (@cmdstring)
      end
   else
      begin
         print '    ALL ALLPAGES CLUSTERED INDEXES ARE UNIQUE.'
      end

   print ' '

   truncate table #chkindexes

   if (exists (select o.*
               from sysobjects o
               where (o.sysstat & 15)  = 3
                 and (sysstat2 & 8192) = 8192))
      begin
         insert into #chkindexes
            select o.name, o.name, o.uid, i.id, i.indid, o.sysstat,
                   o.sysstat2, i.status, i.status2, 0, i.keycnt, NULL,
                   NULL, 0,
                   substring (convert (char (14),
                                       convert (money,
                                                data_pgs (i.id, i.doampg)),
                                       1),
                              1,
                              11),
                   0, 0, i.maxlen, t.rowcnt, 0, NULL
            from sysobjects o,
                 sysindexes i,
                 systabstats t
            where (o.sysstat & 15)    = 3
              and (o.sysstat2 & 8192) = 8192
              and i.id                = o.id
              and i.indid             = 0
              and t.id                = o.id
              and t.indid             = 0
              and (not exists (select i.*
                               from sysindexes i
                               where i.id    = o.id
                                 and i.indid = 1))

         select @cntr = ltrim (substring (convert (char (10), convert (money, count (*)), 1), 1, 7))
         from #chkindexes

         if (@cntr = '0')
            begin
               print '    ALL ALLPAGES USER TABLES HAVE A CLUSTERED INDEX.'
            end
         else
            begin
               print '    THERE ARE %1! ALLPAGES USER TABLES WITHOUT A CLUSTERED INDEX.',
                     @cntr

               print ' '

               update #chkindexes
               set indxcnt = (select count (i.id)
                              from sysindexes i
                              where i.id    = #chkindexes.id
                                and i.indid > 1
                                and i.indid < 254)

               update #chkindexes
               set printname = substring (i1.name + ' (' + user_name (i1.uid) + ')',
                                          1,
                                          30)
               from #chkindexes i1
               where i1.uid <> 1
                  or 1       < (select count (distinct i2.uid)
                                from #chkindexes i2
                                where i2.name = i1.name)

               print '                             NONCLUSTERED'

               select 'TABLE'       = printname,
                      'INDEXES'     = indxcnt,
                      'DATA PAGES'  = substring (convert (char (14),
                                                          convert (money,
                                                                   dpages),
                                                          1),
                                                 1,
                                                 11),
                      '  APP ROWS'  = substring (convert (char (14),
                                                          convert (money,
                                                                   rowcnt),
                                                          1),
                                                 1,
                                                 11),
                      'MAX LEN'     = substring (convert (char (10),
                                                          convert (money,
                                                                   maxlen),
                                                          1),
                                                 1,
                                                 7)
               from #chkindexes
               order by printname
            end
      end
   else
      begin
         print '    THERE ARE NO ALLPAGES USER TABLES.'
      end

   print ' '

   truncate table #chkindexes

   execute  sp_checkindexes_load

   execute  sp_checkindexes_format

   if (exists (select *
               from #chkindexes
               where ratio  > @DENSITY
                 and ipages > 2))
      begin
         print '    THERE IS AT LEAST ONE TABLE WITH AN INDEX/DATA PAGE RATIO > %1!%%.',
               @DENSITY

         print ' '

         select *
         into #chkind_den
         from #chkindexes
         where sort in (select distinct sort
                        from #chkindexes
                        where ratio  > @DENSITY
                          and ipages > 2)

         select @tablen = max (char_length (printname))
         from #chkind_den

         select @cmdstring = 'select ''TABLE'' = substring (printname, 1,'
                             +  convert (varchar (2), @tablen)
                             +  '),'
                             +  'INDID = indid,'
                             +  '''DATA PAGES'' = dpages,'
                             +  '''INDEX PAGES'' = substring (convert (char (14),'
                             +  ' convert (money, ipages), 1), 1, 11),'
                             +  '''INDEX/DATA %'' = convert (smallint, ratio)'
                             +  ' from #chkind_den'
                             +  ' order by name, indid'

         execute  (@cmdstring)

         drop table #chkind_den
      end
   else
      begin
         print '    ALL TABLES HAVE AN INDEX/DATA PAGE RATIO <= %1!%%.',
               @DENSITY
      end

   print ' '

   truncate table #chkindexes

   execute  sp_checkindexes_load

   if (exists (select *
               from #chkindexes
               where (istatus2 & 1) = 1))
      begin
         delete from #chkindexes
         where (istatus & 2048) = 2048
            or (istatus2 & 1)   = 1

         if (exists (select *
                     from #chkindexes))
            begin
               execute  sp_checkindexes_format

               print '    THE FOLLOWING INDEXES ARE NEITHER PRIMARY NOR SUPPORT FOREIGN KEYS.'

               print ' '

               execute  sp_checkindexes_print
            end
         else
            begin
               print '    ALL INDEXES SUPPORT PRIMARY KEYS OR FOREIGN KEYS.'
            end
      end
   else
      begin
         print '    INDEXES WILL NOT BE CHECKED FOR PRIMARY KEY USAGE OR FOREIGN KEY SUPPORT.'
      end

   print ' '
go
sp_addcomment sp_checkindexes, 'Lists user indexes with potential problems'
go
sp_addcomment sp_checkindexes, '   default DENSITY value is 15%'
go
sp_procxmode sp_checkindexes, anymode
go

------------ Temporary tables needed for creation of sp_indexes subroutines --

create table #indexes
   (name       varchar ( 30)  null,
    printname  varchar ( 30)  null,
    uid        int            not null,
    id         int            not null,
    indid      tinyint        not null,
    seq        tinyint        not null,
    cacheflag  tinyint        not null,
    status     smallint       not null,
    status2    smallint       not null,
    keycnt     tinyint        not null,
    keyflag    tinyint        not null,
    txt        varchar ( 75)  null,
    keys       varchar (255)  null,
    sort       varchar ( 63)  null)
go

create procedure sp_indexes_format (@TABLE_FLAG  tinyint)  as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @maxkeys    tinyint,
           @key        varchar (36),
           @keyflag    tinyint,
           @sequence   int

   select @sequence = 1

   if (@TABLE_FLAG = 0)                 -- MORE THAN ONE TABLE IN #indexes
      begin
         update #indexes
         set printname = substring (#indexes.name + ' (' + user_name (#indexes.uid) + ')',
                                    1,
                                    30)
         where #indexes.uid <> 1
            or 1             < (select count (distinct o.uid)
                                from sysobjects o
                                where o.name           = #indexes.name
                                  and (o.sysstat & 15) = 3)

         update #indexes
         set printname = substring (i.printname + ' [' + a.char_value + ']', 1, 30),
             cacheflag = 1
         from #indexes i,
              sysattributes a
         where a.class        = 3             -- buffer manager
           and a.attribute    = 0             -- cache bindings
           and a.object_type  = 'I'           -- index binding
           and i.id           = a.object
           and i.indid        = a.object_info1

         update #indexes
         set printname = ' '
         from #indexes i1
         where cacheflag = 0
           and exists (select *
                       from #indexes i2
                       where i2.id    = i1.id
                         and i2.indid = i1.indid
                         and i2.indid > (select min (i3.indid)
                                         from #indexes i3
                                         where i3.id    = i2.id
                                           and i3.indid > 0))

         update #indexes
         set printname = stuff (printname,
                                1,
                                charindex ('[', printname) - 1,
                                space (charindex ('[', printname) - 1))
         from #indexes i1
         where cacheflag                  = 1
           and charindex ('[', printname) < 29
           and exists (select *
                       from #indexes i2
                       where i2.id    = i1.id
                         and i2.indid = i1.indid
                         and i2.indid > (select min (i3.indid)
                                         from #indexes i3
                                         where i3.id    = i2.id
                                           and i3.indid > 0))
      end

   while (@sequence < 8)
      begin
         update #indexes
         set txt = substring (ltrim (#indexes.txt + ' ' + i.txt), 1, 75)
         from sybsystemprocs.dbo.uitype i
         where i.txt_seq = @sequence
           and i.type2   = 0
           and (((i.type <> (#indexes.status & i.type)  and  (i.dflt = 1)))  or
                 (i.type  = (#indexes.status & i.type)))

         select @sequence = @sequence + 1
      end

   update #indexes
   set txt = 'Primary ' + substring (txt, 16, 50)
   where txt like 'Primary Unique%'

   update #indexes
   set txt = stuff (txt, charindex ('Nonclustered', txt), 12, '(Clustered)')
   where (status2 & 512) = 512

   select @maxkeys  = 1 + max (keycnt),
          @sequence = 1
   from #indexes

   while (@sequence < @maxkeys)
      begin
         update #indexes
         set keys = substring (i.keys
                               + ' '
                               + index_col (i.name, i.indid, @sequence, i.uid)
                               + ',',
                               1,
                               255)
         from syscolumns sc, sysindexes si, #indexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and sc.id            = i.id
           and @sequence       <= i.keycnt
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and sc.offset        > 0
           and (sc.status & 8) <> 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'ASC'

         update #indexes
         set keys = substring (i.keys
                               + ' '
                               + index_col (i.name, i.indid, @sequence, i.uid)
                               + '*,',
                               1,
                               255)
         from syscolumns sc, sysindexes si, #indexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and sc.id            = i.id
           and @sequence       <= i.keycnt
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and sc.offset        > 0
           and (sc.status & 8) <> 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'DESC'

         update #indexes
         set keyflag = 1,
             keys    = substring (i.keys
                                  + ' ['
                                  + index_col (i.name, i.indid, @sequence, i.uid)
                                  + '],',
                                  1,
                                  255)
         from syscolumns sc, sysindexes si, #indexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and sc.id            = i.id
           and @sequence       <= i.keycnt
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and sc.offset        < 0
           and (sc.status & 8) <> 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'ASC'

         update #indexes
         set keyflag = 1,
             keys    = substring (i.keys
                                  + ' ['
                                  + index_col (i.name, i.indid, @sequence, i.uid)
                                  + '*],',
                                  1,
                                  255)
         from syscolumns sc, sysindexes si, #indexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and sc.id            = i.id
           and @sequence       <= i.keycnt
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and sc.offset        < 0
           and (sc.status & 8) <> 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'DESC'

         update #indexes
         set keyflag = 1,
             keys    = substring (i.keys
                                  + ' ('
                                  + index_col (i.name, i.indid, @sequence, i.uid)
                                  + '),',
                                  1,
                                  255)
         from syscolumns sc, sysindexes si, #indexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and sc.id            = i.id
           and @sequence       <= i.keycnt
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and (sc.status & 8)  = 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'ASC'

         update #indexes
         set keyflag = 1,
             keys    = substring (i.keys
                                  + ' ('
                                  + index_col (i.name, i.indid, @sequence, i.uid)
                                  + '*),',
                                  1,
                                  255)
         from syscolumns sc, sysindexes si, #indexes i
         where si.id            = i.id
           and si.indid         = i.indid
           and sc.id            = i.id
           and @sequence       <= i.keycnt
           and sc.name          = index_col (i.name, i.indid, @sequence, i.uid)
           and (sc.status & 8)  = 8
           and index_colorder (object_name (i.id), i.indid, @sequence, i.uid)
                                = 'DESC'

         select @sequence = @sequence + 1
      end

   update #indexes
   set keys = substring (keys, 2, char_length (keys) - 2)

   update #indexes
   set sort = name  +  ' '  +  user_name (uid)
go
sp_existsprocedure sp_indexes_format
go

create procedure sp_indexes_printall as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @adjust_flag  tinyint,
           @cmdstring    varchar (255),
           @keyflag      tinyint,
           @mintablen    tinyint,
           @mintypelen   tinyint,
           @mintxtlen    tinyint,
           @typelen      tinyint,
           @tablen       tinyint,
           @txtlen       smallint

   select @tablen  = max (char_length (printname)),
          @typelen = max (char_length (txt)),
          @txtlen  = max (char_length (keys))
   from #indexes

   select @keyflag = 1
   from #indexes
   where keyflag = 1

   -- NOTE THAT IN ADDITION TO THE THREE COLUMN WIDTHS THAT THE
   -- TITLE WIDTH FOR THE LAST COLUMN IS A FORMAT FACTOR.

   if (@keyflag = 1)
      begin
         select @mintablen  = 23,
                @mintypelen = 25,
                @mintxtlen  = 27

         if (@tablen < 23)
            begin
               select @mintablen = @tablen
            end

         if (@typelen < 23)
            begin
               select @mintypelen = @typelen
            end

         if (@txtlen < 27)
            begin
               select @txtlen = 27
            end
      end
   else
      begin
         select @mintablen  = 27,
                @mintypelen = 29,
                @mintxtlen  = 19

         if (@tablen < 27)
            begin
               select @mintablen = @tablen
            end

         if (@typelen < 29)
            begin
               select @mintypelen = @typelen
            end

         if (@txtlen < 19)
            begin
               select @txtlen = 19
            end
      end

   while ((@tablen + @typelen + @txtlen) > 75)
      begin
         if (@tablen > @mintablen)
            begin
               select @tablen = @tablen - 1
            end

         if (@typelen > @mintypelen)
            begin
               select @typelen = @typelen - 1
            end

         if (@txtlen > @mintxtlen)
            begin
               select @txtlen = @txtlen - 1
            end
      end

   if (@adjust_flag = 1)
      begin
         select @tablen = (75 - @typelen - @txtlen)
      end

   select @cmdstring = 'select ''TABLE'' = substring (printname, 1,'
                       +  convert (varchar (2), @tablen)
                       +  '),'
                       +  '''INDEX TYPE'' = substring (txt, 1,'
                       +  convert (varchar (2), @typelen)
                       +  '),'

   if (@keyflag = 1)
      begin
         select @cmdstring = @cmdstring
                             +  '''(NULLABLE) / [VAR LEN] KEYS '' = substring (keys, 1,'
      end
   else
      begin
         select @cmdstring = @cmdstring
                             +  '''KEYS '' = substring (keys, 1,'
      end

   select @cmdstring = @cmdstring
                       +  convert (varchar (2), @txtlen)
                       +  ')'
                       +  ' from #indexes'
                       +  ' order by sort, indid'

   execute  (@cmdstring)
go
sp_existsprocedure sp_indexes_printall
go

create procedure sp_indexes_printtbl as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @cmdstring  varchar (255),
           @keyflag    tinyint,
           @keylen     smallint,
           @seq        tinyint,
           @typelen    tinyint

   select @keylen  = max (char_length (keys)),
          @typelen = max (char_length (txt)),
          @seq     = 0
   from #indexes

   select @keyflag = 1
   from #indexes
   where keyflag = 1

   if (@keyflag = 1)
      begin
         if (@keylen < 27)
            begin
               select @keylen = 27
            end
      end
   else
      begin
         if (@keylen < 10)
            begin
               select @keylen = 10
            end
      end

   if ((@typelen + @keylen) > 76)
      begin
         select @keylen = 76 - @typelen
      end

   while (exists (select *
                  from #indexes
                  where char_length (keys) > @keylen))
      begin
         insert into #indexes
            select name,
                   printname,
                   uid,
                   id,
                   indid,
                   (@seq + 1),
                   cacheflag,
                   status,
                   status2,
                   keycnt,
                   keyflag,
                   txt,
                   ltrim (substring (keys, (@keylen + 1), 255)),
                   NULL
            from #indexes
            where seq                = @seq
              and char_length (keys) > @keylen

         -- TRIM THE "PRIOR" ROW TO @keylen.

         update #indexes
         set keys = substring (keys, 1, @keylen)
         where seq = @seq

         -- ADD THE "TAIL OF THE PRIOR ROW" TO THE BEGINNING OF THE "NEXT ROW".

         update #indexes
         set b.keys = substring (a.keys, @keylen - charindex (',', reverse (a.keys)) + 3, 255)
                      +  b.keys
         from #indexes b, #indexes a
         where b.seq   = @seq + 1
           and a.id    = b.id
           and a.indid = b.indid
           and a.seq   = @seq

         -- TRIM THE "TAIL OF THE PRIOR ROW".

         update #indexes
         set keys = substring (keys, 1, 1 + @keylen - charindex (',', reverse (keys)))
         where seq = @seq

         select @seq = @seq + 1
      end

   update #indexes
   set txt = ''
   where seq > 0

   select @cmdstring = 'select ''INDEX TYPE'' = substring (txt, 1,'
                       +  convert (varchar (2), @typelen)
                       +  '),'

   if (@keyflag = 1)
      begin
         select @cmdstring = @cmdstring
                             +  '''(NULLABLE) / [VAR LEN] KEYS'' = substring (keys, 1,'
      end
   else
      begin
         select @cmdstring = @cmdstring
                             +  'KEYS = substring (keys, 1,'
      end

   select @cmdstring = @cmdstring
                       +  convert (varchar (3), @keylen)
                       +  ')'
                       +  ' from #indexes'
                       +  ' order by indid, seq'

   execute  (@cmdstring)
go
sp_existsprocedure sp_indexes_printtbl
go

drop table #indexes
go

create procedure sp_indexes (@PREFIX  varchar (30) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    sp_indexes CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @exactcnt    int,
           @indexcnt    int,
           @likestring  varchar ( 30),
           @name        varchar ( 30),
           @tablecnt    int,
           @tableid     int,
           @uid         smallint

   create table #indexes
      (name       varchar ( 30)  null,
       printname  varchar ( 30)  null,
       uid        int            not null,
       id         int            not null,
       indid      tinyint        not null,
       seq        tinyint        not null,
       cacheflag  tinyint        not null,
       status     smallint       not null,
       status2    smallint       not null,
       keycnt     tinyint        not null,
       keyflag    tinyint        not null,
       txt        varchar ( 75)  null,
       keys       varchar (255)  null,
       sort       varchar ( 63)  null)

   if (@PREFIX IS NULL)
      begin
         insert into #indexes
            select o.name, o.name, o.uid, i.id, i.indid, 0, 0, i.status,
                   i.status2, i.keycnt, 0, NULL, NULL, NULL
            from sysindexes i,
                 sysobjects o
            where (o.sysstat & 15) = 3
              and i.id             = o.id
              and i.indid          = 1

         insert into #indexes
            select o.name, o.name, o.uid, i.id, i.indid, 0, 0, i.status,
                   i.status2, (i.keycnt - 1), 0, NULL, NULL, NULL
            from sysindexes i,
                 sysobjects o
            where (o.sysstat & 15) = 3
              and i.id             = o.id
              and i.indid          > 1
              and i.indid          < 254

         if (exists (select *
                     from #indexes))
            begin
               execute  sp_exclude_object  @TYPE='EXT',
                                           @TABLENAME='#indexes',
                                           @COLUMNNAME='name'

               execute  sp_indexes_format  @TABLE_FLAG=0

               execute  sp_indexes_printall
            end
         else
            begin
               print '    THERE ARE NO INDEXES ON USER TABLES.'

               print ' '
            end
      end
   else
      begin
         execute  sp_like_string  @INPUT_STRING=@PREFIX,
                                  @LIKE_STRING=@likestring  output

         select @exactcnt = count (*)
         from sysobjects
         where name            = @PREFIX
           and (sysstat & 15) in (1, 3)

         select @tablecnt = count (*)
         from sysobjects
         where name           like @likestring
           and (sysstat & 15)   in (1, 3)

         if (@exactcnt = 1)
            begin
               select @tableid = id,
                      @uid     = uid
               from sysobjects
               where name            = @PREFIX
                 and (sysstat & 15) in (1, 3)

               select @indexcnt = count (distinct indid)
               from sysindexes
               where id    = @tableid
                 and indid > 0
                 and indid < 254

               if (@indexcnt = 0)
                  begin
                     print '    THERE ARE NO INDEXES ON TABLE %1!.', @PREFIX

                     print ' '
                  end
               else
                  begin
                    if (@indexcnt = 1)
                       begin
                          print '    INDEX IS ON TABLE %1!.', @PREFIX
                       end
                    else
                       begin
                          print '    INDEXES ARE ON TABLE %1!.', @PREFIX
                       end

                    print ' '

                    insert into #indexes
                       select @PREFIX, NULL, @uid, id, 1, 0, 0, status, status2,
                              keycnt, 0, NULL, NULL, NULL
                       from sysindexes
                       where id    = @tableid
                         and indid = 1

                    insert into #indexes
                       select @PREFIX, NULL, @uid, id, indid, 0, 0, status, status2,
                              (keycnt - 1), 0, NULL, NULL, NULL
                       from sysindexes
                       where id    = @tableid
                         and indid > 1
                         and indid < 254

                     execute  sp_indexes_format  @TABLE_FLAG=1

                     execute  sp_indexes_printtbl
                  end
            end
         else if (@tablecnt = 0)
            begin
               print '    TABLES LIKE %1! DO NOT EXIST.', @likestring

               print ' '
            end
         else if (@tablecnt = 1)
            begin
               select @name    = name,
                      @tableid = id,
                      @uid     = uid
               from sysobjects
               where name           like @likestring
                 and (sysstat & 15)   in (1, 3)

               select @indexcnt = count (distinct indid)
               from sysindexes
               where id    = @tableid
                 and indid > 0
                 and indid < 254

               if (@indexcnt = 0)
                  begin
                     print '    THERE ARE NO INDEXES ON TABLE %1!.', @name

                     print ' '
                  end
               else
                  begin
                    if (@indexcnt = 1)
                       begin
                          print '    INDEX IS ON TABLE %1!.', @name
                       end
                    else
                       begin
                          print '    INDEXES ARE ON TABLE %1!.', @name
                       end

                    print ' '

                    insert into #indexes
                       select @name, NULL, @uid, id, 1, 0, 0, status, status2,
                              keycnt, 0, NULL, NULL, NULL
                       from sysindexes
                       where id    = @tableid
                         and indid = 1

                    insert into #indexes
                       select @name, NULL, @uid, id, indid, 0, 0, status, status2,
                              (keycnt - 1), 0, NULL, NULL, NULL
                       from sysindexes
                       where id    = @tableid
                         and indid > 1
                         and indid < 254

                    execute  sp_indexes_format  @TABLE_FLAG=1

                    execute  sp_indexes_printtbl
                  end
            end
         else
            begin
               select @indexcnt = count (distinct i.id)
               from sysindexes i,
                    sysobjects o
               where o.name           like @likestring
                 and (o.sysstat & 15)   in (1, 3)
                 and i.id                = o.id
                 and i.indid             > 0
                 and i.indid             < 254

               if (@indexcnt = 0)
                  begin
                     print '    THERE ARE NO INDEXES ON TABLES LIKE %1!.', @likestring

                     print ' '
                  end
               else if (@indexcnt = 1)
                  begin
                     select @name    = o.name,
                            @tableid = o.id,
                            @uid     = o.uid
                     from sysindexes i,
                          sysobjects o
                     where o.name           like @likestring
                       and (o.sysstat & 15)   in (1, 3)
                       and i.id                = o.id
                       and i.indid             > 0
                       and i.indid             < 254

                     print '    INDEX IS ON TABLE %1!.', @name

                     print ' '

                     insert into #indexes
                        select @name, NULL, @uid, id, 1, 0, 0, status, status2,
                               keycnt, 0, NULL, NULL, NULL
                        from sysindexes
                        where id    = @tableid
                          and indid = 1

                     insert into #indexes
                        select @name, NULL, @uid, id, indid, 0, 0, status, status2,
                               (keycnt - 1), 0, NULL, NULL, NULL
                        from sysindexes
                        where id    = @tableid
                          and indid > 1
                          and indid < 254

                     execute  sp_indexes_format  @TABLE_FLAG=1

                     execute  sp_indexes_printtbl
                  end
               else
                  begin
                     insert into #indexes
                        select o.name, o.name, o.uid, i.id, 1, 0, 0, i.status,
                               i.status2, i.keycnt, 0, NULL, NULL, NULL
                        from sysindexes i,
                             sysobjects o
                        where o.name           like @likestring
                          and (o.sysstat & 15)   in (1, 3)
                          and i.id                = o.id
                          and i.indid             = 1

                     insert into #indexes
                        select o.name, o.name, o.uid, i.id, i.indid, 0, 0, i.status,
                               i.status2, (i.keycnt - 1), 0, NULL, NULL, NULL
                        from sysindexes i,
                             sysobjects o
                        where o.name           like @likestring
                          and (o.sysstat & 15)   in (1, 3)
                          and i.id                = o.id
                          and i.indid             > 1
                          and i.indid             < 254

                     execute  sp_indexes_format  @TABLE_FLAG=0

                     execute  sp_indexes_printall
                  end
            end
      end
go
sp_addcomment sp_indexes, 'Lists indexes/keys for tables like PREFIX% or (default) all user tables'
go
sp_procxmode sp_indexes, anymode
go

create procedure sp_indexes2 (@PREFIX varchar (30) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    sp_indexes2 CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring   varchar (255),
           @idxnamelen  tinyint,
           @indexcnt    int,
           @likestring  varchar ( 30),
           @name        varchar ( 30),
           @tablecnt    int,
           @tableid     int,
           @tabnamelen  tinyint,
           @uid         smallint

   create table #indexes2
      (id         int            not null,
       indid      tinyint        not null,
       uid        int            not null,
       keylen     smallint       not null,
       keycnt     tinyint        not null,
       mrpp       tinyint        not null,
       ff         tinyint        not null,
       rpg        tinyint        not null,
       name       varchar ( 30)  not null,
       sort       char    ( 30)  not null,
       indname    varchar ( 30)  not null)

   if (@PREFIX IS NULL)
      begin
         if (exists (select i.*
                     from sysindexes i,
                          sysobjects o
                     where i.indid           > 0
                       and i.indid           < 254
                       and i.id              = o.id
                       and (o.sysstat & 15) in (1, 3)))
            begin
               insert into #indexes2
                  select i.id,
                         i.indid,
                         o.uid,
                         i.maxirow,
                         i.keycnt,
                         i.maxrowsperpage,
                         i.fill_factor,
                         i.res_page_gap,
                         o.name,
                         ' ',
                         i.name
                  from sysindexes i,
                       sysobjects o
                  where i.id              = o.id
                    and (o.sysstat & 15) in (1, 3)
                    and i.indid           = 1

               insert into #indexes2
                  select i.id,
                         i.indid,
                         o.uid,
                         i.maxirow,
                         i.keycnt - 1,
                         i.maxrowsperpage,
                         i.fill_factor,
                         i.res_page_gap,
                         o.name,
                         ' ',
                         i.name
                  from sysindexes i,
                       sysobjects o
                  where i.id              = o.id
                    and (o.sysstat & 15) in (1, 3)
                    and i.indid           > 1
                    and i.indid          < 254

               execute  sp_exclude_object  @TYPE='EXT',
                                           @TABLENAME='#indexes2',
                                           @COLUMNNAME='name'

               update #indexes2
               set name = substring (i.name + ' (' + user_name (i.uid) + ')', 1, 30)
               from #indexes2 i
               where i.uid <> 1
                  or 1      < (select count (distinct i2.uid)
                               from #indexes2 i2
                               where i2.name = i.name)

               update #indexes2
               set sort = name

               update #indexes2
               set name = ' '
               from #indexes2 i1
               where exists (select *
                             from #indexes2 i2
                             where i2.id    = i1.id
                               and i2.indid = i1.indid
                               and i2.indid > (select min (i3.indid)
                                               from #indexes2 i3
                                               where i3.id = i2.id))

               select @idxnamelen = max (char_length (indname)),
                      @tabnamelen = max (char_length (name))
               from #indexes2

               if (@idxnamelen + @tabnamelen > 45)
                  begin
                     select @tabnamelen = 45 - @idxnamelen
                  end

               select @cmdstring = 'select ''TABLE'' = substring (name, 1,'
                                   +  convert (varchar (2), @tabnamelen)
                                   +  '),'
                                   +  'INDID = indid,'
                                   +  '''INDEX NAME'' = substring (indname, 1,'
                                   +  convert (varchar (2), @idxnamelen)
                                   +  '),'
                                   +  'KEYLEN = keylen,'
                                   +  'KEYS = keycnt,'
                                   +  'MRPP = mrpp,'
                                   +  'FF = ff,'
                                   +  'GAP = rpg'
                                   +  ' from #indexes2'
                                   +  ' order by sort, indid'

               execute  (@cmdstring)
            end
         else
            begin
               print '    THERE ARE NO INDEXES ON USER TABLES.'

               print ' '
            end
      end
   else
      begin
         execute  sp_like_string  @INPUT_STRING=@PREFIX,
                                  @LIKE_STRING=@likestring  output

         select @tablecnt = count (*)
         from sysobjects
         where name           like @likestring
           and (sysstat & 15)   in (1, 3)

         if (@tablecnt = 0)
            begin
               print '    TABLES LIKE %1! DO NOT EXIST.', @likestring

               print ' '
            end
         else
            begin
               select @indexcnt = count (distinct i.id)
               from sysindexes i,
                    sysobjects o
               where o.name           like @likestring
                 and (o.sysstat & 15)   in (1, 3)
                 and i.id                = o.id
                 and i.indid             > 0
                 and i.indid             < 254

               if (@indexcnt = 0)
                  begin
                     print '    THERE ARE NO INDEXES FOR TABLES LIKE %1!.', @likestring

                     print ' '
                  end
               else if (@indexcnt = 1)
                  begin
                     select @name    = o.name,
                            @tableid = o.id,
                            @uid     = o.uid
                     from sysindexes i,
                          sysobjects o
                     where o.name           like @likestring
                       and (o.sysstat & 15)   in (1, 3)
                       and i.id                = o.id
                       and i.indid             > 0
                       and i.indid             < 254

                     print '    INDEXES ARE FOR TABLE %1!.', @name

                     print ' '

                        select INDID        = convert (tinyint, 0),
                               'INDEX NAME' = space (30),
                               KEYLEN       = convert (smallint, 0),
                               KEYS         = convert (tinyint, 0),
                               MRPP         = convert (tinyint, 0),
                               FF           = convert (tinyint, 0),
                               GAP          = convert (tinyint, 0)
                        where 1 = 2
                     union all
                        select indid,
                               name,
                               maxirow,
                               convert (tinyint, keycnt),
                               convert (tinyint, maxrowsperpage),
                               convert (tinyint, fill_factor),
                               convert (tinyint, res_page_gap)
                        from sysindexes
                        where id    = @tableid
                          and indid = 1
                     union all
                        select indid,
                               name,
                               maxirow,
                               convert (tinyint, keycnt - 1),
                               convert (tinyint, maxrowsperpage),
                               convert (tinyint, fill_factor),
                               convert (tinyint, res_page_gap)
                        from sysindexes
                        where id    = @tableid
                          and indid > 1
                          and indid < 254
                     order by INDID
                  end
               else
                  begin
                     insert into #indexes2
                        select i.id,
                               i.indid,
                               o.uid,
                               i.maxirow,
                               i.keycnt,
                               i.maxrowsperpage,
                               i.fill_factor,
                               i.res_page_gap,
                               o.name,
                               ' ',
                               i.name
                        from sysindexes i,
                             sysobjects o
                        where o.name           like @likestring
                          and (o.sysstat & 15)   in (1, 3)
                          and i.id                = o.id
                          and i.indid             = 1

                     insert into #indexes2
                        select i.id,
                               i.indid,
                               o.uid,
                               i.maxirow,
                               i.keycnt - 1,
                               i.maxrowsperpage,
                               i.fill_factor,
                               i.res_page_gap,
                               o.name,
                               ' ',
                               i.name
                        from sysindexes i,
                             sysobjects o
                        where o.name           like @likestring
                          and (o.sysstat & 15)   in (1, 3)
                          and i.id                = o.id
                          and i.indid             > 1
                          and i.indid             < 254

                     update #indexes2
                     set name = substring (i.name + ' (' + user_name (i.uid) + ')', 1, 30)
                     from #indexes2 i
                     where i.uid <> 1
                        or 1      < (select count (distinct i2.uid)
                                     from #indexes2 i2
                                     where i2.name = i.name)

                     update #indexes2
                     set sort = name

                     update #indexes2
                     set name = ' '
                     from #indexes2 i1
                     where exists (select *
                                   from #indexes2 i2
                                   where i2.id    = i1.id
                                     and i2.indid = i1.indid
                                     and i2.indid > (select min (i3.indid)
                                                     from #indexes2 i3
                                                     where i3.id = i2.id))

                     select @idxnamelen = max (char_length (indname)),
                            @tabnamelen = max (char_length (name))
                     from #indexes2

                     if (@idxnamelen + @tabnamelen > 45)
                        begin
                           select @tabnamelen = 45 - @idxnamelen
                        end

                     select @cmdstring = 'select ''TABLE'' = substring (name, 1,'
                                         +  convert (varchar (2), @tabnamelen)
                                         +  '),'
                                         +  'INDID = indid,'
                                         +  '''INDEX NAME'' = substring (indname, 1,'
                                         +  convert (varchar (2), @idxnamelen)
                                         +  '),'
                                         +  'KEYLEN = keylen,'
                                         +  'KEYS = keycnt,'
                                         +  'MRPP = mrpp,'
                                         +  'FF = ff,'
                                         +  'GAP = rpg'
                                         +  ' from #indexes2'
                                         +  ' order by sort, indid'

                     execute  (@cmdstring)
                  end
            end
      end
go
sp_addcomment sp_indexes2, 'Lists secondary index information for tables like PREFIX% or'
go
sp_addcomment sp_indexes2, '(default) all user tables'
go
sp_procxmode sp_indexes2, anymode
go

create procedure sp_selectivity as
   set ansinull                      off              -- Non-ANSI
   set close on endtran              on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 0)
            begin
               set transaction isolation level 0
            end
      end
   else
      begin
         print '    sp_selectivity CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring1      varchar (255),
           @cmdstring2      varchar (255),
           @cmdstring3      varchar (255),
           @col1            varchar ( 30),
           @col2            varchar ( 30),
           @col3            varchar ( 30),
           @col4            varchar ( 30),
           @col5            varchar ( 30),
           @col6            varchar ( 30),
           @col7            varchar ( 30),
           @col8            varchar ( 30),
           @col9            varchar ( 30),
           @col10           varchar ( 30),
           @col11           varchar ( 30),
           @col12           varchar ( 30),
           @col13           varchar ( 30),
           @col14           varchar ( 30),
           @col15           varchar ( 30),
           @col16           varchar ( 30),
           @errorchar       varchar (  4),
           @i               tinyint,
           @indid           tinyint,
           @indid_char      varchar (  3),
           @keylen          tinyint,
           @tablen          tinyint,
           @tabname         varchar ( 30),
           @uid             smallint,
           @uid_char        varchar (  6),
           @username        varchar ( 30)

   declare slctvty_cursor cursor for
      select o.name,
             o.uid,
             i.indid,
             index_col (o.name, i.indid, 1, o.uid),
             index_col (o.name, i.indid, 2, o.uid),
             index_col (o.name, i.indid, 3, o.uid),
             index_col (o.name, i.indid, 4, o.uid),
             index_col (o.name, i.indid, 5, o.uid),
             index_col (o.name, i.indid, 6, o.uid),
             index_col (o.name, i.indid, 7, o.uid),
             index_col (o.name, i.indid, 8, o.uid),
             index_col (o.name, i.indid, 9, o.uid),
             index_col (o.name, i.indid, 10, o.uid),
             index_col (o.name, i.indid, 11, o.uid),
             index_col (o.name, i.indid, 12, o.uid),
             index_col (o.name, i.indid, 13, o.uid),
             index_col (o.name, i.indid, 14, o.uid),
             index_col (o.name, i.indid, 15, o.uid),
             index_col (o.name, i.indid, 16, o.uid)
      from sysindexes i,
           sysobjects o
      where (o.sysstat & 15)     = 3               -- User table
        and i.id                 = o.id
        and i.indid              > 0               -- Not the table space
        and i.indid              < 254             -- Not an Image/Text
        and (i.status & 2)      <> 2               -- Not a Unique index
        and ((charindex ('sa_role', show_role ()) > 0)  or
             (o.uid = user_id ())                       or
             (exists (select *
                      from sysprotects p
                      where p.id           = o.id
                        and p.action       = 193
                        and p.protecttype in (0, 1)
                        and p.uid          = user_id ()
                        and ((p.columns IS NULL)  or  (p.columns = 0)))))
      order by o.name, user_name (o.uid), i.indid
   for read only

   print ' '

   create table #slct
      (tabname      varchar (63)      not null,
       sort         varchar (63)      not null,
       uid          smallint          not null,
       indid        tinyint           not null,
       keytext      varchar (44)      not null,
       selectivity  double precision  not null)

   open slctvty_cursor

   fetch slctvty_cursor into @tabname, @uid, @indid, @col1, @col2, @col3, @col4,
                             @col5, @col6, @col7, @col8, @col9, @col10, @col11,
                             @col12, @col13, @col14, @col15, @col16

   if (@@sqlstatus = 2)
      begin
         select @username = user_name ()

         print '    THERE ARE NO NON-UNIQUE INDEXES FOR USER TABLES ACCESSIBLE BY %1!.',
               @username
      end
   else
      begin
         print '    LOADING WORK TABLE.'

         while (@@sqlstatus = 0)
            begin
               select @indid_char = convert (varchar (3), @indid),
                      @uid_char   = convert (varchar (6), @uid),
                      @username   = user_name (@uid)

               select @tabname = '"'  +  @tabname  +  '"'
               where valid_name (@tabname) = 0

               select @col1 = '"'  +  @col1  +  '"'
               where valid_name (@col1) = 0

               select @col1 = '"'  +  @col1  +  '"'
               where valid_name (isnull (@col1, 'A')) = 0

               select @col2 = '"'  +  @col2  +  '"'
               where valid_name (isnull (@col2, 'A')) = 0

               select @col3 = '"'  +  @col3  +  '"'
               where valid_name (isnull (@col3, 'A')) = 0

               select @col4 = '"'  +  @col4  +  '"'
               where valid_name (isnull (@col4, 'A')) = 0

               select @col5 = '"'  +  @col5  +  '"'
               where valid_name (isnull (@col5, 'A')) = 0

               select @col6 = '"'  +  @col6  +  '"'
               where valid_name (isnull (@col6, 'A')) = 0

               select @col7 = '"'  +  @col7  +  '"'
               where valid_name (isnull (@col7, 'A')) = 0

               select @col8 = '"'  +  @col8  +  '"'
               where valid_name (isnull (@col8, 'A')) = 0

               select @col9 = '"'  +  @col9  +  '"'
               where valid_name (isnull (@col9, 'A')) = 0

               select @col10 = '"'  +  @col10  +  '"'
               where valid_name (isnull (@col10, 'A')) = 0

               select @col11 = '"'  +  @col11  +  '"'
               where valid_name (isnull (@col11, 'A')) = 0

               select @col12 = '"'  +  @col12  +  '"'
               where valid_name (isnull (@col12, 'A')) = 0

               select @col13 = '"'  +  @col13  +  '"'
               where valid_name (isnull (@col13, 'A')) = 0

               select @col14 = '"'  +  @col14  +  '"'
               where valid_name (isnull (@col14, 'A')) = 0

               select @col15 = '"'  +  @col15  +  '"'
               where valid_name (isnull (@col15, 'A')) = 0

               select @col16 = '"'  +  @col16  +  '"'
               where valid_name (isnull (@col16, 'A')) = 0

               select @cmdstring1 = 'insert into #slct select '''
                                    +  @tabname
                                    +  ''', '' '', '
                                    +  @uid_char
                                    +  ', '
                                    +  @indid_char
                                    +  ', '''
                                    +  substring (@col1
                                                  +  ', '  +  @col2
                                                  +  ', '  +  @col3
                                                  +  ', '  +  @col4
                                                  +  ', '  +  @col5
                                                  +  ', '  +  @col6
                                                  +  ', '  +  @col7
                                                  +  ', '  +  @col8,
                                                  1,
                                                  44)

               if (charindex (', ,', @cmdstring1) > 1)
                  begin
                     select @cmdstring1 = substring (@cmdstring1,
                                                     1,
                                                     charindex (', ,', @cmdstring1) - 1)
                  end

               select @cmdstring1 = @cmdstring1
                                    +  ''', isnull (avg (1.0 * count ('
                                    +  @col1
                                    +  ')), 0) from '
                                    +  @username
                                    +  '.'
                                    +  @tabname
                                    +  ' group by '
                                    +  @col1
                                    +  ', '
                                    +  @col2

               if (right (@cmdstring1, 2) = ', ')
                  begin
                     select @cmdstring1 = substring (@cmdstring1,
                                                     1,
                                                     char_length (@cmdstring1) - 2)
                  end
               else
                  begin
                     select @cmdstring1 = substring (@cmdstring1,
                                                     1,
                                                     char_length (@cmdstring1) - charindex (',', reverse (@cmdstring1)))
                  end

               if (@col2 IS NOT NULL)
                  begin
                     select @cmdstring2 = ', '
                                          +  @col2
                                          +  ', '  +  @col3
                                          +  ', '  +  @col4
                                          +  ', '  +  @col5
                                          +  ', '  +  @col6
                                          +  ', '  +  @col7
                                          +  ', '  +  @col8

                    if (charindex (', ,', @cmdstring2) > 1)
                        begin
                           select @cmdstring2 = substring (@cmdstring2,
                                                           1,
                                                           charindex (', ,', @cmdstring2) - 1)
                        end
                     else
                        begin
                           select @cmdstring2 = substring (@cmdstring2,
                                                           1,
                                                           char_length (@cmdstring2) - charindex (',', reverse (@cmdstring2)))
                        end

                     if (@col9 IS NOT NULL)
                        begin
                           select @cmdstring3 = ', '
                                                +  @col9
                                                +  ', '  +  @col10
                                                +  ', '  +  @col11
                                                +  ', '  +  @col12
                                                +  ', '  +  @col13
                                                +  ', '  +  @col14
                                                +  ', '  +  @col15
                                                +  ', '  +  @col16

                           if (charindex (', ,', @cmdstring3) > 1)
                              begin
                                 select @cmdstring3 = substring (@cmdstring3,
                                                                 1,
                                                                 charindex (', ,', @cmdstring3) - 1)
                              end
                           else
                              begin
                                 select @cmdstring3 = substring (@cmdstring3,
                                                                 1,
                                                                 char_length (@cmdstring3) - charindex (',', reverse (@cmdstring3)))
                              end
                        end
                  end

               execute  (@cmdstring1  +  @cmdstring2  +  @cmdstring3)

               select @cmdstring1 = ' ',
                      @cmdstring2 = ' ',
                      @cmdstring3 = ' '

               fetch slctvty_cursor into @tabname, @uid, @indid,
                                         @col1, @col2, @col3, @col4,
                                         @col5, @col6, @col7, @col8, @col9,
                                         @col10, @col11, @col12, @col13, @col14,
                                         @col15, @col16
            end

         if (@@sqlstatus <> 2)
            begin
               select @errorchar = convert (varchar (4), @@sqlstatus)

               print '  CURSOR ERROR.  STATUS = %1!.', @errorchar

               return 1
            end
      end

   close slctvty_cursor

   deallocate cursor slctvty_cursor

   if (exists (select *
               from #slct))
      begin
         if (exists (select *
                     from #slct
                     where selectivity = 1.0))
            begin
              print '    SHOULD SOME INDEXES BE DECLARED AS UNIQUE?  (SELECTIVITY = 1.00)'
            end

         print '    FORMATING DATA.'

         print ' '

         update #slct
         set tabname = substring (s.tabname + ' (' + user_name (s.uid)  + ')', 1, 63)
         from #slct s
         where s.uid <> 1
            or 1      < (select count (o.uid)
                         from sysobjects o
                         where o.name           = s.tabname
                           and (o.sysstat & 15) = 3)

         update #slct
         set sort = tabname

         update #slct
         set sort = substring (sort, 2, char_length (sort) - 2)
         where sort like '"%'

         update #slct
         set tabname = ' '
         from #slct s1
         where exists (select *
                      from #slct s2
                       where s2.tabname = s1.tabname
                         and s2.uid     = s1.uid
                         and s2.indid   = s1.indid
                         and s2.indid   > (select min (indid)
                                          from #slct s3
                                           where s3.tabname = s2.tabname
                                             and s3.uid     = s2.uid))

         select @keylen = max (char_length (keytext)),
                @tablen = max (char_length (tabname))
         from #slct

         if ((@keylen + @tablen) > 58)
            begin
               if (@tablen > 39)
                  begin
                     select @keylen = 19,
                            @tablen = 39
                  end
               else
                  begin
                     select @keylen = 58 - @tablen
                  end
            end

         select @cmdstring1 = 'select ''TABLE'' = substring (tabname, 1,'
                             +  convert (varchar (2), @tablen)
                             +  '),'
                             +  ' KEYS = substring (keytext, 1,'
                             +  convert (varchar (2), @keylen)
                             +  '),'
                             +  ' INDID = indid,'
                             +  ' SELECTIVITY ='
                             +  ' convert (char (11), convert (money, selectivity), 1)'
                             +  ' from #slct'
                             +  ' order by sort, indid'

         execute  (@cmdstring1)
      end

   print ' '
go
sp_addcomment sp_selectivity, 'Lists index selectivities for non-unique non-system indexes'
go
sp_procxmode sp_selectivity, anymode
go

dump transaction sybsystemprocs with truncate_only
go
