use sybsystemprocs
go
sp_dropprocedure sp_textreconstrain
go
sp_dropprocedure sp_textreindex
go

set quoted_identifier on
go

create procedure sp_textreconstrain (@TABLE  varchar (30)) as

   -- type/seq meanings
   --  @t0/1 = use <database>
   --  @t1/1 = set quoted_identifier on  (only if needed)
   --  @t2/1 = sp_size <table>
   --  @t3/1 = alter table ...
   --  @t3/2 =    drop (referential) constraint ...
   --  @t4/1 = drop index ...
   --  @t5/1 = alter table ...
   --  @t5/2 =    drop (index) constraint ...
   --  @t6/1 = (slot for user code)
   --  @t7/1 = create clustered index ...
   --  @t7/3 =    <columns>
   --  @t7/4 =    with <options>
   --  @t7/5 =    on <segment>
   --  @t8/1 = alter table ...
   --  @t8/2 =    add (clustered index) constraint ...
   --  @t8/3 =    <columns>
   --  @t8/4 =    with <options>
   --  @t8/5 =    on <segment>
   --  @t9/1 = create nonclustered index ...
   --  @t9/3 =    <columns>
   --  @t9/4 =    with <options>
   --  @t9/5 =    on <segment>
   -- @t10/1 = alter table ...
   -- @t10/2 =    add (nonclustered index) constraint ...
   -- @t10/3 =    <columns>
   -- @t10/4 =    with <options>
   -- @t10/5 =    on <segment>
   -- @t11/1 = alter table ...
   -- @t11/2 =    add constraint ...
   -- @t11/3 =    foreign key ...
   -- @t11/4 =    references ...
   -- @t12/1 = sp_chgattribute (fillfactor)
   -- @t12/1 = sp_chgattribute (resrvepagegap)
   -- @t13/1 = sp_chgattribute (max_rows_per_page)
   -- @t14/1 = sp_bindcache ...
   -- @t15/1 = sp_cachestrategy ... prefetch
   -- @t16/1 = sp_cachestrategy ... mru
   -- @t17/1 = sp_recompile ...
   -- @t18/1 = sp_size <table>
   -- @tNN/6 = go

   set ansinull                      on
   set flushmessage                  on
   set nocount                       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_textreconstrain CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring     varchar (255),
           @cmdstring1    varchar (255),
           @cmdstring1qi  varchar (255),
           @cmdstring2    varchar (255),
           @cmdstring2qi  varchar (255),
           @cmdstring3    varchar (255),
           @cmdstring3qi  varchar (255),
           @errorchar     varchar (  4),
           @maxtxtlen     tinyint,
           @numchar       varchar (  3),
           @retval        int,
           @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,
           @t13           tinyint,
           @t14           tinyint,
           @t15           tinyint,
           @t16           tinyint,
           @t17           tinyint,
           @t18           tinyint,
           @tabid         int,
           @textwidth     tinyint,
           @txt           char (133),
           @txtsize       tinyint

   create table #trc
      (type    tinyint                   not null,
       seq     int                       not null,
       id1     int            default 0  not null,         -- tableid
       indid   tinyint        default 0  not null,
       id2     int            default 0  not null,         -- reftabid
       id3     int            default 0  not null,         -- constrid
       id4     int            default 0  not null,         -- pmrydbid
       txt     varchar (255))

   create table #trcprint
      (seq     numeric (7, 0)  identity,
       text    char (133)      not null)

   declare text_cursor cursor for
      select text
      from #trcprint
      order by seq
   for read only

   select @retval = 0,
          @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,
          @t13    = 13,
          @t14    = 14,
          @t15    = 15,
          @t16    = 16,
          @t17    = 17,
          @t18    = 18

   print ' '

   select @tabid = object_id (@TABLE)
   from sysobjects
   where (sysstat & 15) = 3
     and uid            = user_id ()

   if (@tabid IS NULL)
      begin
         print '    USER TABLE %1! DOES NOT EXIST OR IS NOT OWNED BY YOU.',
               @TABLE

         print ' '

         return 1
      end

   -- ARE THERE ANY FOREIGN KEYS IN ANOTHER DATABASE?

   if (exists (select *
               from sysreferences
               where reftabid    = @tabid
                 and frgndbname IS NOT NULL))
      begin
         print '    WARNING: THERE ARE FOREIGN KEYS IN ANOTHER DATABASE!'

         print ' '

         print '    USE sp_refkeysx AND MODIFY THE GENERATED SQL APPROPRIATELY.'

         print ' '

         select @retval = 1
      end

   -- ARE ALL DEPENDENT FOREIGN KEYS ON TABLES OWNED BY THE USER?

   if (exists (select r.constrid
               from sysreferences r,
                    sysobjects ofk
               where r.reftabid = @tabid
                 and ofk.id     = r.tableid
                 and ofk.uid   <> user_id ()))
      begin
         print '    WARNING: THERE ARE DEPENDENT FOREIGN KEY TABLES OWNED BY OTHER USERS.'

         print ' '

         print '    MODIFY THE GENERATED SQL APPROPRIATELY.'

         print ' '

            select 'FOREIGN KEY TABLE' = space (61)
            where 1 = 2
         union
            select user_name (ofk.uid)  +  '.'  +  ofk.name
            from sysreferences r,
                 sysobjects ofk
            where r.reftabid             = @tabid
              and ofk.id                 = r.tableid
              and ofk.uid               <> user_id ()
              and valid_name (ofk.name) <> 0
         union
            select user_name (ofk.uid)  +  '."'  +  ofk.name  +  '"'
            from sysreferences r,
                 sysobjects ofk
            where r.reftabid             = @tabid
              and ofk.id                 = r.tableid
              and ofk.uid               <> user_id ()
              and valid_name (ofk.name)  = 0
         order by 'FOREIGN KEY TABLE'

         print ' '

         select @retval = 1
      end

   select @txt = replicate ('X', 255)

   select @textwidth = col_length ('#trcprint', 'text'),
          @txtsize   = datalength (@txt)

   if (@textwidth <> @txtsize)
      begin
         print '    THE @txt VARIABLE SIZE (%1!) IS UNEQUAL TO THE OUTPUT WIDTH (%2!).',
                    @txtsize, @textwidth

         print ' '

         print '    MATCH THE @txt AND #trcprint.text SIZE VALUES AND RECOMPILE sp_textreindex.'

         print ' '

         return 1
      end

   -- GENERATE TEXT FOR ADDING THE CONSTRAINTS (@t11 ROWS).

   insert into #trc
      select @t11, 1, tableid, 0, reftabid, constrid, pmrydbid,
             'alter table '  +  object_name (tableid)
      from sysreferences
      where frgndbname                         IS NULL
        and pmrydbname                         IS NULL
        and valid_name (object_name (tableid))  > 0
        and ((tableid = @tabid)  or  (reftabid = @tabid))

   insert into #trc
      select @t11, 1, tableid, 0, reftabid, constrid, pmrydbid,
             'alter table "'  +  object_name (tableid)  +  '"'
      from sysreferences
      where frgndbname                         IS NULL
        and pmrydbname                         IS NULL
        and valid_name (object_name (tableid))  = 0
        and ((tableid = @tabid)  or  (reftabid  = @tabid))

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

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

   insert into #trc
      select @t11, 3, id1, 0, id2, id3, id4,
             '   foreign key'
             +  space (char_length (object_name (id2))) + '  ('
      from #trc
      where seq = 1

   select @cmdstring1   = 'update #trc set txt=t.txt'
                          +  '+'', '''
                          +  '+col_name(tableid, fokey',
          @cmdstring2   = ') from sysreferences r,#trc t'
                          +  ' where t.type='
                          +  convert (varchar (2), @t11)
                          +  ' and t.seq=3 and r.tableid=t.id1 and r.reftabid=t.id2 and r.constrid=t.id3'
                          +  ' and valid_name(col_name(tableid, fokey',
          @cmdstring3   = '))>0',
          @cmdstring1qi = 'update #trc set txt=t.txt'
                          +  '+'', "'''
                          +  '+col_name(tableid, fokey',
          @cmdstring2qi = ')+''"'' from sysreferences r,#trc t'
                          +  ' where t.type='
                          +  convert (varchar (2), @t11)
                          +  ' and t.seq=3 and r.tableid=t.id1 and r.reftabid=t.id2 and r.constrid=t.id3'
                          +  ' and valid_name(col_name(tableid, fokey',
          @cmdstring3qi = '))=0',
          @sequence     = 1

   while (@sequence < 17)
      begin
         select @numchar = convert (varchar (2), @sequence)

         select @cmdstring = @cmdstring1  +  @numchar  +  @cmdstring2  +  @numchar  +  @cmdstring3

         execute  (@cmdstring)

         select @cmdstring = @cmdstring1qi  +  @numchar  +  @cmdstring2qi  +  @numchar  +  @cmdstring3qi

         execute  (@cmdstring)

         -- UNFORTUNATELY valid_name (NULL) RETURNS 0 INSTEAD OF NULL SO ...

         update #trc
         set txt = substring (txt, 1, charindex (', ""', txt) - 1)
         where type                    = @t11
           and seq                     = 3
           and charindex (', ""', txt) > 0

         select @sequence = @sequence + 1
      end

   update #trc
   set txt = stuff (txt, charindex ('(, ', txt), 3, '(')
   where type                   = @t11
     and seq                    = 3
     and charindex ('(, ', txt) > 1

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

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

   select @cmdstring1   = 'update #trc set txt=t.txt'
                          +  '+'', '''
                          +  '+col_name(reftabid,refkey',
          @cmdstring2   = ',pmrydbid) from sysreferences r,#trc t'
                          +  ' where t.type='
                          +  convert (varchar (2), @t11)
                          +  ' and t.seq=4 and r.tableid=t.id1 and r.reftabid=t.id2 and r.constrid=t.id3'
                          +  ' and valid_name(col_name(reftabid,refkey',
          @cmdstring3   = ',pmrydbid))>0',
          @cmdstring1qi = 'update #trc set txt=t.txt'
                          +  '+'', "'''
                          +  '+col_name(reftabid,refkey',
          @cmdstring2qi = ',pmrydbid)+''"'' from sysreferences r,#trc t'
                          +  ' where t.type='
                          +  convert (varchar (2), @t11)
                          +  ' and t.seq=4 and r.tableid=t.id1 and r.reftabid=t.id2 and r.constrid=t.id3'
                          +  ' and valid_name(col_name(reftabid,refkey',
          @cmdstring3qi = ',pmrydbid))=0',
          @sequence     = 1

   while (@sequence < 17)
      begin
         select @numchar = convert (varchar (2), @sequence)

         select @cmdstring = @cmdstring1 + @numchar + @cmdstring2 + @numchar + @cmdstring3

         execute  (@cmdstring)

         select @cmdstring = @cmdstring1qi + @numchar + @cmdstring2qi + @numchar + @cmdstring3qi

         execute  (@cmdstring)

         -- UNFORTUNATELY valid_name (NULL) returns 0 INSTEAD OF NULL SO ...

         update #trc
         set txt = substring (txt, 1, charindex (', ""', txt) - 1)
         where type                    = @t11
           and seq                     = 4
           and charindex (', ""', txt) > 0

         select @sequence = @sequence + 1
      end

   update #trc
   set txt = stuff (txt, charindex ('(, ', txt), 3, '(')
   where type                   = @t11
     and seq                    = 4
     and charindex ('(, ', txt) > 1

   update #trc
   set txt = txt + ')'
   where type  = @t11
     and seq  in (3, 4)

   -- INSERT @t0, @t2, @t3, AND @t18 ROWS.

   insert into #trc (type, seq, id1, txt)
      values (@t0, 1, 0, 'use ' + db_name ())

   if (valid_name (@TABLE) = 0)
      begin
         insert into #trc (type, seq, id1, txt)
            values (@t2, 1, 0, 'sp_size "'  +  @TABLE  +  '"')

         insert into #trc (type, seq, id1, txt)
            values (@t18, 1, 0, 'sp_size "'  +  @TABLE  +  '"')
      end
   else
      begin
         insert into #trc (type, seq, id1, txt)
            values (@t2, 1, 0, 'sp_size '  +  @TABLE)

         insert into #trc (type, seq, id1, txt)
            values (@t18, 1, 0, 'sp_size '  +  @TABLE)
      end

   insert into #trc
      select @t3, 1, id1, 0, id2, id3, id4, txt
      from #trc
      where type = @t11
        and seq  = 1

   insert into #trc
      select @t3, 2, id1, 0, id2, id3, id4, '   drop ' + substring (txt, 8, 255)
      from #trc
      where type = @t11
        and seq  = 2

   -- CREATE CLUSTERED INDEX CREATION (@t7) ROWS.

   insert into #trc (type, seq, id1, indid, txt)
      select @t7, 1, id, 1, 'create clustered index '  +  name
      from sysindexes
      where id               = @tabid
        and indid            = 1
        and (status & 2)    <> 2
        and (status & 16)    = 16
        and (status & 2048) <> 2048
        and (status2 & 2)   <> 2

   insert into #trc (type, seq, id1, indid, txt)
      select @t7, 1, id, 1, 'create unique clustered index '  +  name
      from sysindexes
      where id               = @tabid
        and indid            = 1
        and (status & 2)     = 2
        and (status & 16)    = 16
        and (status & 2048) <> 2048
        and (status2 & 2)   <> 2

   -- CREATE NONCLUSTERED INDEX CREATION (@t9) ROWS.

   insert into #trc (type, seq, id1, indid, txt)
      select @t9, 1, id, indid, 'create nonclustered index '  +  name
      from sysindexes
      where id               = @tabid
        and indid            > 1
        and indid            < 254
        and (status & 2)    <> 2
        and (status & 16)   <> 16
        and (status & 2048) <> 2048
        and (status2 & 2)   <> 2

   insert into #trc (type, seq, id1, indid, txt)
      select @t9, 1, id, indid, 'create unique nonclustered index '  +  name
      from sysindexes
      where id               = @tabid
        and indid            > 1
        and indid            < 254
        and (status & 2)     = 2
        and (status & 16)   <> 16
        and (status & 2048) <> 2048
        and (status2 & 2)   <> 2

   -- CREATE INDEX "on segment" (@t7 AND @t9) ROWS.

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

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

   -- CREATE CLUSTERED INDEX CONSTRAINT (@t8) ROWS.

   insert into #trc (type, seq, id1, indid, txt)
      select @t8, 1, id, 1, 'alter table '  +  @TABLE
      from sysindexes
      where id                = @tabid
        and indid             = 1
        and (status & 16)     = 16
        and (status2 & 2)     = 2
        and valid_name (name) > 0

   insert into #trc (type, seq, id1, indid, txt)
      select @t8, 1, id, 1, 'alter table "'  +  @TABLE  +  '"'
      from sysindexes
      where id                = @tabid
        and indid             = 1
        and (status & 16)     = 16
        and (status2 & 2)     = 2
        and valid_name (name) = 0

   insert into #trc (type, seq, id1, indid, txt)
      select @t8, 2, t.id1, 1,
             '   add constraint '  +  i.name  +  ' primary key clustered'
      from sysindexes i,
           #trc t
      where t.type              = @t8
        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 #trc (type, seq, id1, indid, txt)
      select @t8, 2, t.id1, 1,
             '   add constraint "'  +  i.name  +  '" primary key clustered'
      from sysindexes i,
           #trc t
      where t.type              = @t8
        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 #trc (type, seq, id1, indid, txt)
      select @t8, 2, t.id1, 1,
             '   add constraint '  +  i.name  +  ' unique clustered'
      from sysindexes i,
           #trc t
      where t.type                = @t8
        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 #trc (type, seq, id1, indid, txt)
      select @t8, 2, t.id1, 1,
             '   add constraint "'  +  i.name  +  '" unique clustered'
      from sysindexes i,
           #trc t
      where t.type                = @t8
        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

   -- CREATE NONCLUSTERED INDEX CONSTRAINT (@t10) ROWS.

   insert into #trc (type, seq, id1, indid, txt)
      select @t10, 1, id, indid, 'alter table '  +  @TABLE
      from sysindexes
      where id                   = @tabid
        and indid                > 1
        and (status & 16)       <> 16
        and (status2 & 2)        = 2
        and valid_name (@TABLE)  > 0

   insert into #trc (type, seq, id1, indid, txt)
      select @t10, 1, id, indid, 'alter table "'  +  @TABLE  +  '"'
      from sysindexes
      where id                   = @tabid
        and indid                > 1
        and (status & 16)       <> 16
        and (status2 & 2)        = 2
        and valid_name (@TABLE)  = 0

   insert into #trc (type, seq, id1, indid, txt)
      select @t10, 2, t.id1, t.indid,
             '   add constraint '  +  i.name  +  ' primary key nonclustered'
      from sysindexes i,
           #trc t
      where t.type              = @t10
        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 #trc (type, seq, id1, indid, txt)
      select @t10, 2, t.id1, t.indid,
             '   add constraint "'  +  i.name  +  '" primary key nonclustered'
      from sysindexes i,
           #trc t
      where t.type              = @t10
        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 #trc (type, seq, id1, indid, txt)
      select @t10, 2, t.id1, t.indid,
             '   add constraint '  +  i.name  +  ' unique nonclustered'
      from sysindexes i,
           #trc t
      where t.type               = @t10
        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 #trc (type, seq, id1, indid, txt)
      select @t10, 2, t.id1, t.indid,
             '   add constraint "'  +  i.name  +  '" unique nonclustered'
      from sysindexes i,
           #trc t
      where t.type               = @t10
        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

   -- CREATE (@t8 AND @t10) ROWS FOR KEYS.

   insert into #trc (type, seq, id1, indid, txt)
      select type, 3, id1, indid, '      ('
      from #trc
      where type in (@t8, @t10)
        and seq   = 1

   select @sequence = 1

   while (@sequence < 17)
      begin
         update #trc
         set txt = t.txt
                   +  ' '
                   +  index_col (@TABLE, i.indid, @sequence)
                   +  ' '
                   +  index_colorder (@TABLE, i.indid, @sequence)
                   +  ','
         from sysindexes i,
              #trc t
         where t.type   in (@t7, @t8, @t9, @t10)
           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 #trc
         set txt = t.txt
                   +  ' "'
                   +  index_col (@TABLE, i.indid, @sequence)
                   +  ' '
                   +  index_colorder (@TABLE, i.indid, @sequence)
                   +  '",'
         from sysindexes i,
              #trc t
         where t.type   in (@t7, @t8, @t9, @t10)
           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 #trc
         set txt = t.txt
                   +  ' '
                   +  index_col (object_name (i.id), i.indid, @sequence)
                   +  ' '
                   +  index_colorder (object_name (i.id), i.indid, @sequence)
                   +  ')'
         from sysindexes i,
              #trc t
         where t.type    in (@t7, @t8, @t9, @t10)
           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 #trc
         set txt = t.txt
                   +  ' "'
                   +  index_col (object_name (i.id), i.indid, @sequence)
                   +  '" '
                   +  index_colorder (object_name (i.id), i.indid, @sequence)
                   +  ')'
         from sysindexes i,
              #trc t
         where t.type   in (@t7, @t8, @t9, @t10)
           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

   -- ELIMINATE THE ASCENDING TEXT  (type=@t7/@t8/@t9/@t10)

   select @sequence = 1

   while (@sequence < 17)
      begin
         update #trc
         set txt = stuff (txt, charindex (' ASC', txt), 4, NULL)
         where charindex (' ASC', txt) > 0

         select @sequence = @sequence + 1
      end

   update #trc
   set txt = stuff (txt, charindex ('( ', txt), 2, '(')
   where type                   in (@t7, @t9)
     and charindex ('( ', txt)  > 0

   update #trc
   set txt = '      ('  +  substring (txt, charindex ('( ', txt) + 2, 255)
   where type                   in (@t8, @t10)
     and charindex ('( ', txt)  > 0

   --- CLUSTERED INDEX "WITH" TEXT (type=@t7, seq=4)

   insert into #trc (type, seq, id1, indid, txt)
      select @t7, 4, i.id, 1, '   with sorted_data'
      from sysindexes i,
           #trc t
      where t.type           = @t7
        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 #trc (type, seq, id1, indid, txt)
      select @t7, 4, i.id, 1, '   with ignore_dup_key, sorted_data'
      from sysindexes i,
           #trc t
      where t.type         = @t7
        and t.seq          = 1
        and t.id1          = i.id
        and i.indid        = 1
        and t.indid        = 1
        and (i.status & 1) = 1

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

   insert into #trc (type, seq, id1, indid, txt)
      select @t7, 4, i.id, 1, '   with allow_dup_row, sorted_data'
      from sysindexes i,
           #trc t
      where t.type          = @t7
        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 (type=@t9, seq=4)

   insert into #trc (type, seq, id1, indid, txt)
      select @t9, 4, i.id, i.indid, '   with ignore_dup_key'
      from sysindexes i,
           #trc t
      where t.type         = @t9
        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 #trc (type, seq, id1, indid, txt)
      select @t9, 4, i.id, i.indid, '   with ignore_dup_row'
      from sysindexes i,
           #trc t
      where t.type         = @t9
        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 #trc (type, seq, id1, indid, txt)
      select @t9, 4, i.id, i.indid, '   with allow_dup_row'
      from sysindexes i,
           #trc t
      where t.type          = @t9
        and t.seq           = 1
        and t.id1           = i.id
        and i.indid         = t.indid
        and t.indid         > 1
        and (i.status & 64) = 64

   -- "FILLFACTOR", "MAX_ROWS_PER_PAGE", AND "RESERVEPAGEGAP" TEXT (type=@t6/@t7/@t8/@t9, seq=4)

   update #trc
   set txt = t.txt  +  ', fillfactor='  +  convert (varchar (3), i.fill_factor)
   from #trc t,
        sysindexes i
   where t.type        in (@t7, @t8, @t9, @t10)
     and t.seq          = 4
     and i.id           = t.id1
     and i.indid        = t.indid
     and i.fill_factor  > 0

   insert into #trc
      select distinct t1.type, 4, t1.id1, t1.indid, t1.id2, t1.id3, t1.id4,
                      '   with fillfactor='  +  convert (varchar (3), i.fill_factor)
      from #trc t1,
           sysindexes i
      where t1.type       in (@t7, @t8, @t9, @t10)
        and t1.seq         = 1
        and i.id           = t1.id1
        and i.indid        = t1.indid
        and i.fill_factor  > 0
        and i.fill_factor IS NOT NULL
        and (not exists (select t2.type
                         from #trc t2
                         where t2.type  = t1.type
                           and t2.id1   = t1.id1
                           and t2.indid = t1.indid
                           and t2.seq   = 4))

   update #trc
   set txt = t.txt  +  ', max_rows_per_page='  +  convert (varchar (3), i.maxrowsperpage)
   from #trc t,
        sysindexes i
   where t.type           in (@t7, @t8, @t9, @t10)
     and t.seq             = 4
     and i.id              = t.id1
     and i.indid           = t.indid
     and i.maxrowsperpage  > 0
     and ((i.fill_factor = 0)  or  (i.fill_factor IS NULL))

   insert into #trc
      select distinct t1.type, 4, t1.id1, t1.indid, t1.id2, t1.id3, t1.id4,
                      '   with max_rows_per_page='  +  convert (varchar (3), i.maxrowsperpage)
      from #trc t1,
           sysindexes i
      where t1.type          in (@t7, @t8, @t9, @t10)
        and t1.seq            = 1
        and i.id              = t1.id1
        and i.indid           = t1.indid
        and i.maxrowsperpage  > 0
        and ((i.fill_factor = 0)  or  (i.fill_factor IS NULL))
        and (not exists (select t2.type
                         from #trc t2
                         where t2.type  = t1.type
                           and t2.id1   = t1.id1
                           and t2.indid = t1.indid
                           and t2.seq   = 4))

   update #trc
   set txt = t.txt  +  ', reservepagegap='  +  convert (varchar (3), i.res_page_gap)
   from #trc t,
        sysindexes i
   where t.type         in (@t7, @t8, @t9, @t10)
     and t.seq           = 4
     and i.id            = t.id1
     and i.indid         = t.indid
     and i.res_page_gap  > 0

   insert into #trc
      select distinct t1.type, 4, t1.id1, t1.indid, t1.id2, t1.id3, t1.id4,
                      '   with reservepagegap='  +  convert (varchar (3), i.res_page_gap)
      from #trc t1,
           sysindexes i
      where t1.type        in (@t7, @t8, @t9, @t10)
        and i.id            = t1.id1
        and i.indid         = t1.indid
        and i.res_page_gap  > 0
        and (not exists (select t2.type
                         from #trc t2
                         where t2.type  = t1.type
                           and t2.id1   = t1.id1
                           and t2.indid = t1.indid
                           and t2.seq   = 4))

   -- "ON SEGMENT" TEXT (type=@t6/@t7/@t8/@t9, seq=5)

   insert into #trc
      select t.type, 5, t.id1, t.indid, t.id2, t.id3, t.id4, '   on '  +  s.name
      from sysindexes i,
           syssegments s,
           #trc t
      where t.type    in (@t7, @t8, @t9, @t10)
        and t.seq      = 1
        and i.id       = t.id1
        and i.indid    = t.indid
        and s.segment  = i.segment

   update #trc
   set txt = stuff (txt, charindex ('default', txt), 7, '''default''')
   where charindex ('default', txt)  > 0
     and type                        in (@t7, @t8, @t9, @t10)
     and seq                         = 5

   -- GENERATE THE "DROP INDEX" SQL (tpe=@t4/@t5)

   insert into #trc (type, seq, id1, indid, txt)
      select @t4, 1, i.id, i.indid,
             'drop index '  +  object_name (i.id)  +  '.'  +  i.name
      from sysindexes i,
           #trc t
      where t.type  in (@t7, @t9)
        and t.seq    = 1
        and i.id     = t.id1
        and i.indid  = t.indid

   insert into #trc (type, seq, id1, indid, txt)
      select @t5, 1, id1, indid, txt
      from #trc
      where type in (@t8, @t10)
        and seq   = 1

   insert into #trc (type, seq, id1, indid, txt)
      select @t5, 2, id1, indid,
             '   drop '  +  substring (txt, 8, charindex ('primary key', txt) - 9)
      from #trc
      where type                           in (@t8, @t10)
        and seq                             = 2
        and charindex ('primary key', txt)  > 0

   insert into #trc (type, seq, id1, indid, txt)
      select @t5, 2, id1, indid,
             '   drop '  +  substring (txt, 8, charindex ('unique', txt) - 9)
      from #trc
      where type                      in (@t8, @t10)
        and seq                        = 2
        and charindex ('unique', txt)  > 0

   -- GENERATE sp_chgattribute TEXT (type=@t12/@t13/@t14)

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t12, 1, id, indid, indid,
             'sp_chgattribute '''
             +  @TABLE
             +  ''', ''fillfactor'', '
             +  convert (varchar (3), fill_factor)
      from sysindexes
      where id          = @tabid
        and indid       < 2
        and fill_factor > 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t12, 1, id, indid, indid,
             'sp_chgattribute '''
             +  @TABLE
             +  '.'
             +  name
             +  ''', ''fillfactor'', '
             +  convert (varchar (3), fill_factor)
      from sysindexes
      where id          = @tabid
        and indid       > 1
        and indid       < 254
        and fill_factor > 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t13, 1, id, indid, indid,
             'sp_chgattribute '''
             +  @TABLE
             +  ''', ''reservepagegap'', '
             +  convert (varchar (3), res_page_gap)
      from sysindexes
      where id           = @tabid
        and indid        < 2
        and res_page_gap > 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t13, 1, id, indid, indid,
             'sp_chgattribute '''
             +  @TABLE
             +  '.'
             +  name
             +  ''', ''reservepagegap'', '
             +  convert (varchar (3), res_page_gap)
      from sysindexes
      where id           = @tabid
        and indid        > 1
        and indid        < 254
        and res_page_gap > 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t14, 1, id, indid, indid,
             'sp_chgattribute '''
             +  @TABLE
             +  ''', ''max_rows_per_page'', '
             +  convert (varchar (3), maxrowsperpage)
      from sysindexes
      where id             = @tabid
        and indid          < 2
        and maxrowsperpage > 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t14, 1, id, indid, indid,
             'sp_chgattribute '''
             +  @TABLE
             +  '.'
             +  name
             +  ''', ''max_rows_per_page'', '
             +  convert (varchar (3), maxrowsperpage)
      from sysindexes
      where id             = @tabid
        and indid          > 1
        and indid          < 254
        and maxrowsperpage > 0

   -- GENERATE sp_cachestartegy TEXT (@t15/@t16)

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t15, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', '
             +  o.name
             +  ', ''table only'', prefetch, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             = 0
        and (i.status2 & 16)    = 16
        and valid_name (o.name) > 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t15, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', '
             +  o.name
             +  ', '
             +  i.name
             +  ', prefetch, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             > 0
        and i.indid             < 254
        and (i.status2 & 16)    = 16
        and valid_name (o.name) > 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t15, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', '
             +  o.name
             +  ', ''text only'', prefetch, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             > 254
        and (i.status2 & 16)    = 16
        and valid_name (o.name) > 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t15, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', "'
             +  o.name
             +  '", ''table only'', prefetch, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             = 0
        and (i.status2 & 16)    = 16
        and valid_name (o.name) = 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t15, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', "'
             +  o.name
             +  '", '
             +  i.name
             +  ', prefetch, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             > 0
        and i.indid             < 254
        and (i.status2 & 16)    = 16
        and o.id                = i.id
        and valid_name (o.name) = 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t15, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', "'
             +  o.name
             +  '", ''text only'', prefetch, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             > 254
        and (i.status2 & 16)    = 16
        and valid_name (o.name) = 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t16, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', '
             +  o.name
             +  ', ''table only'', mru, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             = 0
        and i.indid             < 254
        and (i.status2 & 32)    = 32
        and valid_name (o.name) > 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t16, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', '
             +  o.name
             +  ', '
             +  i.name
             +  ', mru, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             > 0
        and i.indid             < 254
        and (i.status2 & 32)    = 32
        and valid_name (o.name) > 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t16, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', '
             +  o.name
             +  ', ''text only'', mru, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             > 254
        and (i.status2 & 32)    = 32
        and valid_name (o.name) > 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t16, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', "'
             +  o.name
             +  '", ''table only'', mru, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             = 0
        and i.indid             < 254
        and (i.status2 & 32)    = 32
        and valid_name (o.name) = 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t16, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', "'
             +  o.name
             +  '", '
             +  i.name
             +  ', mru, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             > 0
        and i.indid             < 254
        and (i.status2 & 32)    = 32
        and valid_name (o.name) = 0

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t16, 1, o.id, i.indid, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', "'
             +  o.name
             +  '", ''text only'', mru, ''off'''
      from sysindexes i,
           sysobjects o
      where o.id                = @tabid
        and i.id                = o.id
        and i.indid             > 254
        and (i.status2 & 32)    = 32
        and valid_name (o.name) = 0

   -- GENERATE sp_bindcache TEXT (@t17)

   insert into #trc (type, seq, id1, indid, id4, txt)
      select @t17, 1, a.object, i.indid, i.indid,
             'sp_bindcache '''
             +  rtrim (a.char_value)
             +  ''', '
             +  db_name ()
             +  ', '
             +  object_name (a.object)
             +  ', '
             +  i.name
      from sysattributes a,
           sysindexes i
      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 i.id             = @tabid

   -- GENERATE sp_recompile TEXT (@type=@t18)

   insert into #trc (type, seq, id1, txt)
      select @t18, 1, @tabid, 'sp_recompile "'  +  @TABLE  +  '"'
      where valid_name (@TABLE) = 0

   insert into #trc (type, seq, id1, txt)
      select @t18, 1, @tabid, 'sp_recompile '  +  @TABLE
      where valid_name (@TABLE) > 0

   -- GENERATE THE FINAL SQL

   if (exists (select *
               from #trc
               where type in (@t7, @t11)))
      begin
         if (exists (select *
                     from #trc
                     where type in (@t7, @t8, @t9, @t10, @t11)
                       and charindex ('"', txt) > 0))
            begin
               insert into #trc (type, seq, txt)
                  values (@t1, 1, 'set quoted_identifier on')
            end

         insert into #trc
            select type, 6, id1, indid, id2, id3, id4, 'go'
            from #trc
            where seq = 1

         -- INSERT SECTION TITLE COMMENTS

         insert into #trc (type, seq, txt)
            select min (u.number), 0, u.name
            from sybsystemprocs.dbo.usptvalues u
            where u.type = 'XRC'
              and exists (select r.*
                          from #trc r
                          where r.type = u.number
                            and u.type = 'XRC')
            group by u.name

         insert into #trc (type, seq, txt)
            select 6, aux1, name
            from sybsystemprocs.dbo.usptvalues
            where type   = 'XRC'
              and number = 6

         select @maxtxtlen = max (char_length (txt))
         from #trc

         if (@textwidth < @maxtxtlen)
            begin
               print ' '

               print '    THE RECONSTRAIN TEXT (%1!) IS WIDER THAN THE OUTPUT WIDTH (%2!).',
                          @maxtxtlen, @textwidth

               print ' '

               print '    ADJUST THE @txt AND #trcprint.text SIZE VALUES AND RECOMPILE sp_textreconstrain.'

               print ' '

               select @retval = 1
            end
         else
            begin
               insert into #trcprint (text)
                  select distinct txt
                  from #trc
                  order by type, object_name (id1), object_name (id3), indid, seq

               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
      end
   else
      begin
         print ' '

         print '    THERE ARE NO INDEX/REFERENTIAL CONSTRAINTS FOR %1!.', @TABLE

         print ' '
      end

   return @retval
go
sp_addcomment sp_textreconstrain, 'Generates SQL to drop/add index/referential constraints for'
go
sp_addcomment sp_textreconstrain, 'TABLE'
go
sp_procxmode sp_textreconstrain, anymode
go

create procedure sp_textreindex as

   -- type1/type2/seq meanings
   --    -1    -1   1 = set quoted_identifier on (if appropriate)
   --     0   @t0   1 = use <database>
   --     1   @t1   1 = sp_freespace
   --     2   @t2   1 = alter table ...
   --     2   @t2   2 =    drop (referential) constraint ...
   --     3   @t3   1 = drop index ...
   --     4   @t4   1 = alter table ...
   --     4   @t4   2 =    drop (index) constraint ...
   --     5   @t5   1 = create clustered index ...
   --     5   @t5   3 =    <columns>
   --     5   @t5   4 =    with <options>
   --     5   @t5   5 =    on <segment>
   --     5   @t6   1 = alter table ...
   --     5   @t6   2 =    add (clustered index) constraint ...
   --     5   @t6   3 =    <columns>
   --     5   @t6   4 =    with <options>
   --     5   @t6   5 =    on <segment>
   --     5   @t7   1 = create nonclustered index ...
   --     5   @t7   3 =    <columns>
   --     5   @t7   4 =    with <options>
   --     5   @t7   5 =    on <segment>
   --     5   @t8   1 = alter table ...
   --     5   @t8   2 =    add (nonclustered index) constraint ...
   --     5   @t8   3 =    <columns>
   --     5   @t8   4 =    with <options>
   --     5   @t8   5 =    on <segment>
   --     5   @t9   1 = sp_chgattribute (fillfactor)
   --     5  @t10   1 = sp_chgattribute (resrvepagegap)
   --     5  @t11   1 = sp_chgattribute (max_rows_per_page)
   --     5  @t12   1 = sp_bindcache ...
   --     5  @t13   1 = sp_cachestrategy ... prefetch
   --     5  @t14   1 = sp_cachestrategy ... mru
   --     5  @t15   1 = sp_recompile ...
   --    16  @t16   1 = alter table ...
   --    16  @t16   2 =    add (referential) constraint ...
   --    16  @t16   3 =    foreign key ...
   --    16  @t16   4 =    references ...
   --    17  @t17   1 = sp_freespace
   --    NN  @tNN   6 = go

   -- THE type1 & type2 COLUMNS ALLOW FOR A HIERARCHICAL SEQUENCE OF OPERATIONS.
   -- THE type1 = 5 OPERATIONS SUPPORT DOING SEVERAL OPERATIONS ON A PER TABLE
   -- BASIS.  MOST OF THE TEXT GENERATION IS DONE WITH type2 AND seq.  AT THE
   -- END type1 VALUES ARE SET FROM type2.

   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_textreindex CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring     varchar (255),
           @cmdstring1    varchar (255),
           @cmdstring1qi  varchar (255),
           @cmdstring2    varchar (255),
           @cmdstring2qi  varchar (255),
           @cmdstring3    varchar (255),
           @cmdstring3qi  varchar (255),
           @errorchar     varchar (  4),
           @maxtxtlen     tinyint,
           @maxnameid     int,             -- id of max table name
           @minnameid     int,             -- id of min table name
           @numchar       varchar (  2),
           @sequence      int,
           @retval        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,
           @t13           tinyint,
           @t14           tinyint,
           @t15           tinyint,
           @t16           tinyint,
           @t17           tinyint,
           @textwidth     tinyint,
           @txt           char (127),
           @txtsize       tinyint,
           @username      varchar ( 30)

   create table #tri
      (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))

   create table #triprint
      (seq     numeric (7, 0)  identity,
       text    char (127)      not null)

   declare text_cursor cursor for
      select text
      from #triprint
      order by seq
   for read only

   select @retval = 0,
          @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,
          @t13    = 13,
          @t14    = 14,
          @t15    = 15,
          @t16    = 16,
          @t17    = 17

   -- ARE THERE ANY FOREIGN KEYS IN OTHER DATABASES?

   if (exists (select *
               from sysreferences
               where frgndbname IS NOT NULL))
      begin
         print '    WARNING: THERE ARE FOREIGN KEYS IN OTHER DATABASES.'

         print ' '

         print '    USE sp_refkeysx AND MODIFY THE GENERATED SQL APPROPRIATELY'

         print ' '

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

         print ' '

         select @retval = 1
      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'

         print ' '

         select @retval = 1
      end

   -- ARE ALL INDEXES ON TABLES OWNED BY THE USER?

   if (exists (select o.id
               from sysindexes i,
                    sysobjects o
               where i.indid  > 0
                 and i.indid  < 254
                 and o.id     = i.id
                 and o.uid   <> user_id ()))
      begin
         print '    WARNING: THERE ARE INDEXES ON TABLES NOT OWNED BY THE USER.'

         print ' '

         print '    MODIFY THE GENERATED SQL APPROPRIATELY.'

         print ' '

            select 'USER.TABLE' = space (61)
            where 1 = 2
         union
            select user_name (o.uid)  +  '.'  +  o.name
            from sysindexes i,
                 sysobjects o
            where i.indid              > 0
              and i.indid              < 254
              and o.id                 = i.id
              and o.uid               <> user_id ()
              and valid_name (o.name)  > 0
         union
            select user_name (o.uid)  +  '."'  +  o.name  +  '"'
            from sysindexes i,
                 sysobjects o
            where i.indid              > 0
              and i.indid              < 254
              and o.id                 = i.id
              and o.uid               <> user_id ()
              and valid_name (o.name)  = 0
         order by 'USER.TABLE'

         print ' '

         select @retval = 1
      end

   select @txt = replicate ('X', 255)

   select @textwidth = col_length ('#triprint', 'text'),
          @txtsize   = datalength (@txt)

   if (@textwidth <> @txtsize)
      begin
         print '    THE @txt VARIABLE SIZE (%1!) IS UNEQUAL TO THE OUTPUT WIDTH (%2!).',
                    @txtsize, @textwidth

         print ' '

         print '    MATCH THE @txt AND #triprint.text SIZE VALUES AND RECOMPILE sp_textreindex.'

         print ' '

         return 1
      end

   -- PROLOG TEXT

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

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

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

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

   insert into #tri (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.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        and i.id                  = o.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 #tri (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.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        and i.id                  = o.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 #tri (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.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        and i.id                  = o.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 #tri (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.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        and i.id                  = o.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 #tri (type2, seq, id1, indid, txt)
      select type2, 3, id1, indid, '   on '  +  object_name (id1)  +  ' ('
      from #tri
      where type2                           in (@t5, @t7)
        and valid_name (object_name (id1)) > 0

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

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

   insert into #tri (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.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        and i.id                  = o.id
        and i.indid               = 1
        and (i.status & 16)       = 16
        and (i.status2 & 2)       = 2
        and valid_name (o.name)   > 0

   insert into #tri (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.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        and i.id                  = o.id
        and i.indid               = 1
        and (i.status & 16)       = 16
        and (i.status2 & 2)       = 2
        and valid_name (o.name)   = 0

   insert into #tri (type2, seq, id1, txt)
      select @t6, 2, t.id1,
             '   add constraint '  +  i.name  +  ' primary key clustered'
      from sysindexes i,
           #tri 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 #tri (type2, seq, id1, txt)
      select @t6, 2, t.id1,
             '   add constraint "'  +  i.name  +  '" primary key clustered'
      from sysindexes i,
           #tri 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 #tri (type2, seq, id1, txt)
      select @t6, 2, t.id1,
             '   add constraint '  +  i.name  +  ' unique clustered'
      from sysindexes i,
           #tri 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 #tri (type2, seq, id1, txt)
      select @t6, 2, t.id1,
             '   add constraint "'  +  i.name  +  '" unique clustered'
      from sysindexes i,
           #tri 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 #tri (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 (o.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        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 #tri (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 (o.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        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 #tri (type2, seq, id1, indid, txt)
      select @t8, 2, t.id1, t.indid,
             '   add constraint '  +  i.name  +  ' primary key nonclustered'
      from sysindexes i,
           #tri 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 #tri (type2, seq, id1, indid, txt)
      select @t8, 2, t.id1, t.indid,
             '   add constraint "'  +  i.name  +  '" primary key nonclustered'
      from sysindexes i,
           #tri 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 #tri (type2, seq, id1, indid, txt)
      select @t8, 2, t.id1, t.indid,
             '   add constraint '  +  i.name  +  ' unique nonclustered'
      from sysindexes i,
           #tri 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 #tri (type2, seq, id1, indid, txt)
      select @t8, 2, t.id1, t.indid,
             '   add constraint "'  +  i.name  +  '" unique nonclustered'
      from sysindexes i,
           #tri 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 (type2=@t5/@t6/@t7/@t8, seq=3)

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

   select @sequence = 1

   while (@sequence < 17)
      begin
         update #tri
         set txt = t.txt
                   +  ' '
                   +  index_col (object_name (i.id), i.indid, @sequence)
                   + ' '
                   +  index_colorder (object_name (i.id), i.indid, @sequence)
                   +  ','
         from sysindexes i,
              #tri 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 #tri
         set txt = t.txt
                   +  ' "'
                   +  index_col (object_name (i.id), i.indid, @sequence)
                   +  '" '
                   +  index_colorder (object_name (i.id), i.indid, @sequence)
                   +  ','
         from sysindexes i,
              #tri 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 #tri
         set txt = t.txt
                   +  ' '
                   +  index_col (object_name (i.id), i.indid, @sequence)
                   + ' '
                   +  index_colorder (object_name (i.id), i.indid, @sequence)
                   +  ')'
         from sysindexes i,
              #tri 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 #tri
         set txt = t.txt
                   +  ' "'
                   +  index_col (object_name (i.id), i.indid, @sequence)
                   +  '" '
                   +  index_colorder (object_name (i.id), i.indid, @sequence)
                   +  ')'
         from sysindexes i,
              #tri 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

   -- ELIMINATE THE ASCENDING TEXT  (type2=@t5/@t6/@t7/@t8)

   select @sequence = 1

   while (@sequence < 17)
      begin
         update #tri
         set txt = stuff (txt, charindex (' ASC', txt), 4, NULL)
         where charindex (' ASC', txt) > 0

         select @sequence = @sequence + 1
      end

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

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

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

   insert into #tri (type2, seq, id1, txt)
      select @t5, 4, i.id, '   with sorted_data'
      from sysindexes i,
           #tri 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 #tri (type2, seq, id1, txt)
      select @t5, 4, i.id, '   with ignore_dup_key, sorted_data'
      from sysindexes i,
           #tri 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 #tri (type2, seq, id1, txt)
      select @t5, 4, i.id, '   with ignore_dup_row, sorted_data'
      from sysindexes i,
           #tri 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 #tri (type2, seq, id1, txt)
      select @t5, 4, i.id, '   with allow_dup_row, sorted_data'
      from sysindexes i,
           #tri 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=@t7, seq=4)

   insert into #tri (type2, seq, id1, indid, txt)
      select @t7, 4, i.id, i.indid, '   with ignore_dup_key'
      from sysindexes i,
           #tri 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 #tri (type2, seq, id1, indid, txt)
      select @t7, 4, i.id, i.indid, '   with ignore_dup_row'
      from sysindexes i,
           #tri 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 #tri (type2, seq, id1, indid, txt)
      select @t7, 4, i.id, i.indid, '   with allow_dup_row'
      from sysindexes i,
           #tri 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 (type2=@t5/@t6/@t7/@t8, seq=5)

   insert into #tri
      select 0, t.type2, 5, t.id1, t.indid, t.id2, t.id3, t.id4, '   on '  +  s.name
      from sysindexes i,
           syssegments s,
           #tri 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 #tri
   set txt = stuff (txt, charindex ('default', txt), 7, '''default''')
   where charindex ('default', txt)  > 0
     and type2                      in (@t5, @t6, @t7, @t8)
     and seq                         = 5

   -- "FILLFACTOR", "MAX_ROWS_PER_PAGE", AND "RESERVEPAGEGAP" TEXT (type2=@t5/@t6/@t7/@t8, seq=4)

   update #tri
   set txt = t.txt  +  ', fillfactor='  +  convert (varchar (3), i.fill_factor)
   from #tri t,
        sysindexes i
   where t.type2       in (@t5, @t6, @t7, @t8)
     and t.seq          = 4
     and i.id           = t.id1
     and i.indid        = t.indid
     and i.fill_factor  > 0

   update #tri
   set txt = t.txt  +  ', max_rows_per_page='  +  convert (varchar (3), i.maxrowsperpage)
   from #tri t,
        sysindexes i
   where t.type2          in (@t5, @t6, @t7, @t8)
     and t.seq             = 4
     and i.id              = t.id1
     and i.indid           = t.indid
     and i.maxrowsperpage  > 0
     and ((i.fill_factor = 0)  or  (i.fill_factor IS NULL))

   insert into #tri
      select 0, t1.type2, 4, t1.id1, t1.indid, t1.id2, t1.id3, t1.id4,
             '   with fillfactor='  +  convert (varchar (3), i.fill_factor)
      from #tri t1,
           sysindexes i
      where t1.type2      in (@t5, @t6, @t7, @t8)
        and i.id           = t1.id1
        and i.indid        = t1.indid
        and i.fill_factor  > 0
        and i.fill_factor IS NOT NULL
        and (not exists (select t2.type2
                         from #tri t2
                         where t2.type2 = t1.type2
                           and t2.id1   = t1.id1
                           and t2.indid = t1.indid
                           and t2.seq   = 4))

   insert into #tri
      select 0, t1.type2, 4, t1.id1, t1.indid, t1.id2, t1.id3, t1.id4,
             '   with max_rows_per_page='  +  convert (varchar (3), i.maxrowsperpage)
      from #tri t1,
           sysindexes i
      where t1.type2         in (@t5, @t6, @t7, @t8)
        and i.id              = t1.id1
        and i.indid           = t1.indid
        and i.maxrowsperpage  > 0
        and ((i.fill_factor = 0)  or  (i.fill_factor IS NULL))
        and (not exists (select t2.type2
                         from #tri t2
                         where t2.type2 = t1.type2
                           and t2.id1   = t1.id1
                           and t2.indid = t1.indid
                           and t2.seq   = 4))

   update #tri
   set txt = t.txt  +  ', reservepagegap='  +  convert (varchar (3), i.res_page_gap)
   from #tri t,
        sysindexes i
   where t.type2        in (@t5, @t6, @t7, @t8)
     and t.seq           = 4
     and i.id            = t.id1
     and i.indid         = t.indid
     and i.res_page_gap  > 0

   insert into #tri
      select 0, t1.type2, 4, t1.id1, t1.indid, t1.id2, t1.id3, t1.id4,
             '   with reservepagegap='  +  convert (varchar (3), i.res_page_gap)
      from #tri t1,
           sysindexes i
      where t1.type2       in (@t5, @t6, @t7, @t8)
        and i.id            = t1.id1
        and i.indid         = t1.indid
        and i.res_page_gap  > 0
        and (not exists (select t2.type2
                         from #tri t2
                         where t2.type2 = t1.type2
                           and t2.id1   = t1.id1
                           and t2.indid = t1.indid
                           and t2.seq   = 4))

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

   insert into #tri
      select 0, @t16, 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.frgndbname IS NULL
        and r.tableid     = o.id
        and o.uid         = user_id ()

   select @cmdstring1   = 'update #tri set txt = t.txt'
                          +  '+'', '''
                          +  '+col_name(tableid, fokey',
          @cmdstring2   = ') from sysreferences r,#tri t'
                          +  ' where t.type2='
                          +  convert (varchar (2), @t16)
                          +  ' and r.tableid=t.id1 and r.reftabid=t.id2 and r.constrid=t.id3'
                          +  ' and valid_name(col_name(tableid, fokey',
          @cmdstring3   = '))>0',
          @cmdstring1qi = 'update #tri set txt = t.txt'
                          +  '+'', "'''
                          +  '+col_name(tableid, fokey',
          @cmdstring2qi = ')+''"'' from sysreferences r,#tri t'
                          +  ' where t.type2='
                          +  convert (varchar (2), @t16)
                          +  ' and r.tableid=t.id1 and r.reftabid=t.id2 and r.constrid=t.id3'
                          +  ' and valid_name(col_name(tableid, fokey',
          @cmdstring3qi = '))=0',
          @sequence     = 1

   while (@sequence < 17)
      begin
         select @numchar = convert (varchar (2), @sequence)

         select @cmdstring = @cmdstring1  +  @numchar  +  @cmdstring2  +  @numchar  +  @cmdstring3

         execute  (@cmdstring)

         select @cmdstring = @cmdstring1qi  +  @numchar  +  @cmdstring2qi  +  @numchar  +  @cmdstring3qi

         execute  (@cmdstring)

         -- UNFORTUNATELY valid_name (NULL) RETURNS 0 INSTEAD OF NULL SO ...

         update #tri
         set txt = substring (txt, 1, charindex (', ""', txt) - 1)
         where type2                   = @t16
           and charindex (', ""', txt) > 0

         select @sequence = @sequence + 1
      end

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

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

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

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

   select @cmdstring1   = 'update #tri set txt=t.txt'
                          +  '+'', '''
                          +  '+col_name(reftabid,refkey',
          @cmdstring2   = ',pmrydbid) from sysreferences r,#tri t'
                          +  ' where t.type2='
                          +  convert (varchar (2), @t16)
                          +  ' and r.tableid=t.id1 and r.reftabid=t.id2 and r.constrid=t.id3 and t.seq=4'
                          +  ' and valid_name(col_name(reftabid,refkey',
          @cmdstring3   = ',pmrydbid))>0',
          @cmdstring1qi = 'update #tri set txt=t.txt'
                          +  '+'', "'''
                          +  '+col_name(reftabid,refkey',
          @cmdstring2qi = ',pmrydbid)+''"'' from sysreferences r,#tri t'
                          +  ' where t.type2='
                          +  convert (varchar (2), @t16)
                          +  ' and r.tableid=t.id1 and r.reftabid=t.id2 and r.constrid=t.id3 and t.seq=4'
                          +  ' and valid_name(col_name(reftabid,refkey',
          @cmdstring3qi = ',pmrydbid))=0',
          @sequence     = 1

   while (@sequence < 17)
      begin
         select @numchar = convert (varchar (2), @sequence)

         select @cmdstring = @cmdstring1 + @numchar + @cmdstring2 + @numchar + @cmdstring3

         execute  (@cmdstring)

         select @cmdstring = @cmdstring1qi + @numchar + @cmdstring2qi + @numchar + @cmdstring3qi

         execute  (@cmdstring)

         -- UNFORTUNATELY valid_name (NULL) RETURNS 0 INSTEAD OF NULL SO ...

         update #tri
         set txt = substring (txt, 1, charindex (', ""', txt) - 1)
         where type2                   = @t16
           and seq                     = 4
           and charindex (', ""', txt) > 0

         select @sequence = @sequence + 1
      end

   update #tri
   set txt = stuff (txt, charindex ('(, ', txt), 3, '(')
   where type2                  = @t16
     and charindex ('(, ', txt) > 1

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

   update #tri
   set txt = txt  +  ')'
   where type2 = @t16

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

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

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

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

   -- GENERATE PROLOG SQL (type2=@t2)

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

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

   -- GENERATE "DROP" SQL (type2=@t3/@t4)

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

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

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

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

   -- GENERATE sp_chgattribute TEXT (type2=@t9/@t10/@t11)

   insert into #tri (type1, type2, seq, id1, indid, txt)
      select 0, @t9, 1, i.id, i.indid,
             'sp_chgattribute '''
             +  o.name
             +  '.'
             +  i.name
             +  ''', ''fillfactor'', '
             +  convert (varchar (3), i.fill_factor)
      from sysindexes i,
           sysobjects o
      where i.id                  > 100
        and i.indid               > 0
        and i.indid               < 254
        and i.fill_factor         > 0
        and o.id                  = i.id
        and o.uid                 = user_id ()
        and (o.sysstat & 15)      = 3
        and (o.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768

   insert into #tri (type1, type2, seq, id1, indid, txt)
      select 0, @t10, 1, i.id, i.indid,
             'sp_chgattribute '''
             +  o.name
             +  '.'
             +  i.name
             +  ''', ''reservepagegap'', '
             +  convert (varchar (3), i.res_page_gap)
      from sysindexes i,
           sysobjects o
      where i.id                  > 100
        and i.indid               > 0
        and i.indid               < 254
        and i.res_page_gap        > 0
        and o.id                  = i.id
        and o.uid                 = user_id ()
        and (o.sysstat & 15)      = 3
        and (o.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768

   insert into #tri (type1, type2, seq, id1, indid, txt)
      select 0, @t11, 1, i.id, i.indid,
             'sp_chgattribute '''
             +  o.name
             +  '.'
             +  i.name
             +  ''', ''max_rows_per_page'', '
             +  convert (varchar (3), i.maxrowsperpage)
      from sysindexes i,
           sysobjects o
      where i.id                  > 100
        and i.indid               > 0
        and i.indid               < 254
        and i.maxrowsperpage      > 0
        and o.id                  = i.id
        and o.uid                 = user_id ()
        and (o.sysstat & 15)      = 3
        and (o.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768

   -- GENERATE sp_bindcache TEXT (type2=@t12)

   insert into #tri (type1, type2, seq, id1, id4, txt)
      select 0, @t12, 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 valid_name (a.char_value)  > 0
        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
        and (o.sysstat2 & 16384)      <> 16384
        and (o.sysstat2 & 32768)      <> 32768

   insert into #tri (type1, type2, seq, id1, id4, txt)
      select 0, @t12, 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 valid_name (a.char_value)  = 0
        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
        and (o.sysstat2 & 16384)      <> 16384
        and (o.sysstat2 & 32768)      <> 32768

   -- GENERATE sp_cachestrategy TEXT (type2=@t13/@t14)

   insert into #tri (type1, type2, seq, id1, id4, txt)
      select 0, @t13, 1, o.id, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', '
             +  o.name
             +  ', '
             +  i.name
             +  ', prefetch, ''off'''
      from sysindexes i,
           sysobjects o
      where i.indid               > 0
        and i.indid               < 254
        and (i.status2 & 16)      = 16
        and o.id                  = i.id
        and o.uid                 = user_id ()
        and (o.sysstat & 15)      = 3
        and (o.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        and valid_name (o.name)   > 0

   insert into #tri (type1, type2, seq, id1, id4, txt)
      select 0, @t13, 1, o.id, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', "'
             +  o.name
             +  '", '
             +  i.name
             +  ', prefetch, ''off'''
      from sysindexes i,
           sysobjects o
      where i.indid               > 0
        and i.indid               < 254
        and (i.status2 & 16)      = 16
        and o.id                  = i.id
        and o.uid                 = user_id ()
        and (o.sysstat & 15)      = 3
        and (o.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        and valid_name (o.name)   = 0

   insert into #tri (type1, type2, seq, id1, id4, txt)
      select 0, @t14, 1, o.id, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', '
             +  o.name
             +  ', '
             +  i.name
             +  ', mru, ''off'''
      from sysindexes i,
           sysobjects o
      where i.indid               > 0
        and i.indid               < 254
        and (i.status2 & 32)      = 32
        and o.id                  = i.id
        and o.uid                 = user_id ()
        and (o.sysstat & 15)      = 3
        and (o.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        and valid_name (o.name)   > 0

   insert into #tri (type1, type2, seq, id1, id4, txt)
      select 0, @t14, 1, o.id, i.indid,
             'sp_cachestrategy '
             +  db_name ()
             +  ', "'
             +  o.name
             +  '", '
             +  i.name
             +  ', mru, ''off'''
      from sysindexes i,
           sysobjects o
      where i.indid               > 0
        and i.indid               < 254
        and (i.status2 & 32)      = 32
        and o.id                  = i.id
        and o.uid                 = user_id ()
        and (o.sysstat & 15)      = 3
        and (o.sysstat2 & 16384) <> 16384
        and (o.sysstat2 & 32768) <> 32768
        and valid_name (o.name)   = 0

   -- GENERATE sp_recompile TEXT (@type2=@t15)

   insert into #tri (type1, type2, seq, id1, txt)
      select 0, @t15, 1, id1, 'sp_recompile '  +  object_name (id1)
      from #tri
      where type2                          in (@t5, @t6, @t7, @t8)
        and seq                             = 1
        and valid_name (object_name (id1))  > 0

   insert into #tri (type1, type2, seq, id1, txt)
      select 0, @t15, 1, id1, 'sp_recompile "'  +  object_name (id1) +  '"'
      from #tri
      where type2                          in (@t5, @t6, @t7, @t8)
        and seq                             = 1
        and valid_name (object_name (id1))  = 0

   -- GENERATE THE FINAL SQL

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

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

         -- SET type1 VALUES

         update #tri
         set type1 = type2

         update #tri
         set type1 = 5
         where type2 in (@t5, @t6, @t7, @t8, @t9, @t10, @t11, @t12, @t13, @t15)

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

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

         -- GENERATE SECTION COMMENT HEADERS

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

         insert into #tri (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 #tri t
                           where t.type1 = usv.number
                             and t.seq   = 1))

         -- GENERATE TABLE COMMENT HEADERS

         insert into #tri (type1, type2, seq, id1, txt)
            select @t5,
                   min (type2),
                   -1,
                   id1,
                   replicate ('-', 22)
                   +  ' REINDEX '
                   +  object_name (id1)
                   +  ' '
                   +  replicate ('-', 46 - char_length (object_name (id1)))
            from #tri
            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 #tri
            select type1, type2, 6, id1, indid, id2, id3, id4, 'go'
            from #tri
            where seq = 1

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

         select @maxtxtlen = max (char_length (txt))
         from #tri

         if (@textwidth < @maxtxtlen)
            begin
               print ' '

               print '    THE REINDEX TEXT (%1!) IS WIDER THAN THE OUTPUT WIDTH (%2!).',
                          @maxtxtlen, @textwidth

               print ' '

               print '    ADJUST THE @txt AND #triprint.text SIZE VALUES AND RECOMPILE sp_textreindex.'

               print ' '

               select @retval = 1
            end
         else
            begin
               insert into #triprint (text)
                  select distinct txt
                  from #tri
                  order by type1,
                           object_name (id1),
                           type2,
                           indid,
                           object_name (id2),
                           object_name (id3),
                           id4,
                           seq

               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 1
                  end

               close text_cursor

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

         print ' '

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

         print ' '
      end

   return @retval
go
sp_addcomment sp_textreindex, 'Generates SQL to reindex allpages user tables'
go
sp_procxmode sp_textreindex, anymode
go

dump transaction sybsystemprocs with truncate_only
go
