use sybsystemprocs
go

drop procedure sp_checkindexes_format
go
drop procedure sp_checkindexes_load
go
drop procedure sp_checkindexes
go
drop procedure sp_indexagg
go
drop procedure sp_indexes_format
go
drop procedure sp_indexes_printall
go
drop procedure sp_indexes_printtbl
go
drop procedure sp_indexes
go
drop procedure sp_indexchoices
go
drop procedure sp_indexnames
go
drop procedure sp_readonly
go
drop procedure sp_sysindexes            -- In 1151B release
go

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

create table #indexes
   (name       varchar ( 30)  null,
    printname  varchar ( 30)  null,
    uid        int            not null,
    id         int            not null,
    indid      tinyint        not null,
    status     smallint       not null,
    approws    int            not null,
    dist       int            not null,
    distkey    varchar ( 30)  null,
    keyflag    tinyint        not null,
    keycnt     tinyint        not null,
    maxlen     smallint       not null,
    txt        varchar ( 75)  null,
    keys       varchar (255)  null,
    drpages    char (14)      not null,
    dpages     char (11)      not null,
    ipages     int            not null,
    ratio      int            not null)
go

set quoted_identifier on
go

create procedure sp_checkindexes_format as
   set ansinull                      on
   set string_rtruncation            on

   declare @maxkeys   tinyint,
           @sequence  int,
           @LP        char (2)

   select @sequence = 1,
          @LP       = ' ('

   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, 60)
   where txt like 'Primary Unique%'

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

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

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

         update #indexes
         set keyflag = 1,
             keys    = substring (#indexes.keys
                                  + @LP
                                  + index_col (#indexes.name,
                                               #indexes.indid,
                                               @sequence,
                                               #indexes.uid)
                                  + '),',
                                  1,
                                  255)
         from syscolumns c,
              sysindexes i
         where i.id            = #indexes.id
           and i.indid         = #indexes.indid
           and i.keycnt       >= @sequence
           and c.id            = #indexes.id
           and c.name          = index_col (#indexes.name,
                                            #indexes.indid,
                                            @sequence,
                                            #indexes.uid)
           and (c.status & 8)  = 8

         select @sequence = @sequence + 1
      end

   update #indexes
   set printname = substring (i.name + @LP + user_name (i.uid) + ')', 1, 30)
   from #indexes 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 #indexes
   set name = printname

   update #indexes
   set distkey = substring (keys, 2, charindex (',', keys) - 2)
   where charindex (',', keys) > 2

   update #indexes
   set keys = substring (keys, 2, char_length (keys) - 2)
go
sp_procxmode sp_checkindexes_format, unchained
go

create procedure sp_checkindexes_load (@TABLE  varchar (63)) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   if (@TABLE 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) = 3))
            begin
               insert into #indexes
                  select o.name,
                         o.name,
                         o.uid,
                         i.id,
                         i.indid,                         -- indid
                         i.status,                        -- status
                         rowcnt (i.doampg),               -- approws
                         i.distribution,                  -- dist
                         NULL,                            -- distkey
                         0,                               -- keyflag
                         i.keycnt,                        -- keycnt
                         i.maxlen,                        -- maxlen
                         NULL,
                         NULL,
                         substring (convert (char (17),
                                             convert (money,
                                                      reserved_pgs (i.id, i.doampg)),
                                             1),
                                    1,
                                    14),
                         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)
                  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,                         -- indid
                         i.status,                        -- status
                         rowcnt (d.doampg),               -- approws
                         i.distribution,                  -- dist
                         NULL,                            -- distkey
                         0,                               -- keyflag
                         i.keycnt - 1,                    -- keycnt - 1
                         d.maxlen,                        -- maxlen
                         NULL,
                         NULL,
                         substring (convert (char (17),
                                             convert (money,
                                                      reserved_pgs (d.id, d.doampg)),
                                             1),
                                    1,
                                    14),
                         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)
                  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
      end
   else
      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)
                       and o.name            = @TABLE))
            begin
               insert into #indexes
                  select o.name,
                         o.name,
                         o.uid,
                         i.id,
                         i.indid,                         -- indid
                         i.status,                        -- status
                         rowcnt (i.doampg),               -- approws
                         i.distribution,                  -- dist
                         NULL,                            -- distkey
                         0,                               -- keyflag
                         i.keycnt,                        -- keycnt
                         i.maxlen,                        -- maxlen
                         NULL,
                         NULL,
                         substring (convert (char (17),
                                             convert (money,
                                                      reserved_pgs (i.id, i.doampg)),
                                             1),
                                    1,
                                    14),
                         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)
                  from sysindexes i,
                       sysobjects o
                  where o.id             = object_id (@TABLE)
                    and i.id             = o.id
                    and i.indid          = 1

               insert into #indexes
                  select o.name,
                         o.name,
                         o.uid,
                         i.id,
                         i.indid,                         -- indid
                         i.status,                        -- status
                         rowcnt (d.doampg),               -- approws
                         i.distribution,                  -- dist
                         NULL,                            -- distkey
                         0,                               -- keyflag
                         i.keycnt - 1,                    -- keycnt - 1
                         d.maxlen,                        -- maxlen
                         NULL,
                         NULL,
                         substring (convert (char (17),
                                             convert (money,
                                                      reserved_pgs (d.id, d.doampg)),
                                             1),
                                    1,
                                    14),
                         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)
                  from sysindexes i,
                       sysindexes d,
                       sysobjects o
                  where o.id             = object_id (@TABLE)
                    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 '   TABLE <%1!> DOES NOT EXIST OR HAS NO INDEXES.', @TABLE

               print ' '
            end
      end
go
sp_procxmode sp_checkindexes_load, unchained
go

drop table #indexes
go

