use sybsystemprocs
go

drop procedure sp_tgr                     -- In 11031A release
go
drop procedure sp_ts                      -- In 11031A release
go
drop procedure sp_textdroplogin_generate  -- In 11031C release
go
drop procedure sp_textdroplogin_generate1 -- In 1151A release
go
drop procedure sp_textdroplogin_generate2 -- In 1151A release
go
drop procedure sp_textgroupsright_print   -- In 1151A release
go
drop procedure sp_textselectivity_print
go
drop procedure sp_textdroplogin           -- In 1151A release
go
drop procedure sp_textgroupsright         -- In 1151A release
go
drop procedure sp_textreconstrain         -- In 1151A release
go
drop procedure sp_textrecluster           -- In 11031C release
go
drop procedure sp_textreindex
go
drop procedure sp_textselectivity
go
drop procedure sp_textwhoiswhere        -- In 11031B release
go

------------ Temporary tables needed for creation of sp_text "subroutines" ---

create table #textdellgn
   (text  varchar (60))
go

create table #tgr
   (db        varchar (30)  not null,
    suid      smallint      not null,
    username  varchar (30)  not null,
    groupname varchar (30)  not null)
go

create table #selectivity
   (id            int               not null,
    indid         tinyint           not null,
    uid           smallint          not null,
    keycnt        tinyint           not null,
    keytext       char (33)         not null,
    selectivity   double precision  not null,
    dselectivity  int               not null,
    prntext       varchar (62)      not null)
go

set quoted_identifier on
go

create proc sp_textselectivity_print as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

   update #selectivity
   set keycnt = keycnt - 1
   where indid > 1

   update #selectivity
   set dselectivity = selectivity
   where dselectivity = 0
     and keycnt       = 1

   update #selectivity
   set keytext = substring (index_col (object_name (id), indid, 1, uid)
                            + ', ' + index_col (object_name (id), indid, 2, uid)
                            + ', ' + index_col (object_name (id), indid, 3, uid)
                            + ', ' + index_col (object_name (id), indid, 4, uid)
                            + ', ' + index_col (object_name (id), indid, 5, uid)
                            + ', ' + index_col (object_name (id), indid, 6, uid)
                            + ', ' + index_col (object_name (id), indid, 7, uid)
                            + ', ' + index_col (object_name (id), indid, 8, uid)
                            + ', ' + index_col (object_name (id), indid, 9, uid)
                            + ', ' + index_col (object_name (id), indid, 10, uid)
                            + ', ' + index_col (object_name (id), indid, 11, uid)
                            + ', ' + index_col (object_name (id), indid, 12, uid)
                            + ', ' + index_col (object_name (id), indid, 13, uid)
                            + ', ' + index_col (object_name (id), indid, 14, uid)
                            + ', ' + index_col (object_name (id), indid, 15, uid)
                            + ', ' + index_col (object_name (id), indid, 16, uid),
                            1,

                            33)

   update #selectivity
   set keytext = substring (keytext, 1, charindex (', ,', keytext) - 1)
   where charindex (', ,', keytext) > 1

   update #selectivity
   set prntext = object_name (id)

   update #selectivity
   set prntext = prntext + ' (' + user_name (uid) + ')'
   where uid <> 1

   print '                                                           INDEX  DISTRIBUTION'

   select 'TABLE'       = substring (prntext, 1, 23),
          'KEYS'        = keytext,
          'SEL...'      = substring (convert (char (8),
                                              convert (money,
                                                       selectivity),
                                              1),
                                     1,
                                     7),
          'SELECTIVITY' = substring (convert (char (14),
                                              convert (money,
                                                       dselectivity),
                                              1),
                                     1,
                                     11)
   from #selectivity
   order by prntext, indid
go

drop table #textdellgn
go
drop table #selectivity
go
drop table #tgr
go