create procedure sp_checkindexes (@TABLE    varchar (63) = NULL,
                                  @DENSITY  tinyint      = 15) as
   set ansinull                      on
   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 @keyflag       tinyint,
           @maxtablen     tinyint,
           @maxtxtlen     tinyint,
           @subtypeflag   tinyint,
           @suspectindex  int

   create table #indexes
      (name       varchar ( 30)  null,
       printname  varchar ( 30)  null,
       uid        int            not null,
       id         int            not null,
       indid      tinyint        not null,
       status     smallint       not null,
       approws    int            not null,
       dist       int            not null,
       distkey    varchar ( 30)  null,
       keyflag    tinyint        not null,
       keycnt     tinyint        not null,
       maxlen     smallint       not null,
       txt        varchar ( 75)  null,
       keys       varchar (255)  null,
       drpages    char (14)      not null,
       dpages     char (11)      not null,
       ipages     int            not null,
       ratio      int            not null)


   if (user_id () = 1)
      begin
         print '    UPDATING STATISTICS ON SYSTEM TABLES.'

         print ' '

         update statistics syscolumns

         update statistics sysindexes

         update 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 @TABLE

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

         select @maxtablen = max (char_length (printname)),
                @maxtxtlen = max (char_length (txt))
         from #indexes

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

         select @subtypeflag = 1
         from #indexes
         where char_length (txt) > 25

         if (@TABLE IS NULL)
            begin
               if (exists (select *
                           from #indexes
                           where (status & 2)  <> 2
                             and (status & 16)  = 16))
                  begin
                     print '    THERE ARE NON-UNIQUE CLUSTERED INDEXES.'

                     print ' '

                     if ((@keyflag = 0)  and  (@subtypeflag = 0))
                        begin
                           select 'TABLE' = substring (name, 1, 22),
                                  'KEYS'  = substring (keys, 1, 54)
                           from #indexes
                           where (status & 2)  <> 2
                             and (status & 16)  = 16
                           order by name, indid
                        end
                     else if ((@keyflag = 0)  and  (@subtypeflag = 1))
                        begin
                           select 'TABLE'         = substring (name, 1, 15),
                                  'INDEX SUBTYPE' = isnull (substring (txt, 11, 14), '-'),
                                  'KEYS'          = substring (keys, 1, 46)
                           from #indexes
                           where (status & 2)  <> 2
                             and (status & 16)  = 16
                           order by name, indid
                        end
                     else if ((@keyflag = 1)  and  (@subtypeflag = 0))
                        begin
                           select 'TABLE'                       = substring (name, 1, 22),
                                  '(NULLABLE) / [VAR LEN] KEYS' = substring (keys, 1, 54)
                           from #indexes
                           where (status & 2)  <> 2
                             and (status & 16)  = 16
                           order by name, indid
                        end
                     else
                        begin
                           select 'TABLE'                       = substring (name, 1, 15),
                                  'INDEX SUBTYPE'               = isnull (substring (txt, 11, 14), '-'),
                                  '(NULLABLE) / [VAR LEN] KEYS' = substring (keys, 1, 46)
                           from #indexes
                           where (status & 2)  <> 2
                             and (status & 16)  = 16
                           order by name, indid
                        end
                  end
               else
                  begin
                     print '    ALL CLUSTERED INDEXES ARE UNIQUE.'
                  end

               print ' '


               if (exists (select *
                           from #indexes
                           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 #indexes_den
                     from #indexes
                     where printname in (select distinct printname
                                         from #indexes
                                         where ratio  > @DENSITY
                                           and ipages > 2)

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

                     select 'TABLE'          = substring (printname, 1, 25),
                            'INDID'          = indid,
                            'DATA PAGES '    = dpages,
                            'INDEX PAGES'    = substring (convert (char (14),
                                                                   convert (money,
                                                                            ipages),
                                                                   1),
                                                          1,
                                                          11),
                            'INDEX/DATA %'   = convert (smallint, ratio)
                     from #indexes_den
                     order by name, indid
                  end
               else
                  begin
                     print '    ALL TABLES HAVE AN INDEX/DATA PAGE RATIO <= %1!%%.',
                           @DENSITY
                  end

               print ' '

               delete from #indexes
               where dist    <> 0
                 and keyflag  = 0

               if (exists (select *
                           from #indexes
                           where dist    = 0
                             and approws > 0))
                  begin
                     print '    THERE ARE USER INDEXES WITH A DEFAULT DISTRIBUTION PAGE.'

                     print ' '

                     select *
                     into #indexes_dist
                     from #indexes
                     where dist    = 0
                       and approws > 0

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

                     select @maxtablen = max (char_length (printname)),
                            @maxtxtlen = max (char_length (txt))
                     from #indexes_dist

                     if ((@maxtablen > 25)  and  (@maxtxtlen < 20))
                        begin
                           select 'TABLE'            = substring (printname, 1, 33),
                                  INDID              = indid,
                                  'INDEX TYPE'       = substring (txt, 1, 20),
                                  'DISTRIBUTION KEY' = substring (distkey, 1, 16)
                           from #indexes_dist
                           order by name, indid
                        end
                     else if ((@maxtablen > 25)  and  (@maxtxtlen > 19))
                        begin
                           select 'TABLE'            = substring (printname, 1, 26),
                                  INDID              = indid,
                                  'INDEX TYPE'       = substring (txt, 1, 27),
                                  'DISTRIBUTION KEY' = substring (distkey, 1, 16)
                           from #indexes_dist
                           order by name, indid
                        end
                     else if (@maxtxtlen > 20)
                        begin
                           select 'TABLE'            = substring (printname, 1, 24),
                                  INDID              = indid,
                                  'INDEX TYPE'       = substring (txt, 1, 29),
                                  'DISTRIBUTION KEY' = substring (distkey, 1, 16)
                           from #indexes_dist
                           order by name, indid
                        end
                     else
                        begin
                           select 'TABLE'            = substring (printname, 1, 24),
                                  INDID              = indid,
                                  'INDEX TYPE'       = substring (txt, 1, 20),
                                  'DISTRIBUTION KEY' = substring (distkey, 1, 25)
                           from #indexes_dist
                           order by name, indid
                        end
                  end
               else
                  begin
                     print '    ALL INDEXES HAVE A NON-DEFAULT DISTRIBUTION PAGE.'
                  end
            end
         else
            begin
               if (exists (select *
                           from #indexes
                           where (status & 2)  <> 2
                             and (status & 16)  = 16))
                  begin
                     print '    THE CLUSTERED INDEX ON <%1!> IS NON-UNIQUE.',
                           @TABLE

                     print ' '

                     if (exists (select *
                                 from #indexes
                                 where (status & 16)  = 16
                                   and keyflag        = 1))
                        begin
                           select 'INDEX SUBTYPE'               = isnull (substring (txt, 11, 14), '-'),
                                  '(NULLABLE) / [VAR LEN] KEYS' = substring (keys, 1, 62)
                           from #indexes
                           where (status & 16) = 16
                           order by name, indid
                        end
                     else
                        begin
                           select 'INDEX SUBTYPE' = isnull (substring (txt, 11, 14), '-'),
                                  'KEYS'          = substring (keys, 1, 62)
                           from #indexes
                           where (status & 16) = 16
                           order by name, indid
                        end
                  end

               print ' '

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

                     print ' '

                     select 'INDID'          = indid,
                            'INDEX TYPE'     = substring (txt, 1, 34),
                            'DATA PAGES '    = dpages,
                            'INDEX PAGES'    = substring (convert (char (14),
                                                                   convert (money,
                                                                            ipages),
                                                                   1),
                                                          1,
                                                          11),
                            'INDEX/DATA %'   = convert (smallint, ratio)
                     from #indexes
                     order by indid
                  end
               else
                  begin
                     print '    NO INDEX ON <%1!> HAS AN INDEX/DATA PAGE RATIO > %2!%%.',
                           @TABLE, @DENSITY
                  end

               print ' '

               delete from #indexes
               where dist    <> 0
                 and keyflag  = 0

               if (exists (select *
                           from #indexes
                           where dist    = 0
                             and approws > 0))
                  begin
                     print '    THERE ARE INDEXES ON <%1!> WITH A DEFAULT DISTRIBUTION PAGE.',
                           @TABLE

                     print ' '

                     select INDID              = indid,
                            'INDEX TYPE'       = substring (txt, 1, 35),
                            'DISTRIBUTION KEY' = distkey
                     from #indexes
                     where dist    = 0
                       and approws > 0
                     order by indid
                  end
               else
                  begin
                     print '    ALL INDEXES ON <%1!> HAVE A NON-DEFAULT DISTRIBUTION PAGE.',
                           @TABLE
                  end
            end
      end
go
sp_addcomment sp_checkindexes, 13, 'Lists user indexes with problems or potential problems'
go
sp_addcomment sp_checkindexes, 13, '   options (defaults) are TABLE (NULL) and DENSITY (15)'
go
sp_procxmode sp_checkindexes, anymode
go

create procedure sp_indexagg 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_indexagg> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @sequence      int,
           @suspectindex  int

   select @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

   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) = 3))
      begin
         create table #indexagg1
            (status smallint      not null)

         create table #indexagg2
            (status smallint      not null,
             C      int           not null,
             txt    varchar (45)  null)

         print '    COUNT TABLE OF INDEX TYPES FOR USER TABLES.'

         print ' '

         insert into #indexagg1
            select i.status
            from sysindexes i,
                 sysobjects o
            where (o.sysstat & 15) = 3
              and i.id             = o.id
              and i.indid          > 0
              and i.indid          < 254
            group by i.status
            having (o.sysstat & 15) = 3
               and i.id             = o.id
               and i.indid          > 0
               and i.indid          < 254

         insert into #indexagg2
            select distinct status, count (status), NULL
            from #indexagg1
            group by status

         select @sequence = 1

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

               select @sequence = @sequence + 1
            end

         update #indexagg2
         set txt = stuff (txt, charindex ('Primary Unique', txt), 14, 'Primary')
         where charindex ('Primary Unique', txt) > 0

         select distinct QUANTITY = convert (smallint, sum (C)),
                         TYPE     = txt
         from #indexagg2
         group by txt
         order by txt
      end
   else
      begin
         print '    THERE ARE NO INDEXES ON USER TABLES.'

         print ' '
      end