create procedure sp_textreindex (@FILLFACTOR  tinyint = NULL) as

   -- type1/type2/seq meanings
   --    -1    -1   1 = set quoted_identifier on
   --     0     0   1 = use <database>
   --     1     1   1 = sp_freespace
   --     2     2   1 = alter table ...
   --     2     2   2 =    drop (referential) constraint ...
   --     3     3   1 = drop index ...
   --     4     4   1 = alter table ...
   --     4     4   2 =    drop (index) constraint ...
   --     5     5   1 = create clustered index ...
   --     5     5   3 =    <columns>
   --     5     5   4 =    with <options>
   --     5     5   5 =    on <segment>
   --     5     6   1 = alter table ...
   --     5     6   2 =    add (clustered index) constraint ...
   --     5     6   3 =    <columns>
   --     5     6   4 =    with <options>
   --     5     6   5 =    on <segment>
   --     5     7   1 = create nonclustered index ...
   --     5     7   3 =    <columns>
   --     5     7   4 =    with <options>
   --     5     7   5 =    on <segment>
   --     5     8   1 = alter table ...
   --     5     8   2 =    add (nonclustered index) constraint ...
   --     5     8   3 =    <columns>
   --     5     8   4 =    with <options>
   --     5     8   5 =    on <segment>
   --     5     9   1 = sp_bindcache ...
   --     5    10   1 = sp_recompile ...
   --    11    11   1 = alter table ...
   --    11    11   2 =    add (referential) constraint ...
   --    11    11   3 =    foreign key ...
   --    11    11   4 =    references ...
   --    12    12   1 = sp_freespace
   --     N     N   6 = go

   -- THE type1 & type2 COLUMNS ALLOW FOR A HIERARCHICAL SEQUENCE OF OPERATIONS.
   -- THE type1 = 5 OPERATIONS SUPPORT DOING SEVERAL "CREATE" OPERATIONS ON A
   -- PER TABLE BASIS.

   set ansinull                      off              -- Non-ANSI
   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_textreindex> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @errorchar  varchar (4),
           @ff         varchar (3),     -- fillfactor character string
           @maxnameid  int,             -- id of max table name
           @minnameid  int,             -- id of min table name
           @sequence   int,
           @sqlstatus  int,
           @t0         tinyint,
           @t1         tinyint,
           @t2         tinyint,
           @t3         tinyint,
           @t4         tinyint,
           @t5         tinyint,
           @t6         tinyint,
           @t7         tinyint,
           @t8         tinyint,
           @t9         tinyint,
           @t10        tinyint,
           @t11        tinyint,
           @t12        tinyint,
           @txt        varchar (127),
           @username   varchar (30)

   create table #textreindex
      (type1   smallint       default 0  not null,
       type2   smallint       default 0  not null,
       seq     int                       not null,
       id1     int                       not null,         -- tableid
       indid   tinyint        default 1  not null,
       id2     int            default 0  not null,         -- reftabid
       id3     int            default 0  not null,         -- constrid
       id4     int            default 0  not null,         -- pmrydbid / indid
       txt     varchar (255))

   declare text_cursor cursor for
      select distinct substring (txt, 1, 127)
      from #textreindex
      order by type1,
               object_name (id1),
               type2,
               indid,
               object_name (id2),
               object_name (id3),
               id4,
               seq
   for read only

   select @t0  = 0,
          @t1  = 1,
          @t2  = 2,
          @t3  = 3,
          @t4  = 4,
          @t5  = 5,
          @t6  = 6,
          @t7  = 7,
          @t8  = 8,
          @t9  = 9,
          @t10 = 10,
          @t11 = 11,
          @t12 = 12

   if ((@FILLFACTOR < 0)  or  (@FILLFACTOR > 100))
      begin
         print '    THE FILLFACTOR PARAMETER <%1!> IS INVALID AND WILL BE IGNORED.',
               @FILLFACTOR

         print ' '

         select @ff = NULL
      end
   else
      begin
         select @ff = convert (varchar (3), @FILLFACTOR)
      end

   -- ARE THERE ANY PRIMARY/FOREIGN KEYS IN OTHER DATABASES?

   if (exists (select *
               from sysreferences
               where pmrydbname <> db_name ()))
      begin
         print '    WARNING: THERE ARE PRIMARY KEYS IN OTHER DATABASES!'

         print ' '

         print '    PRIMARY KEY OWNERS IN OTHER DATABASES ARE UNKNOWN,'

         print ' '

         print '    OWNER dbo (''..'') IS ASSUMED.'

         print ' '
      end

   if (exists (select *
               from sysreferences
               where frgndbname <> db_name ()))
      begin
         print '    WARNING: THERE ARE FOREIGN KEYS IN OTHER DATABASES!'

         print ' '

         print '    USE sp_refkeysx AND MODIFY THE REST OF THIS OUTPUT'

         print ' '

         print '    AND/OR DROP FOREIGN KEY CONSTAINTS IN OTHER DATABASES.'

         print ' '
      end

   -- ARE ALL REFERENTIAL CONSTRAINTS ON TABLES WITH THE SAME OWNER?

   if (exists (select r.constrid
               from sysreferences r,
                    sysobjects ofk,
                    sysobjects opk
               where ((opk.uid = user_id ())  or  (ofk.uid = user_id ()))
                 and opk.id   = r.reftabid
                 and ofk.id   = r.tableid
                 and ofk.uid <> opk.uid))
      begin
         print '    WARNING: THERE ARE REFERENTIAL CONSTRAINTS WITH DIFFERENT OWNERS!'

         print ' '

         select 'FOREIGN KEY TABLE' = substring (user_name (ofk.uid)  +  '.'  +  ofk.name, 1, 38),
                'PRIMARY KEY TABLE' = substring (user_name (opk.uid)  +  '.'  +  opk.name, 1, 38)
         from sysreferences r,
              sysobjects ofk,
              sysobjects opk
         where ((opk.uid = user_id ())  or  (ofk.uid = user_id ()))
           and opk.id   = r.reftabid
           and ofk.id   = r.tableid
           and ofk.uid <> opk.uid
         order by 'FOREIGN KEY TABLE', 'PRIMARY KEY TABLE'
      end

   -- PROLOG TEXT

   insert into #textreindex (type2, seq, id1, indid, txt)
      values (@t0, 1, 0, 0, 'use ' + db_name ())

   insert into #textreindex (type2, seq, id1, indid, txt)
      values (@t1, 1, 0, 0, 'sp_freespace')

   -- GENERATE COLUMN TEXT FOR CREATING INDEXES/CONSTRAINTS (type1 = 5)

   -- CLUSTERED INDEXES (type2=5, seq=1)

   insert into #textreindex (type2, seq, id1, txt)
      select @t5, 1, i.id,
             'create clustered index '  +  i.name
      from sysindexes i,
           sysobjects o
      where o.uid              = user_id ()
        and (o.sysstat & 15)   = 3
        and o.id               = i.id
        and i.indid            = 1
        and (i.status & 2)    <> 2
        and (i.status & 16)    = 16
        and (i.status & 2048) <> 2048
        and (i.status2 & 2)   <> 2

   insert into #textreindex (type2, seq, id1, txt)
      select @t5, 1, i.id, 'create unique clustered index '  +  i.name
      from sysindexes i,
           sysobjects o
      where o.uid              = user_id ()
        and (o.sysstat & 15)   = 3
        and o.id               = i.id
        and i.indid            = 1
        and (i.status & 2)     = 2
        and (i.status & 16)    = 16
        and (i.status & 2048) <> 2048
        and (i.status2 & 2)   <> 2

   -- NONCLUSTERED INDEXES (type2=7, seq=1)

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t7, 1, i.id, i.indid,
             'create nonclustered index '  +  i.name
      from sysindexes i,
           sysobjects o
      where o.uid              = user_id ()
        and (o.sysstat & 15)   = 3
        and o.id               = i.id
        and i.indid            > 1
        and i.indid            < 254
        and (i.status & 2)    <> 2
        and (i.status & 16)   <> 16
        and (i.status & 2048) <> 2048
        and (i.status2 & 2)   <> 2

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t7, 1, i.id, i.indid, 'create unique nonclustered index '  +  i.name
      from sysindexes i,
           sysobjects o
      where o.uid              = user_id ()
        and (o.sysstat & 15)   = 3
        and o.id               = i.id
        and i.indid            > 1
        and i.indid            < 254
        and (i.status & 2)     = 2
        and (i.status & 16)   <> 16
        and (i.status & 2048) <> 2048
        and (i.status2 & 2)   <> 2

   -- COLUMN TEXT (type2=5 or 7; seq=3)

   insert into #textreindex (type2, seq, id1, indid, txt)
      select type2, 3, id1, indid, '   on '  +  object_name (id1)  +  ' ('
      from #textreindex
      where type2                           in (@t5, @t7)
        and valid_name (object_name (id1)) > 0

   insert into #textreindex (type2, seq, id1, indid, txt)
      select type2, 3, id1, indid, '   on "'  +  object_name (id1)  +  '" ('
      from #textreindex
      where type2                           in (@t5, @t7)
        and valid_name (object_name (id1))  = 0

   -- CLUSTERED CONSTRAINTS (type2=6, seq=1 or 2)

   insert into #textreindex (type2, seq, id1, txt)
      select @t6, 1, i.id, 'alter table '  +  o.name
      from sysindexes i,
           sysobjects o
      where o.uid               = user_id ()
        and (o.sysstat & 15)    = 3
        and o.id                = i.id
        and i.indid             = 1
        and (i.status & 16)     = 16
        and (i.status2 & 2)     = 2
        and valid_name (o.name) > 0

   insert into #textreindex (type2, seq, id1, txt)
      select @t6, 1, i.id, 'alter table "'  +  o.name  +  '"'
      from sysindexes i,
           sysobjects o
      where o.uid               = user_id ()
        and (o.sysstat & 15)    = 3
        and o.id                = i.id
        and i.indid             = 1
        and (i.status & 16)     = 16
        and (i.status2 & 2)     = 2
        and valid_name (o.name) = 0

   insert into #textreindex (type2, seq, id1, txt)
      select @t6, 2, t.id1,
             '   add constraint '  +  i.name  +  ' primary key clustered'
      from sysindexes i,
           #textreindex t
      where t.type2              = @t6
        and t.seq               = 1
        and t.id1               = i.id
        and i.indid             = 1
        and (i.status & 2048)   = 2048
        and valid_name (i.name) > 0

   insert into #textreindex (type2, seq, id1, txt)
      select @t6, 2, t.id1,
             '   add constraint "'  +  i.name  +  '" primary key clustered'
      from sysindexes i,
           #textreindex t
      where t.type2              = @t6
        and t.seq               = 1
        and t.id1               = i.id
        and i.indid             = 1
        and (i.status & 2048)   = 2048
        and valid_name (i.name) = 0

   insert into #textreindex (type2, seq, id1, txt)
      select @t6, 2, t.id1,
             '   add constraint '  +  i.name  +  ' unique clustered'
      from sysindexes i,
           #textreindex t
      where t.type2               = @t6
        and t.seq                = 1
        and t.id1                = i.id
        and i.indid              = 1
        and (i.status & 2)       = 2
        and (i.status & 2048)   <> 2048
        and valid_name (i.name)  > 0

   insert into #textreindex (type2, seq, id1, txt)
      select @t6, 2, t.id1,
             '   add constraint "'  +  i.name  +  '" unique clustered'
      from sysindexes i,
           #textreindex t
      where t.type2               = @t6
        and t.seq                = 1
        and t.id1                = i.id
        and i.indid              = 1
        and (i.status & 2)       = 2
        and (i.status & 2048)   <> 2048
        and valid_name (i.name)  = 0

   -- NONCLUSTERED CONSTRAINTS (type2=8, seq=1 or 2)

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t8, 1, i.id, i.indid, 'alter table '  +  o.name
      from sysindexes i,
           sysobjects o
      where o.uid                = user_id ()
        and (o.sysstat & 15)     = 3
        and valid_name (o.name)  > 0
        and i.id                 = o.id
        and i.indid              > 1
        and (i.status & 16)     <> 16
        and (i.status2 & 2)      = 2

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t8, 1, i.id, i.indid, 'alter table "'  +  o.name  +  '"'
      from sysindexes i,
           sysobjects o
      where o.uid                = user_id ()
        and (o.sysstat & 15)     = 3
        and valid_name (o.name)  = 0
        and i.id                 = o.id
        and i.indid              > 1
        and (i.status & 16)     <> 16
        and (i.status2 & 2)      = 2

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t8, 2, t.id1, t.indid,
             '   add constraint '  +  i.name  +  ' primary key nonclustered'
      from sysindexes i,
           #textreindex t
      where t.type2              = @t8
        and t.seq               = 1
        and t.id1               = i.id
        and t.indid             = i.indid
        and (i.status & 2048)   = 2048
        and valid_name (i.name) > 0

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t8, 2, t.id1, t.indid,
             '   add constraint "'  +  i.name  +  '" primary key nonclustered'
      from sysindexes i,
           #textreindex t
      where t.type2              = @t8
        and t.seq               = 1
        and t.id1               = i.id
        and t.indid             = i.indid
        and (i.status & 2048)   = 2048
        and valid_name (i.name) = 0

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t8, 2, t.id1, t.indid,
             '   add constraint '  +  i.name  +  ' unique nonclustered'
      from sysindexes i,
           #textreindex t
      where t.type2               = @t8
        and t.seq                = 1
        and t.id1                = i.id
        and t.indid              = i.indid
        and (i.status & 2)       = 2
        and (i.status & 2048)   <> 2048
        and valid_name (i.name)  > 0

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t8, 2, t.id1, t.indid,
             '   add constraint "'  +  i.name  +  '" unique nonclustered'
      from sysindexes i,
           #textreindex t
      where t.type2               = @t8
        and t.seq                = 1
        and t.id1                = i.id
        and t.indid              = i.indid
        and (i.status & 2)       = 2
        and (i.status & 2048)   <> 2048
        and valid_name (i.name)  = 0

   -- TEXT OF KEYS (seq=3)

   insert into #textreindex (type2, seq, id1, indid, txt)
      select type2, 3, id1, indid, '      ('
      from #textreindex
      where type2 in (@t6, @t8)
        and seq   = 1

   select @sequence = 1

   while (@sequence < 17)
      begin
         update #textreindex
         set txt = t.txt
                   +  ' '
                   +  index_col (object_name (i.id), i.indid, @sequence)
                   +  ','
         from sysindexes i,
              #textreindex t
         where t.type2   in (@t5, @t6, @t7, @t8)
           and t.seq     = 3
           and t.id1     = i.id
           and t.indid   = i.indid
           and valid_name (index_col (object_name (i.id), i.indid, @sequence)) > 0
           and (((i.indid = 1)  and  (i.keycnt > @sequence))  or
                ((i.indid > 1)  and  (i.keycnt > @sequence + 1)))

         update #textreindex
         set txt = t.txt
                   +  ' "'
                   +  index_col (object_name (i.id), i.indid, @sequence)
                   +  '",'
         from sysindexes i,
              #textreindex t
         where t.type2   in (@t5, @t6, @t7, @t8)
           and t.seq     = 3
           and t.id1     = i.id
           and t.indid   = i.indid
           and valid_name (index_col (object_name (i.id), i.indid, @sequence)) = 0
           and (((i.indid = 1)  and  (i.keycnt > @sequence))  or
                ((i.indid > 1)  and  (i.keycnt > @sequence + 1)))

         update #textreindex
         set txt = t.txt
                   +  ' '
                   +  index_col (object_name (i.id), i.indid, @sequence)
                   +  ')'
         from sysindexes i,
              #textreindex t
         where t.type2   in (@t5, @t6, @t7, @t8)
           and t.seq     = 3
           and t.id1     = i.id
           and t.indid   = i.indid
           and valid_name (index_col (object_name (i.id), i.indid, @sequence)) > 0
           and (((i.indid = 1)  and  (i.keycnt = @sequence))  or
                ((i.indid > 1)  and  (i.keycnt = @sequence + 1)))

         update #textreindex
         set txt = t.txt
                   +  ' "'
                   +  index_col (object_name (i.id), i.indid, @sequence)
                   +  '")'
         from sysindexes i,
              #textreindex t
         where t.type2   in (@t5, @t6, @t7, @t8)
           and t.seq     = 3
           and t.id1     = i.id
           and t.indid   = i.indid
           and valid_name (index_col (object_name (i.id), i.indid, @sequence)) = 0
           and (((i.indid = 1)  and  (i.keycnt = @sequence))  or
                ((i.indid > 1)  and  (i.keycnt = @sequence + 1)))

         select @sequence = @sequence + 1
      end

   update #textreindex
   set txt = stuff (txt, charindex ('( ', txt), 2, '(')
   where type2                  in (@t5, @t7)
     and charindex ('( ', txt)  > 0

   update #textreindex
   set txt = '      ('  +  substring (txt, charindex ('( ', txt) + 2, 255)
   where type2                  in (@t6, @t8)
     and charindex ('( ', txt)  > 0

   --- CLUSTERED INDEX "WITH" TEXT (type2=5, seq=4)

   insert into #textreindex (type2, seq, id1, txt)
      select @t5, 4, i.id, '   with sorted_data'
      from sysindexes i,
           #textreindex t
      where t.type2           = @t5
        and t.seq            = 1
        and t.id1            = i.id
        and i.indid          = 1
        and t.indid          = 1
        and (i.status & 1)  <> 1
        and (i.status & 4)  <> 4
        and (i.status & 64) <> 64

   insert into #textreindex (type2, seq, id1, txt)
      select @t5, 4, i.id, '   with ignore_dup_key, sorted_data'
      from sysindexes i,
           #textreindex t
      where t.type2         = @t5
        and t.seq          = 1
        and t.id1          = i.id
        and i.indid        = 1
        and t.indid        = 1
        and (i.status & 1) = 1

   insert into #textreindex (type2, seq, id1, txt)
      select @t5, 4, i.id, '   with ignore_dup_row, sorted_data'
      from sysindexes i,
           #textreindex t
      where t.type2         = @t5
        and t.seq          = 1
        and t.id1          = i.id
        and i.indid        = 1
        and t.indid        = 1
        and (i.status & 4) = 4

   insert into #textreindex (type2, seq, id1, txt)
      select @t5, 4, i.id, '   with allow_dup_row, sorted_data'
      from sysindexes i,
           #textreindex t
      where t.type2          = @t5
        and t.seq           = 1
        and t.id1           = i.id
        and i.indid         = 1
        and t.indid         = 1
        and (i.status & 64) = 64

   --- NONCLUSTERED INDEX "WITH" TEXT (type2=7, seq=4)

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t7, 4, i.id, i.indid, '   with ignore_dup_key'
      from sysindexes i,
           #textreindex t
      where t.type2         = @t7
        and t.seq          = 1
        and t.id1          = i.id
        and i.indid        = t.indid
        and t.indid        > 1
        and (i.status & 1) = 1

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t7, 4, i.id, i.indid, '   with ignore_dup_row'
      from sysindexes i,
           #textreindex t
      where t.type2         = @t7
        and t.seq          = 1
        and t.id1          = i.id
        and i.indid        = t.indid
        and t.indid        > 1
        and (i.status & 4) = 4

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t7, 4, i.id, i.indid, '   with allow_dup_row'
      from sysindexes i,
           #textreindex t
      where t.type2          = @t7
        and t.seq           = 1
        and t.id1           = i.id
        and i.indid         = t.indid
        and t.indid         > 1
        and (i.status & 64) = 64

   -- "ON SEGMENT" TEXT (seq=5)

   insert into #textreindex
      select 0, t.type2, 5, t.id1, t.indid, t.id2, t.id3, t.id4, '   on '  +  s.name
      from sysindexes i,
           syssegments s,
           #textreindex t
      where t.type2    in (@t5, @t6, @t7, @t8)
        and t.seq      = 1
        and i.id       = t.id1
        and i.indid    = t.indid
        and s.segment  = i.segment

   update #textreindex
   set txt = stuff (txt, charindex ('default', txt), 7, '''default''')
   where charindex ('default', txt)  > 0
     and type2                       in (@t5, @t6, @t7, @t8)
     and seq                         = 5

   if (@ff IS NOT NULL)
      begin
         update #textreindex
         set txt = txt  +  ', fillfactor = '  +  @ff
         where type2 in (@t5, @t6, @t7, @t8)
           and seq  = 4

         insert into #textreindex
            select 0, t1.type2, 4, t1.id1, t1.indid, t1.id2, t1.id3, t1.id4,
                   '   with fillfactor = '  +  @ff
            from #textreindex t1
            where t1.type2 in (@t5, @t6, @t7, @t8)
              and (not exists (select t2.type2
                               from #textreindex t2
                               where t2.type2  = t1.type2
                                 and t2.id1   = t1.id1
                                 and t2.indid = t1.indid
                                 and t2.seq   = 4))
      end

   -- GENERATE COLUMN TEXT FOR CREATING REFERENTIAL CONSTRAINTS (type2=9)

   insert into #textreindex
      select 0, @t11, 3, r.tableid, 0, r.reftabid, r.constrid, r.pmrydbid,
             '      foreign key'
             +  space (char_length (object_name (r.reftabid)) + 1)  +  ' ('
      from sysobjects o,
           sysreferences r
      where r.frgndbid = db_id ()
        and r.tableid  = o.id
        and o.uid      = user_id ()

   update #textreindex
   set txt = t.txt
             +  col_name (tableid, fokey1)
             +  ', '  +  col_name (tableid, fokey2)
             +  ', '  +  col_name (tableid, fokey3)
             +  ', '  +  col_name (tableid, fokey4)
             +  ', '  +  col_name (tableid, fokey5)
             +  ', '  +  col_name (tableid, fokey6)
             +  ', '  +  col_name (tableid, fokey7)
             +  ', '  +  col_name (tableid, fokey8)
             +  ', '  +  col_name (tableid, fokey9)
             +  ', '  +  col_name (tableid, fokey10)
             +  ', '  +  col_name (tableid, fokey11)
             +  ', '  +  col_name (tableid, fokey12)
             +  ', '  +  col_name (tableid, fokey13)
             +  ', '  +  col_name (tableid, fokey14)
             +  ', '  +  col_name (tableid, fokey15)
             +  ', '  +  col_name (tableid, fokey16)
   from sysreferences r,
        #textreindex t
   where t.type2     = @t11
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3

   update #textreindex
   set txt = substring (txt, 1, charindex (', ,', txt) - 1)
   where type2                   = @t11
     and charindex (', ,', txt) > 1

   insert into #textreindex
      select 0, @t11, 4, id1, 0, id2, id3, id4,
             '      references  '  +  object_name (id2)  +  ' ('
      from #textreindex
      where type2                           = @t11
        and id4                            = db_id ()
        and valid_name (object_name (id2)) > 0

   insert into #textreindex
      select 0, @t11, 4, id1, 0, id2, id3, id4,
             '      references  "'  +  object_name (id2)  +  '" ('
      from #textreindex
      where type2                           = @t11
        and id4                            = db_id ()
        and valid_name (object_name (id2)) = 0

   insert into #textreindex
      select 0, @t11, 4, id1, 0, id2, id3, id4,
             '      references  '
             +  db_name (id4)
             +  '..'
             +  object_name (id2, id4)
             +  ' ('
      from #textreindex
      where type2                            = @t11
        and id4                            <> db_id ()
        and valid_name (object_name (id2))  > 0

   insert into #textreindex
      select 0, @t11, 4, id1, 0, id2, id3, id4,
             '      references  '
             +  db_name (id4)
             +  '.."'
             +  object_name (id2, id4)
             +  '" ('
      from #textreindex
      where type2                            = @t11
        and id4                            <> db_id ()
        and valid_name (object_name (id2))  = 0

   update #textreindex
   set txt = t.txt
             +  col_name (reftabid, refkey1, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey2, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey3, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey4, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey5, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey6, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey7, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey8, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey9, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey10, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey11, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey12, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey13, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey14, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey15, pmrydbid)
             +  ', '  +  col_name (reftabid, refkey16, pmrydbid)
   from sysreferences r,
        #textreindex t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type2     = @t11
     and t.seq      = 4

   update #textreindex
   set txt = substring (txt, 1, charindex (', ,', txt) - 1)
   where type2                   = @t11
     and seq                    = 4
     and charindex (', ,', txt) > 1

   update #textreindex
   set txt = txt  +  ')'
   where type2 = @t11

   insert into #textreindex
      select 0, @t11, 1, id1, indid, id2, id3, id4,
             'alter table '  +  object_name (id1)
      from #textreindex
      where type2                           = @t11
        and seq                            = 3
        and valid_name (object_name (id1)) > 0

   insert into #textreindex
      select 0, @t11, 1, id1, indid, id2, id3, id4,
             'alter table "'  +  object_name (id1)  +  '"'
      from #textreindex
      where type2                           = @t11
        and seq                            = 3
        and valid_name (object_name (id1)) = 0

   insert into #textreindex
      select 0, @t11, 2, id1, indid, id2, id3, id4,
             '   add constraint '  +  object_name (id3)
      from #textreindex
      where type2 = @t11
        and seq  = 1

   -- GENERATE PROLOG SQL FOR type2<5.

   insert into #textreindex
      select 0, @t2, 1, id1, indid, id2, id3, id4, txt
      from #textreindex
      where type2 = @t11
        and seq  = 1

   insert into #textreindex
      select 0, @t2, 2, id1, indid, id2, id3, id4, '   drop '  +  substring (txt, 8, 255)
      from #textreindex
      where type2 = @t11
        and seq  = 2

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t3, 1, i.id, i.indid,
             'drop index '  +  object_name (i.id)  +  '.'  +  i.name
      from sysindexes i,
           #textreindex t
      where t.type2  in (@t5, @t7)
        and t.seq    = 1
        and i.id     = t.id1
        and i.indid  = t.indid

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t4, 1, id1, indid, txt
      from #textreindex
      where type2 in (@t6, @t8)
        and seq   = 1

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t4, 2, id1, indid,
             '   drop '  +  substring (txt, 8, charindex ('primary key', txt) - 9)
      from #textreindex
      where type2                           in (@t6, @t8)
        and seq                             = 2
        and charindex ('primary key', txt)  > 0

   insert into #textreindex (type2, seq, id1, indid, txt)
      select @t4, 2, id1, indid,
             '   drop '  +  substring (txt, 8, charindex ('unique', txt) - 9)
      from #textreindex
      where type2                      in (@t6, @t8)
        and seq                        = 2
        and charindex ('unique', txt)  > 0

   -- GENERATE sp_bindcache TEXT (type2=9)

   insert into #textreindex (type1, type2, seq, id1, id4, txt)
      select 5, @t9, 1, a.object, i.indid,
             'sp_bindcache '''
             +  rtrim (a.char_value)
             +  ''', '
             +  db_name ()
             +  ', '
             +  object_name (a.object)
             +  ', '
             +  i.name
      from sysattributes a,
           sysindexes i,
           sysobjects o
      where a.class          = 3
        and a.attribute      = 0
        and a.object_type    = 'I'
        and i.id             = a.object
        and i.indid          = a.object_info1
        and i.indid          < 254
        and o.id             = i.id
        and o.uid            = user_id ()
        and (o.sysstat & 15) = 3

   -- GENERATE sp_recompile AND sp_freespace TEXT

   select @maxnameid = object_id (max (object_name (id1)))
   from #textreindex

   insert into #textreindex (type1, type2, seq, id1, txt)
      select 0, @t10, 1, id1, 'sp_recompile '  +  object_name (id1)
      from #textreindex
      where type2 in (@t5, @t6)
        and seq    = 1

   -- GENERATE THE FINAL SQL

   if (exists (select *
               from #textreindex
               where type2 in (@t5, @t6)))
      begin
         insert into #textreindex (type1, type2, seq, id1, txt)
            values (@t12, @t12, 1, @maxnameid, 'sp_freespace')

         -- SET type1 VALUES

         update #textreindex
         set type1 = type2

         update #textreindex
         set type1 = 5
         where type2 in (5, 6, 7, 8, 9, 10)

         if (exists (select *
                     from #textreindex
                     where type2 in (@t5, @t6, @t7, @t8)
                       and charindex ('"', txt) > 0))
            begin
               insert into #textreindex
                  values (0, -1, 1, 0, 0, 0, 0, 0, 'set quoted_identifier on')
            end

         select @minnameid = object_id (min (object_name (id1)))
         from #textreindex

         -- GENERATE SECTION COMMENT HEADERS

         insert into #textreindex (type2, seq, id1, txt)
            values (-2, 0, 0,
                    '---------------------- PROLOG ------------------------------------------------')

         insert into #textreindex (type1, seq, id1, txt)
            select usv.number, 0, @minnameid, usv.name
            from sybsystemprocs.dbo.usptvalues usv
            where usv.type = 'XRI'
              and (exists (select t.type1
                           from #textreindex t
                           where t.type1 = usv.number
                             and t.seq   = 1))

         -- GENERATE TABLE COMMENT HEADERS

         insert into #textreindex (type1, type2, seq, id1, txt)
            select 5,
                   min (type2),
                   -1,
                   id1,
                   replicate ('-', 22)
                   +  ' REINDEX '
                   +  object_name (id1)
                   +  ' '
                   +  replicate ('-', 46 - char_length (object_name (id1)))
            from #textreindex
            where type2 in (5, 6, 7, 8)
              and seq    = 1
            group by id1
            having type2 in (5, 6, 7, 8)
               and seq    = 1

         -- GENERATE ALL OF THE go STATEMENTS

         insert into #textreindex
            select type1, type2, 6, id1, indid, id2, id3, id4, 'go'
            from #textreindex
            where seq = 1

         -- GENERATE THE TEXT WITHOUT A LEADING BLANK VIA A CURSOR

         open text_cursor

         fetch text_cursor into @txt

         while (@@sqlstatus = 0)
            begin
               print @txt

               fetch text_cursor into @txt
            end

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

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

               return
            end

         close text_cursor

         deallocate cursor text_cursor
      end
   else
      begin
         select @username = user_name ()

         print ' '

         print '    THERE ARE NO INDEXES FOR TABLES OWNED BY %1!.',
               @username

         print ' '
      end
go
sp_addcomment sp_textreindex, 13, 'Generates SQL to reindex user tables with optional FILLFACTOR'
go
sp_procxmode sp_textreindex, anymode
go

create procedure sp_textselectivity as
   set ansinull                      on
   set string_rtruncation            on

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

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

         print ' '

         return 1
      end

   print ' '

   declare @errorchar  varchar (4),
           @sqlstatus  int,
           @txt        varchar (127)

   create table #textsel
      (id       int            not null,
       indid    smallint       not null,
       uid      smallint       not null,
       seq      smallint       not null,
       keycnt   tinyint        not null,
       txt      varchar (127)  null)

   declare text_cursor cursor for
      select txt
      from #textsel
      order by id, indid, seq
   for read only

   -- THE "KERNEL" WILL BE THE select LINE OF THE insert STATEMENTS.

   insert into #textsel
      select i.id, i.indid, o.uid, 2, i.keycnt,
             '   select ' + convert (varchar (10), i.id) + ', '
             + convert (varchar (3), i.indid) + ', '
             + convert (varchar (6), o.uid) + ', '
             + convert (varchar (3), i.keycnt)
             + ', '' '', isnull (avg (1.0 * count (' + index_col (object_name (i.id), i.indid, 1, o.uid) + ')), 0), 0, '' '''
      from sysindexes i,
           sysobjects o
      where i.indid           > 0
        and i.indid           < 254
        and i.id              = o.id
        and (o.sysstat & 15)  = 3

   -- CREATE THE REST OF THE ABOVE insert STATEMENTS.

   insert into #textsel
      select id, indid, uid, 1, keycnt, 'insert into #selectivity'
      from #textsel
      where seq = 2

   insert into #textsel
      select id, indid, uid, 3, keycnt, '   from ' + object_name (id)
      from #textsel
      where seq                               = 2
        and uid                               = 1
        and charindex (' ', object_name (id)) = 0

   insert into #textsel
      select id, indid, uid, 3, keycnt, '   from ' + user_name (uid) + '.' + object_name (id)
      from #textsel
      where seq                                = 2
        and uid                               <> 1
        and charindex (' ', object_name (id))  = 0

   insert into #textsel
      select id, indid, uid, 3, keycnt, '   from "' + object_name (id) + '"'
      from #textsel
      where seq                               = 2
        and uid                               = 1
        and charindex (' ', object_name (id)) > 0

   insert into #textsel
      select id, indid, uid, 3, keycnt, '   from ' + user_name (uid) + '."'  + object_name (id) + '"'
      from #textsel
      where seq                                = 2
        and uid                               <> 1
        and charindex (' ', object_name (id))  > 0

   insert into #textsel
      select id, indid, uid, 4, keycnt,
             '   group by ' + index_col (object_name (id), indid, 1, uid)
             + ', ' + index_col (object_name (id), indid, 2, uid)
             + ', ' + index_col (object_name (id), indid, 3, uid)
             + ', ' + index_col (object_name (id), indid, 4, uid)
             + ', ' + index_col (object_name (id), indid, 5, uid)
             + ', ' + index_col (object_name (id), indid, 6, uid)
             + ', ' + index_col (object_name (id), indid, 7, uid)
             + ', ' + index_col (object_name (id), indid, 8, uid)
             + ', ' + index_col (object_name (id), indid, 9, uid)
             + ', ' + index_col (object_name (id), indid, 10, uid)
             + ', ' + index_col (object_name (id), indid, 11, uid)
             + ', ' + index_col (object_name (id), indid, 12, uid)
             + ', ' + index_col (object_name (id), indid, 13, uid)
             + ', ' + index_col (object_name (id), indid, 14, uid)
             + ', ' + index_col (object_name (id), indid, 15, uid)
             + ', ' + index_col (object_name (id), indid, 16, uid)
      from #textsel
      where seq = 2

      update #textsel
      set txt = substring (txt, 1, charindex (', ,', txt) - 1)
      where seq                    = 4
        and charindex (', ,', txt) > 1

   -- CREATE THE SQL FOR COMPUTING DISTRIBUTION PAGE SELECTIVITY.

   insert into #textsel
      select s.id, s.indid, s.uid, s.seq + 1000, s.keycnt, s.txt
      from #textsel s,
           #textsel s2
      where s.id                    = s2.id
        and s.indid                 = s2.indid
        and s2.seq                  = 4
        and charindex (',', s2.txt) > 0

   update #textsel
   set txt = 'update #selectivity'
   where seq = 1001

   update #textsel
   set txt = 'set dselectivity = (select '
             +  substring (txt, charindex ('isnull', txt), 110)
   where seq = 1002

   update #textsel
   set txt = stuff (txt, charindex ('1.0', txt), 6, NULL)
   where seq = 1002

   update #textsel
   set txt = substring (txt, 1, 1 + charindex ('0)', txt))
   where seq = 1002

   update #textsel
   set txt = '                 '
             +  substring (txt, 1, 110)
   where seq = 1003

   update #textsel
   set txt = '                 '
             +  substring (txt, 1, charindex (',', txt) - 1)
             +  ')'
   where seq = 1004

   insert into #textsel
      select id, indid, uid, 1005, keycnt,
             'where id = ' +  convert (varchar (10), id)
             + '  and  indid = ' + convert (varchar (3), indid)
      from #textsel
      where seq = 1004

   -- ELIMINATE DISTRIBUTION PAGE SELECTIVITY SQL FOR UNIQUE INDEXES WITH 1 KEY.

   delete #textsel
   from sysindexes i
   where #textsel.seq    > 999
     and ((#textsel.indid = 1 and #textsel.keycnt = 1)  or
          (#textsel.indid > 1 and #textsel.keycnt = 2))
     and #textsel.id     = i.id
     and #textsel.indid  = i.indid
     and (i.status & 2)      = 2

   -- REPLACE SELECTIVITY SQL FOR UNIQUE INDEXES.

   delete #textsel
   from sysindexes i
   where #textsel.seq    > 1
     and #textsel.seq    < 999
     and #textsel.id     = i.id
     and #textsel.indid  = i.indid
     and (i.status & 2)      = 2

   insert into #textsel
      select i.id, i.indid, o.uid, 2, i.keycnt,
             '   select ' + convert (varchar (10), i.id) + ', '
             + convert (varchar (3), i.indid) + ', '
             + convert (varchar (6), o.uid) + ', '
             + convert (varchar (3), i.keycnt)
             + ', '' '', 1, 1, '' '''
      from sysindexes i,
           sysobjects o
      where i.indid           > 0
        and i.indid           < 254
        and (i.status & 2)    = 2
        and i.id              = o.id
        and (o.sysstat & 15)  = 3

   -- CREATE THE PROLOG AND EPILOG STATEMENTS.

   if (exists (select *
               from #textsel
               where seq                  = 1003
                 and charindex ('"', txt) > 0))
      begin
         insert into #textsel
            values (-2, 0, 0, 1, 0, 'set quoted_identifier on')
      end

   insert into #textsel
      values (-1, 0, 0, 1, 0, 'use ' + db_name ())

   insert into #textsel
      select 0, 0, 0, number, 0, name
      from sybsystemprocs.dbo.usptvalues
      where type   = 'XIS'
        and number > 0

   -- CREATE THE THE EPILOG STATEMENT.

   insert into #textsel
      select max (id) + 1, 0, 0, 1, 0, 'exec sp_textselectivity_print'
      from #textsel

   -- CREATE THE go STATEMENTS.

   insert into #textsel
      select id, indid, 0, 255, 0, 'go'
      from #textsel
      where seq = 1

   insert into #textsel
      select id, indid, 0, 2001, 0, 'go'
      from #textsel
      where seq = 1005

   -- PRINT THE FINISHED SQL.

   open text_cursor

   fetch text_cursor into @txt

   while (@@sqlstatus = 0)
      begin
         print @txt

         fetch text_cursor into @txt
      end

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

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

         return
      end

   close text_cursor

   deallocate cursor text_cursor
go
sp_addcomment sp_textselectivity, 13, 'Generates SQL to report index selectivities'
go
sp_procxmode sp_textselectivity, anymode
go

dump transaction sybsystemprocs with truncate_only
go