go
sp_addcomment sp_indexagg, 13, 'Displays index aggregates for all user tables'
go
sp_procxmode sp_indexagg, 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)
go

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

   declare @maxkeys    tinyint,
           @sequence   int

   select @sequence = 1

   if (@TABLE_FLAG = 0)
      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%'

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

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

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

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

         select @sequence = @sequence + 1
      end

   update #indexes
   set keys = substring (keys, 2, char_length (keys) - 2)
go
sp_procxmode sp_indexes_format, unchained
go

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

   declare @keyflag    tinyint,
           @maxkeylen  tinyint,
           @maxtablen  tinyint,
           @maxtxtlen  tinyint

   select @maxkeylen = max (char_length (keys)),
          @maxtablen = max (char_length (printname)),
          @maxtxtlen = max (char_length (txt))
   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
         if (@maxtablen < 16)
            begin
               if (@maxtxtlen < 21)
                  begin
                     select 'TABLE'                       = substring (printname, 1, 15),
                            'INDEX TYPE'                  = substring (txt, 1, 20),
                            '(NULLABLE) / [VAR LEN] KEYS' = substring (keys, 1, 40)
                     from #indexes
                     order by name, user_name (uid), indid
                  end
               else if (@maxtxtlen < 26)
                  begin
                     select 'TABLE'                       = substring (printname, 1, 15),
                            'INDEX TYPE'                  = substring (txt, 1, 25),
                            '(NULLABLE) / [VAR LEN] KEYS' = substring (keys, 1, 35)
                     from #indexes
                     order by name, user_name (uid), indid
                  end
               else
                  begin
                     select 'TABLE'                       = substring (printname, 1, 15),
                            'INDEX TYPE'                  = substring (txt, 1, 33),
                            '(NULLABLE) / [VAR LEN] KEYS' = substring (keys, 1, 27)
                     from #indexes
                     order by name, user_name (uid), indid
                  end
            end
         else
            begin
               if (@maxtxtlen < 21)
                  begin
                     select 'TABLE'                       = substring (printname, 1, 23),
                            'INDEX TYPE'                  = substring (txt, 1, 20),
                            '(NULLABLE) / [VAR LEN] KEYS' = substring (keys, 1, 32)
                     from #indexes
                     order by name, user_name (uid), indid
                  end
               else
                  begin
                     select 'TABLE'                       = substring (printname, 1, 23),
                            'INDEX TYPE'                  = substring (txt, 1, 25),
                            '(NULLABLE) / [VAR LEN] KEYS' = substring (keys, 1, 27)
                     from #indexes
                     order by name, user_name (uid), indid
                  end
            end
      end
   else
      begin
         if (@maxtablen < 16)
            begin
               if (@maxtxtlen < 21)
                  begin
                     select 'TABLE'      = substring (printname, 1, 15),
                            'INDEX TYPE' = substring (txt, 1, 20),
                            KEYS         = substring (keys, 1, 40)
                     from #indexes
                     order by name, user_name (uid), indid
                  end
               else if (@maxtxtlen < 26)
                  begin
                     select 'TABLE'      = substring (printname, 1, 15),
                            'INDEX TYPE' = substring (txt, 1, 25),
                            KEYS         = substring (keys, 1, 35)
                     from #indexes
                     order by name, user_name (uid), indid
                  end
               else
                  begin
                     select 'TABLE'      = substring (printname, 1, 15),
                            'INDEX TYPE' = substring (txt, 1, 33),
                            KEYS         = substring (keys, 1, 27)
                     from #indexes
                     order by name, user_name (uid), indid
                  end
            end
         else
            begin
               if (@maxtxtlen < 21)
                  begin
                     select 'TABLE'      = substring (printname, 1, 23),
                            'INDEX TYPE' = substring (txt, 1, 20),
                            KEYS         = substring (keys, 1, 32)
                     from #indexes
                     order by name, user_name (uid), indid
                  end
               else
                  begin
                     select 'TABLE'      = substring (printname, 1, 23),
                            'INDEX TYPE' = substring (txt, 1, 25),
                            KEYS         = substring (keys, 1, 27)
                     from #indexes
                     order by name, user_name (uid), indid
                  end
            end
      end
go
sp_procxmode sp_indexes_printall, unchained
go

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

   declare @keyflag      tinyint,
           @keytxtwidth  tinyint,
           @maxtxtlen    tinyint,
           @seq          tinyint

   select @maxtxtlen = max (char_length (txt)),
          @seq       = 0
   from #indexes

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

   if (@maxtxtlen < 21)
      begin
         select @keytxtwidth = 56
      end
   else if (@maxtxtlen < 35)
      begin
         select @keytxtwidth = 42
      end
   else
      begin
         select @keytxtwidth = 27
      end

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

         -- Trim the "prior" row to @keytxtwidth.

         update #indexes
         set keys = substring (keys, 1, @keytxtwidth)
         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, @keytxtwidth - 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 + @keytxtwidth - charindex (',', reverse (keys)))
         where seq = @seq

         select @seq = @seq + 1
      end

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

   -- 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
         if (@maxtxtlen < 21)
            begin
               select 'INDEX TYPE'                  = substring (txt, 1, 20),
                      '(NULLABLE) / [VAR LEN] KEYS' = substring (keys, 1, 56)
               from #indexes
               order by indid, seq
            end
         else if (@maxtxtlen < 35)
            begin
               select 'INDEX TYPE'                  = substring (txt, 1, 34),
                      '(NULLABLE) / [VAR LEN] KEYS' = substring (keys, 1, 42)
               from #indexes
               order by indid, seq
            end
         else
            begin
               select 'INDEX TYPE'                  = substring (txt, 1, 49),
                      '(NULLABLE) / [VAR LEN] KEYS' = substring (keys, 1, 27)
               from #indexes
               order by indid, seq
            end
      end
   else
      begin
         if (@maxtxtlen < 21)
            begin
               select 'INDEX TYPE' = substring (txt, 1, 20),
                      KEYS         = substring (keys, 1, 56)
               from #indexes
               order by indid, seq
            end
         else if (@maxtxtlen < 35)
            begin
               select 'INDEX TYPE' = substring (txt, 1, 34),
                      KEYS         = substring (keys, 1, 42)
               from #indexes
               order by indid, seq
            end
         else
            begin
               select 'INDEX TYPE' = substring (txt, 1, 49),
                      KEYS         = substring (keys, 1, 27)
               from #indexes
               order by indid, seq
            end
      end
go
sp_procxmode sp_indexes_printtbl, unchained
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 @exclude1  varchar (7),
           @exclude2  varchar (7),
           @exclude3  varchar (7),
           @exclude4  varchar (7),
           @indexcnt  int,
           @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)

   select @exclude1 = 'sys%',
          @exclude2 = 'pb%',
          @exclude3 = 'sr[_]%'

   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
            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
            from sysindexes i,
                 sysobjects o
            where (o.sysstat & 15) = 3
              and i.id             = o.id
              and i.indid          > 1
              and i.indid          < 254

         delete from #indexes
         where name like @exclude1
            or name like @exclude2
            or name like @exclude3
            or name like @exclude4

         if (exists (select *
                     from #indexes))
            begin
               execute sp_excld_prnt  'TABLES', @exclude1, @exclude2, @exclude3, @exclude4

               execute sp_indexes_format 0

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

               print ' '
            end
      end
   else
      begin
         select @tablecnt = count (*)
         from sysobjects
         where name           like (@PREFIX  +  '%')
           and (sysstat & 15)   in (1, 3)

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

               print ' '
            end
         else
            begin
               select @indexcnt = count (distinct i.id)
               from sysindexes i,
                    sysobjects o
               where o.name           like (@PREFIX  +  '%')
                 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!%%>.',
                           @PREFIX

                     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 (@PREFIX  +  '%')
                       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 ' '

                     insert into #indexes
                        select @name, NULL, @uid, id, 1, 0, 0, status, status2,
                               keycnt, 0, 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
                        from sysindexes
                        where id    = @tableid
                          and indid > 1
                          and indid < 254

                     execute sp_indexes_format 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
                        from sysindexes i,
                             sysobjects o
                        where o.name           like (@PREFIX  +  '%')
                          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
                        from sysindexes i,
                             sysobjects o
                        where o.name           like (@PREFIX  +  '%')
                          and (o.sysstat & 15)   in (1, 3)
                          and i.id                = o.id
                          and i.indid             > 1
                          and i.indid             < 254

                     execute sp_indexes_format 0

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

create procedure sp_indexchoices as
   set ansinull                      off              -- Non-ANSI
   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_indexchoices> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @keyflag     tinyint,
           @maxkeylen   tinyint,
           @maxkeys     tinyint,
           @maxtablen   tinyint,
           @maxtxtlen   tinyint,
           @minindid    tinyint,
           @minname     varchar (30),
           @minuid      smallint,
           @multicnt    varchar (7),
           @overlapcnt  varchar (7),
           @sequence    int,
           @unclustcnt  varchar (7)

   create table #ic
      (name       varchar (30)  null,
       printname  varchar (30)  null,
       uid        int           not null,
       id         int           not null,
       indid      tinyint       not null,
       dpages     int           not null,
       status     int           not null,             -- Also used for row count
       status2    smallint      not null,             -- Also used for maxlen
       keycnt     tinyint       not null,             -- also used for index count
       keyflag    tinyint       not null,
       typetxt    varchar (75)  null,
       keytext    varchar (75)  null)

   create table #ickeys
      (id         int           not null,
       indid      tinyint       not null,
       keyseq     tinyint       not null,
       keycol     varchar (30)  not null,
       coloffset  smallint      not null,
       colstatus  tinyint       not null)

   if (exists (select o.*
               from sysobjects o
               where (o.sysstat & 15) = 3))
      begin
         insert into #ic
            select o.name,
                   o.name,
                   o.uid,
                   o.id,
                   i.indid,
                   data_pgs (i.id, i.doampg),
                   rowcnt (i.doampg),
                   i.maxlen,
                   0,
                   0,
                   ' ',
                   ' '
            from sysindexes i,
                 sysobjects o
            where (o.sysstat & 15) = 3
              and i.id             = o.id
              and i.indid          = 0
              and (not exists (select i.*
                               from sysindexes i
                               where i.id    = o.id
                                 and i.indid = 1))

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

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

               print ' '

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

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

               print '                             NONCLUSTERED'

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

   truncate table #ic

   print ' '

   select @sequence = 1

   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) = 3))
      begin
         insert into #ic
            select o.name,
                   o.name,
                   o.uid,
                   i.id,
                   i.indid,
                   0,
                   i.status,
                   i.status2,
                   i.keycnt,
                   0,
                   NULL,
                   NULL
            from sysindexes i,
                 sysobjects o
            where (o.sysstat & 15) = 3
              and i.id             = o.id
              and i.indid          = 1

         insert into #ic
            select o.name,
                   o.name,
                   o.uid,
                   i.id,
                   i.indid,
                   0,
                   i.status,
                   i.status2,
                   (i.keycnt - 1),
                   0,
                   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

         while (@sequence < 17)
            begin
               insert into #ickeys
                  select i.id,
                         i.indid,
                         @sequence,
                         index_col (o.name, i.indid, @sequence, o.uid),
                         0,
                         0
                  from sysindexes i,
                       sysobjects o
                  where (o.sysstat & 15) = 3
                    and i.id             = o.id
                    and i.indid          > 0
                    and i.indid          < 254
                    and index_col (o.name, i.indid, @sequence, o.uid) IS NOT NULL

               select @sequence = @sequence + 1
            end

         create unique clustered index UC on #ickeys (id, indid, keycol)

         update #ickeys
         set coloffset = c.offset,
             colstatus = (c.status & 8)
         from syscolumns c,
              #ickeys k
         where c.id   = k.id
           and c.name = k.keycol

         delete #ic
         from #ic i1
         where 1 = (select count (i2.id)
                    from #ic i2
                    where i2.id = i1.id)

         if (exists (select *
                     from #ic))
            begin
               select @multicnt = ltrim (substring (convert (char (10), convert (money, count (distinct id)), 1), 1, 7))
               from #ic

               print '    THERE ARE %1! USER TABLES WITH MULTIPLE INDEXES.',
                     @multicnt

               print ' '

               select @sequence = 1

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

                     select @sequence = @sequence + 1
                  end

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

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

               while (@sequence < @maxkeys)
                  begin
                     update #ic
                     set keytext = substring (c.keytext + ' ' + k.keycol + ',',
                                              1,
                                              75)
                     from #ic c,
                          #ickeys k
                     where k.id         = c.id
                       and k.indid      = c.indid
                       and k.keyseq     = @sequence
                       and k.coloffset  > 0
                       and k.colstatus <> 8

                     update #ic
                     set keyflag = 1,
                         keytext = substring (c.keytext + ' [' + k.keycol + '],',
                                              1,
                                              75)
                     from #ic c,
                          #ickeys k
                     where k.id         = c.id
                       and k.indid      = c.indid
                       and k.keyseq     = @sequence
                       and k.coloffset  < 0
                       and k.colstatus <> 8

                     update #ic
                     set keyflag = 1,
                         keytext = substring (c.keytext + ' (' + k.keycol + '),',
                                              1,
                                              75)
                     from #ic c,
                          #ickeys k
                     where k.id        = c.id
                       and k.indid     = c.indid
                       and k.keyseq    = @sequence
                       and k.colstatus = 8

                     select @sequence = @sequence + 1
                  end

               update #ic
               set keytext = substring (keytext, 2, char_length (keytext) - 2)

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

               update #ic
               set name = printname

               insert into #ic
                  select distinct name, ' ', uid, id, 0, 0, 0, 0, 0, 0, ' ', ' '
                  from #ic

               -- THE NEXT THREE STATEMENTS DELETE THE FIRST "BLANK ROW" FROM
               -- THE PREVIOUS INSERT.  IT'S TRICKY BECAUSE THE OWNER MAY OR
               -- MAY NOT BE APPENDED.

               select @minname = min (object_name (id))
               from #ic

               select @minuid = user_id (min (user_name (uid)))
               from #ic
               where object_name (id)= @minname

               delete from #ic
               where printname  = ' '
                 and uid        = @minuid
                 and name      in (@minname,
                                   @minname + ' (' + user_name (@minuid) + ')')

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

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

               select @maxkeylen = max (char_length (keytext)),
                      @maxtablen = max (char_length (printname)),
                      @maxtxtlen = max (char_length (typetxt))
               from #ic

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

               if ((@maxtablen > 15)  and  (@keyflag = 1))
                  begin
                     select 'TABLE'                       = substring (printname, 1, 23),
                            'INDEX TYPE'                  = substring (typetxt, 1, 25),
                            '(NULLABLE) / [VAR LEN] KEYS' = substring (keytext, 1, 27)
                     from #ic
                     order by name, indid
                  end
               else if ((@maxtablen > 15)  and  (@maxtxtlen > 20))
                  begin
                     select 'TABLE'      = substring (printname, 1, 23),
                            'INDEX TYPE' = substring (typetxt, 1, 25),
                            KEYS         = substring (keytext, 1, 27)
                     from #ic
                     order by name, indid
                  end
               else if (@maxtablen > 15)
                  begin
                     select 'TABLE'      = substring (printname, 1, 23),
                            'INDEX TYPE' = substring (typetxt, 1, 20),
                            KEYS         = substring (keytext, 1, 32)
                     from #ic
                     order by name, indid
                  end
               else if ((@maxtxtlen > 20)  and  (@maxkeylen < 28)  and  (@keyflag = 1))
                  begin
                     select 'TABLE'                       = substring (printname, 1, 15),
                            'INDEX TYPE'                  = substring (typetxt, 1, 33),
                            '(NULLABLE) / [VAR LEN] KEYS' = substring (keytext, 1, 27)
                     from #ic
                     order by name, indid
                  end
               else if ((@maxtxtlen > 20)  and  (@maxkeylen < 27)  and  (@keyflag = 0))
                  begin
                     select 'TABLE'      = substring (printname, 1, 15),
                            'INDEX TYPE' = substring (typetxt, 1, 34),
                            KEYS         = substring (keytext, 1, 26)
                     from #ic
                     order by name, indid
                  end
               else if ((@maxtxtlen > 20)  and  (@keyflag = 1))
                  begin
                     select 'TABLE'                       = substring (printname, 1, 15),
                            'INDEX TYPE'                  = substring (typetxt, 1, 25),
                            '(NULLABLE) / [VAR LEN] KEYS' = substring (keytext, 1, 35)
                     from #ic
                     order by name, indid
                        end
               else if ((@maxtxtlen > 20)  and  (@keyflag = 0))
                  begin
                     select 'TABLE'      = substring (printname, 1, 15),
                            'INDEX TYPE' = substring (typetxt, 1, 25),
                            KEYS         = substring (keytext, 1, 35)
                     from #ic
                     order by name, indid
                  end
               else if (@keyflag = 1)
                  begin
                     select 'TABLE'                       = substring (printname, 1, 15),
                            'INDEX TYPE'                  = substring (typetxt, 1, 20),
                            '(NULLABLE) / [VAR LEN] KEYS' = substring (keytext, 1, 40)
                     from #ic
                     order by name, indid
                  end
               else
                  begin
                     select 'TABLE'      = substring (printname, 1, 15),
                            'INDEX TYPE' = substring (typetxt, 1, 20),
                            KEYS         = substring (keytext, 1, 40)
                     from #ic
                     order by name, indid
                  end

               print ' '

               select id      = i1.id,
                      indid   = i1.indid,
                      keycol1 = i1.keycol,
                      keycol2 = convert (varchar (30), NULL)
               into #ic2keys
               from #ickeys i1
               where keyseq = 1

               update #ic2keys
               set keycol2 = #ickeys.keycol
               from #ic2keys,
                    #ickeys
               where #ickeys.id     = #ic2keys.id
                 and #ickeys.indid  = #ic2keys.indid
                 and #ickeys.keyseq = 2

               select distinct i.id, i.indid
               into #ic2
               from #ic2keys i
               where (exists (select i2.*
                              from #ic2keys i2
                              where i2.id       = i.id
                                and i2.indid   <> i.indid
                                and i2.keycol1  = i.keycol1
                                and (((i2.keycol2 IS NULL)  and  (i.keycol2 IS NULL))  or
                                     (i2.keycol2 = isnull (i.keycol2, i2.keycol2))     or
                                     (i.keycol2  = isnull (i2.keycol2, i.keycol2)))))

               if (exists (select *
                           from #ic2))
                  begin
                     select @overlapcnt = ltrim (substring (convert (char (10), convert (money, count (distinct id)), 1), 1, 7))
                     from #ic2

                     print '    THERE ARE %1! USER TABLES WITH DUPLICATE / OVERLAPPING INDEXES.',
                           @overlapcnt

                     print ' '

                     delete #ic
                     where not exists (select #ic2.id
                                       from #ic2
                                       where #ic2.id    = #ic.id
                                         and #ic2.indid = #ic.indid)

                     select @maxkeylen = max (char_length (keytext)),
                            @maxtablen = max (char_length (printname)),
                            @maxtxtlen = max (char_length (typetxt))
                     from #ic

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

                     insert into #ic
                        select distinct name, ' ', uid, id, 0, 0, 0, 0, 0, 0, ' ', ' '
                        from #ic

                     select @minname = min (name)
                     from #ic

                     select @minindid = min (indid)
                     from #ic
                     where name = @minname

                     delete from #ic
                     where printname = ' '
                       and name      = @minname
                       and indid     = @minindid

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

                     if ((@maxtablen > 15)  and  (@keyflag = 1))
                        begin
                           select 'TABLE'                       = substring (printname, 1, 23),
                                  'INDEX TYPE'                  = substring (typetxt, 1, 25),
                                  '(NULLABLE) / [VAR LEN] KEYS' = substring (keytext, 1, 27)
                           from #ic
                           order by name, indid
                        end
                     else if ((@maxtablen > 15)  and  (@maxtxtlen > 20))
                        begin
                           select 'TABLE'      = substring (printname, 1, 23),
                                  'INDEX TYPE' = substring (typetxt, 1, 25),
                                  KEYS         = substring (keytext, 1, 27)
                           from #ic
                           order by name, indid
                        end
                     else if (@maxtablen > 15)
                        begin
                           select 'TABLE'      = substring (printname, 1, 23),
                                  'INDEX TYPE' = substring (typetxt, 1, 20),
                                  KEYS         = substring (keytext, 1, 32)
                           from #ic
                           order by name, indid
                        end
                     else if ((@maxtxtlen > 20)  and  (@maxkeylen < 28)  and  (@keyflag = 1))
                        begin
                           select 'TABLE'                       = substring (printname, 1, 15),
                                  'INDEX TYPE'                  = substring (typetxt, 1, 33),
                                  '(NULLABLE) / [VAR LEN] KEYS' = substring (keytext, 1, 27)
                           from #ic
                           order by name, indid
                        end
                     else if ((@maxtxtlen > 20)  and  (@maxkeylen < 27)  and  (@keyflag = 0))
                        begin
                           select 'TABLE'      = substring (printname, 1, 15),
                                  'INDEX TYPE' = substring (typetxt, 1, 34),
                                  KEYS         = substring (keytext, 1, 26)
                           from #ic
                           order by name, indid
                        end
                     else if ((@maxtxtlen > 20)  and  (@keyflag = 1))
                        begin
                           select 'TABLE'                       = substring (printname, 1, 15),
                                  'INDEX TYPE'                  = substring (typetxt, 1, 25),
                                  '(NULLABLE) / [VAR LEN] KEYS' = substring (keytext, 1, 35)
                           from #ic
                           order by name, indid
                        end
                     else if ((@maxtxtlen > 20)  and  (@keyflag = 0))
                        begin
                           select 'TABLE'      = substring (printname, 1, 15),
                                  'INDEX TYPE' = substring (typetxt, 1, 25),
                                  KEYS         = substring (keytext, 1, 35)
                           from #ic
                           order by name, indid
                        end
                     else if (@keyflag = 1)
                        begin
                           select 'TABLE'                       = substring (printname, 1, 15),
                                  'INDEX TYPE'                  = substring (typetxt, 1, 20),
                                  '(NULLABLE) / [VAR LEN] KEYS' = substring (keytext, 1, 40)
                           from #ic
                           order by name, indid
                        end
                     else
                        begin
                           select 'TABLE'      = substring (printname, 1, 15),
                                  'INDEX TYPE' = substring (typetxt, 1, 20),
                                  KEYS         = substring (keytext, 1, 40)
                           from #ic
                           order by name, indid
                        end
                  end
               else
                  begin
                     print '    THERE ARE NO DUPLICATE OR OVERLAPING USER INDEXES.'
                  end
            end
         else
            begin
               print '    THERE ARE NO TABLES WITH MULTIPLE INDEXES.'
            end

         print ' '

         if (exists (select i.id
                     from sysindexes i,
                          sysobjects o
                     where i.indid          > 0
                       and i.indid          < 254
                       and (i.status2 & 1)  = 1
                       and o.id             = i.id
                       and (o.sysstat & 15) = 3))
            begin
               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)

               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
                  from sysindexes i,
                       sysobjects o
                  where (o.sysstat & 15)   = 3
                    and i.id               = o.id
                    and i.indid            = 1
                    and (i.status & 2048) <> 2048
                    and (i.status2 & 1)   <> 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
                  from sysindexes i,
                       sysobjects o
                  where (o.sysstat & 15)   = 3
                    and i.id               = o.id
                    and i.indid            > 1
                    and i.indid            < 254
                    and (i.status & 2048) <> 2048
                    and (i.status2 & 1)   <> 1

               if (exists (select *
                           from #indexes))
                  begin
                     print '    THE FOLLOWING INDEXES ARE NEITHER PRIMARY NOR SUPPORT FOREIGN KEYS.'

                     print ' '

                     execute sp_indexes_format 0

                     execute sp_indexes_printall
                  end
               else
                  begin
                     print '    ALL INDEXES SUPPORT PRIMARY KEYS OR FOREIGN KEYS.'

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

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

         print ' '
      end
go
sp_addcomment sp_indexchoices, 13, 'Lists user tables with no clustered index, multiple indexes, or overlapping indexes'
go
sp_procxmode sp_indexchoices, anymode
go

create procedure sp_indexnames (@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_indexnames> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @exclude1  varchar (7),
           @exclude2  varchar (7),
           @exclude3  varchar (7),
           @exclude4  varchar (7),
           @indexcnt  int,
           @name      varchar (30),
           @tablecnt  int,
           @tableid   int,
           @uid       smallint

   create table #indexnames
      (id         int            not null,
       indid      tinyint        not null,
       uid        int            not null,
       keylen     smallint       not null,
       keycnt     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
               select @exclude1 = 'sys%',
                      @exclude2 = 'pb%',
                      @exclude3 = 'sr[_]%'

               execute sp_excld_prnt  'TABLES', @exclude1, @exclude2, @exclude3, @exclude4

               insert into #indexnames
                  select i.id,
                         i.indid,
                         o.uid,
                         i.maxirow,
                         i.keycnt,
                         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 #indexnames
                  select i.id,
                         i.indid,
                         o.uid,
                         i.maxirow,
                         i.keycnt - 1,
                         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

               delete from #indexnames
               where name like @exclude1
                  or name like @exclude2
                  or name like @exclude3
                  or name like @exclude4

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

               update #indexnames
               set sort = name

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

               select 'TABLE'      = substring (name, 1, 24),
                      INDID        = indid,
                      'INDEX NAME' = substring (indname, 1, 30),
                      'KEY LEN'    = keylen,
                      SUBKEYS      = keycnt
               from #indexnames
               order by sort, indid
            end
         else
            begin
               print '    THERE ARE NO INDEXES ON USER TABLES.'

               print ' '
            end
      end
   else
      begin
         select @tablecnt = count (*)
         from sysobjects
         where name           like (@PREFIX  +  '%')
           and (sysstat & 15)   in (1, 3)

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

               print ' '
            end
         else
            begin
               select @indexcnt = count (distinct i.id)
               from sysindexes i,
                    sysobjects o
               where o.name           like (@PREFIX  +  '%')
                 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!%%>.',
                           @PREFIX

                     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 (@PREFIX  +  '%')
                       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),
                               'KEY LEN'    = convert (smallint, 0),
                               SUBKEYS      = convert (tinyint, 0)
                        where 1 = 2
                     union all
                        select indid, name, maxirow, convert (tinyint, keycnt)
                        from sysindexes
                        where id    = @tableid
                          and indid = 1
                     union all
                        select indid, name, maxirow, convert (tinyint, keycnt - 1)
                        from sysindexes
                        where id    = @tableid
                          and indid > 1
                          and indid < 254
                     order by INDID
                  end
               else
                  begin
                     insert into #indexnames
                        select i.id, i.indid, o.uid, i.maxirow, i.keycnt, o.name, ' ', i.name
                        from sysindexes i,
                             sysobjects o
                        where o.name           like (@PREFIX  +  '%')
                          and (o.sysstat & 15)   in (1, 3)
                          and i.id                = o.id
                          and i.indid             = 1

                     insert into #indexnames
                        select i.id, i.indid, o.uid, i.maxirow, i.keycnt - 1, o.name, ' ', i.name
                        from sysindexes i,
                             sysobjects o
                        where o.name           like (@PREFIX  +  '%')
                          and (o.sysstat & 15)   in (1, 3)
                          and i.id                = o.id
                          and i.indid             > 1
                          and i.indid             < 254

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

                     update #indexnames
                     set sort = name

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

                     select 'TABLE'      = substring (name, 1, 24),
                            INDID        = indid,
                            'INDEX NAME' = substring (indname, 1, 30),
                            'KEY LEN'    = keylen,
                            SUBKEYS      = keycnt
                     from #indexnames
                     order by sort, indid
                  end
            end
      end
go
sp_addcomment sp_indexnames, 13, 'Lists index names for tables like PREFIX% or (default) all user tables'
go
sp_procxmode sp_indexnames, anymode
go

create procedure sp_readonly 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_readonly> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @bitoff      bit,
           @biton       bit,
           @maxtabname  tinyint

   create table #readonly
      (tabid       int           not null,
       uid         smallint      not null,
       tabname     varchar (63)  not null,
       indexes     tinyint       not null,
       dependents  tinyint       not null,
       fkeys       tinyint       not null,
       dpages      int           not null)

   select @bitoff = convert (bit, 0)

   select @biton  = ~(@bitoff)

   insert into #readonly
      select distinct o.id,
                      o.uid,
                      o.name,
                      convert (tinyint, count (i.indid)),
                      0,
                      0,
                      0
      from sysindexes i,
           sysobjects o
      where (o.sysstat & 15) = 3
        and i.id             = o.id
        and i.indid          > 0
        and i.indid          < 255
        and not exists (select d.*
                        from sysdepends d
                        where d.depid     = o.id
                          and d.resultobj = @biton)
      group by o.id
      having (o.sysstat & 15) = 3
         and i.id             = o.id
         and i.indid          > 0
         and i.indid          < 255
         and not exists (select d.*
                         from sysdepends d
                         where d.depid     = o.id
                           and d.resultobj = @biton)

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

   update #readonly
   set dependents = (select count (d.id)
                     from sysdepends d
                     where d.depid = ro.tabid),
       fkeys      = (select count (r.tableid)
                     from sysreferences r
                     where r.pmrydbid = db_id ()
                       and r.reftabid = ro.tabid),
       dpages     = (select data_pgs (i.id, i.doampg)
                     from sysindexes i
                     where i.id    = ro.tabid
                       and i.indid < 2)
   from #readonly ro

   if (exists (select *
               from #readonly))
      begin
         select @maxtabname = max (char_length (tabname))
         from #readonly

         if (@maxtabname < 19)
            begin
               select 'USER TABLE' = substring (tabname, 1, 18),
                      INDEXES      = indexes,
                      DEPENDENTS   = dependents,
                      'FK TABLES'  = fkeys,
                      'DATA PAGES' = substring (convert (char (13),
                                                         convert (money,
                                                                  dpages),
                                                         1),
                                                1,
                                                10)
               from #readonly
               order by tabname
            end
         else if (@maxtabname < 31)
            begin
               select 'USER TABLE' = substring (tabname, 1, 30),
                      INDEXES      = indexes,
                      DEPENDENTS   = dependents,
                      'FK TABLES'  = fkeys,
                      'DATA PAGES' = substring (convert (char (13),
                                                         convert (money,
                                                                  dpages),
                                                         1),
                                                1,
                                                10)
               from #readonly
               order by tabname
            end
         else
            begin
               select 'USER TABLE' = substring (tabname, 1, 37),
                      INDEXES      = indexes,
                      DEPENDENTS   = dependents,
                      'FK TABLES'  = fkeys,
                      'DATA PAGES' = substring (convert (char (13),
                                                         convert (money,
                                                                  dpages),
                                                         1),
                                                1,
                                                10)
               from #readonly
               order by tabname
            end
      end
   else
      begin
         print '    THERE ARE NO READ-ONLY USER TABLES.'

         print ' '
      end
go
sp_addcomment sp_readonly, 13, 'Lists all user tables which are read only'
go
sp_procxmode sp_readonly, anymode
go

dump transaction sybsystemprocs with truncate_only
go
