use sybsystemprocs
go
drop procedure sp_tgr                   -- In 11031A release
go
drop procedure sp_ts                    -- In 11031A release
go
drop procedure sp_textdroplogin_generate
go
drop procedure sp_textgroupsright_print
go
drop procedure sp_textselectivity_print
go
drop procedure sp_textdroplogin
go
drop procedure sp_textgroupsright
go
drop procedure sp_textreconstrain
go
drop procedure sp_textrecluster
go
drop procedure sp_textselectivity
go
drop procedure sp_textwhoiswhere        -- In 11031B release
go

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

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

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

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

set quoted_identifier on
go

create procedure sp_textdroplogin_generate (@SUID  int)  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set quoted_identifier             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_textdroplogin_generate> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   print ' '

   if ((exists (select *
                from sysalternates
                where suid = @SUID))  or
       (exists (select *
                from sysusers
                where suid = @SUID)))
      begin
         insert into #textdellgn
            select 'use ' + db_name ()

         insert into #textdellgn
            values ('go')

         if ((exists (select o.id
                      from sysobjects o,
                           sysusers u
                      where u.suid = @SUID
                        and o.uid  = u.uid))  or
             (exists (select t.usertype
                      from systypes t,
                           sysusers u
                      where u.suid     = @SUID
                        and t.uid      = u.uid
                        and t.usertype > 99)))
            begin
               insert into #textdellgn
                  values ('--')

               insert into #textdellgn
                  select '--  WARNING: '  +  name  +  ' OWNS OBJECTS.   USE sp_ownedby.'
                  from sysusers
                  where suid = @SUID

               insert into #textdellgn
                  values ('--')

               if (exists (select *
                           from sysalternates
                           where altsuid = @SUID))
                  begin
                     insert into #textdellgn
                        values ('--')

                     insert into #textdellgn
                        values ('--  WARNING: ALIASES EXIST AND WILL BE DROPPED.')

                     insert into #textdellgn
                        values ('--')
                  end
            end
         else if (exists (select *
                          from sysalternates
                          where suid = @SUID))
            begin
               insert into #textdellgn
                  select 'sp_dropalias ' + suser_name (@SUID)

               insert into #textdellgn
                  values ('go')
            end
         else if (exists (select *
                          from sysusers
                          where suid = @SUID))
            begin
               if (exists (select *
                           from sysalternates
                           where altsuid = @SUID))
                  begin
                     insert into #textdellgn
                        values ('--')

                     insert into #textdellgn
                        values ('--  WARNING: ALIASES EXIST AND WILL BE DROPPED.')

                     insert into #textdellgn
                        values ('--')
                  end

               insert into #textdellgn
                  select 'sp_dropuser '  +  name
                  from sysusers
                  where suid = @SUID

               insert into #textdellgn
                  values ('go')
            end
      end

   if (db_id () = 1)
      begin
         if (not exists (select *
                         from #textdellgn
                         where text = 'use master'))
            begin
               insert into #textdellgn
                  values ('use master')

               insert into #textdellgn
                  values ('go')
            end

         if (exists (select *
                     from master.dbo.sysdatabases
                     where suid = @SUID))
            begin
               insert into #textdellgn
                  values ('--')

               insert into #textdellgn
                  values ('--  WARNING: '  +  suser_name (@SUID)  +  ' OWNS AT LEAST ONE DATABASE.')

               insert into #textdellgn
                  values ('--')
            end
         else
            begin
               insert into #textdellgn
                  select 'sp_droplogin ' + suser_name (@SUID)

               insert into #textdellgn
                  values ('go')
            end
      end
go
sp_procxmode sp_textdroplogin_generate, anymode
go

create proc sp_textgroupsright_print as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   declare @groupcnt  varchar (7)

   print ' '

   delete #tgr
   from #tgr t1
   where not exists (select t2.*
                     from #tgr t2
                     where t2.groupname  = t1.groupname
                       and t2.db        <> t1.db)

   select groupname = t1.groupname,
          db        = t1.db,
          username  = t1.username,
          sysname   = l.name,
          sort      = t1.groupname + ' ' + t1.db + ' ' + t1.username
   into #tgur
   from #tgr t1,
        master.dbo.syslogins l
   where t1.suid *= l.suid
     and t1.groupname in (select distinct t2.groupname
                          from #tgr t2
                          where exists (select t3.*
                                        from #tgr t3
                                        where t3.groupname  = t2.groupname
                                          and t3.db        <> t2.db
                                          and not exists (select t4.*
                                                          from #tgr t4
                                                          where t4.groupname = t3.groupname
                                                            and t4.db        = t3.db
                                                            and t4.suid      = t2.suid)))

   if (exists (select *
               from #tgur))
      begin
         select distinct 'CONSISTENT SERVER GROUPS' = #tgr.groupname
         from #tgr
         where not exists (select #tgur.*
                           from #tgur
                           where #tgur.groupname = #tgr.groupname)
         order by #tgr.groupname

         print ' '

         select @groupcnt = ltrim (substring (convert (char (10),
                                                       convert (money,
                                                                count (distinct groupname)),
                                                                1),
                                              1,
                                              7))
         from #tgur

         print '    THERE ARE %1! INCONSISTENT GROUPS.', @groupcnt

         print ' '

         insert into #tgur
            select ' ', ' ', ' ', ' ', min (sort)
            from #tgur
            group by groupname

         delete from #tgur
         where groupname = ' '
           and sort      = (select min (sort)
                            from #tgur)

         update #tgur
         set username = substring (username + ' (' + sysname + ')', 1, 30)
         where username <> sysname

         select 'GROUP'    = substring (groupname, 1, 22),
                'DATABASE' = substring (db, 1, 23),
                USERNAME   = username
         from #tgur
         order by sort, groupname
      end
   else
      begin
         print ' '

         print '    ALL GROUPS ARE CONSISTENT ACROSS ALL DATABASES.'
      end
go

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

   declare @maxtablen  tinyint

   print ' '

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

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

   update #selectivity
   set keytext = index_col (object_name (id), indid, 1, uid)
   where keycnt = 1

   update #selectivity
   set keytext = substring (index_col (object_name (id), indid, 1, uid)
                            + ', '
                            + index_col (object_name (id), indid, 2, uid),
                            1,
                            42)
   where keycnt = 2

   update #selectivity
   set keytext = substring (index_col (object_name (id), indid, 1, uid)
                            + ', '
                            + index_col (object_name (id), indid, 2, uid)
                            + ', '
                            + index_col (object_name (id), indid, 3, uid),
                            1,
                            42)
   where keycnt = 3

   update #selectivity
   set keytext = substring (index_col (object_name (id), indid, 1, uid)
                            + ', '
                            + index_col (object_name (id), indid, 2, uid)
                            + ', '
                            + index_col (object_name (id), indid, 3, uid)
                            + ', '
                            + index_col (object_name (id), indid, 4, uid),
                            1,
                            42)
   where keycnt = 4

   update #selectivity
   set keytext = substring (index_col (object_name (id), indid, 1, uid)
                            + ', '
                            + index_col (object_name (id), indid, 2, uid)
                            + ', '
                            + index_col (object_name (id), indid, 3, uid)
                            + ', '
                            + index_col (object_name (id), indid, 4, uid)
                            + ', '
                            + index_col (object_name (id), indid, 5, uid),
                            1,
                            42)
   where keycnt = 5

   update #selectivity
   set keytext = substring (index_col (object_name (id), indid, 1, uid)
                            + ', '
                            + index_col (object_name (id), indid, 2, uid)
                            + ', '
                            + index_col (object_name (id), indid, 3, uid)
                            + ', '
                            + index_col (object_name (id), indid, 4, uid)
                            + ', '
                            + index_col (object_name (id), indid, 5, uid)
                            + ', '
                            + index_col (object_name (id), indid, 6, uid),
                            1,
                            42)
   where keycnt = 6

   update #selectivity
   set keytext = substring (index_col (object_name (id), indid, 1, uid)
                            + ', '
                            + index_col (object_name (id), indid, 2, uid)
                            + ', '
                            + index_col (object_name (id), indid, 3, uid)
                            + ', '
                            + index_col (object_name (id), indid, 4, uid)
                            + ', '
                            + index_col (object_name (id), indid, 5, uid)
                            + ', '
                            + index_col (object_name (id), indid, 6, uid)
                            + ', '
                            + index_col (object_name (id), indid, 7, uid),
                            1,
                            42)
   where keycnt = 7

   update #selectivity
   set keytext = substring (index_col (object_name (id), indid, 1, uid)
                            + ', '
                            + index_col (object_name (id), indid, 2, uid)
                            + ', '
                            + index_col (object_name (id), indid, 3, uid)
                            + ', '
                            + index_col (object_name (id), indid, 4, uid)
                            + ', '
                            + index_col (object_name (id), indid, 5, uid)
                            + ', '
                            + index_col (object_name (id), indid, 6, uid)
                            + ', '
                            + index_col (object_name (id), indid, 7, uid)
                            + ', '
                            + index_col (object_name (id), indid, 8, uid),
                            1,
                            42)
   where keycnt = 8

   select @maxtablen = max (char_length (object_name (id)))
   from #selectivity

   print '                                                           INDEX  DISTRIBUTION'

   if (@maxtablen > 14)
      begin
         select 'TABLE'       = substring (object_name (id), 1, 23),
                'KEYS'        = substring (keytext, 1, 33),
                'SEL...'      = substring (convert (char (8),
                                                    convert (money,
                                                             selectivity),
                                                    1),
                                           1,
                                           7),
                'SELECTIVITY' = substring (convert (char (14),
                                                    convert (money,
                                                             dselectivity),
                                                    1),
                                           1,
                                           11)
         from #selectivity
         order by object_name (id), indid
      end
   else
      begin
         select 'TABLE'       = substring (object_name (id), 1, 14),
                'KEYS'        = keytext,
                'SEL...'      = substring (convert (char (8),
                                                    convert (money,
                                                             selectivity),
                                                    1),
                                           1,
                                           7),
                'SELECTIVITY' = substring (convert (char (14),
                                                    convert (money,
                                                             dselectivity),
                                                    1),
                                           1,
                                           11)
         from #selectivity
         order by object_name (id), indid
      end
go

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

create procedure sp_textdroplogin (@LOGIN varchar (30))  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set quoted_identifier             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_textdroplogin> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @maxdbname     varchar (30),
           @suidchar      varchar (10)

   print ' '

   select @suidchar = convert (varchar (10), suid)
   from master.dbo.syslogins
   where name = @LOGIN

   create table #textdroplogin
      (dbid    smallint      not null,
       db      varchar (30)  not null,
       seq     int           not null,
       txt     varchar (76)  null)

   if (@suidchar IS NULL)
      begin
         print '    LOGIN <%1!> DOES NOT EXIST.', @LOGIN

         print ' '
      end
   else
      begin
         if (exists (select *
                     from master.dbo.sysdatabases
                     where suid = suser_id (@LOGIN)))
            begin
               print '    WARNING: LOGIN <%1!> OWNS DATABASES.', @LOGIN

               print ' '
            end

         insert into #textdroplogin
            values (9999, ' ', -3, 'create table #textdellgn')

         insert into #textdroplogin
            values (9999, ' ', -2, '   (text      varchar (60))')

         insert into #textdroplogin
            values (9999, ' ', -1, 'go')

         select @maxdbname = max (name)
         from master.dbo.sysdatabases

         insert into #textdroplogin
            select dbid, name, 1, 'use ' + name
            from master.dbo.sysdatabases

         insert into #textdroplogin
            select dbid, db, 2, 'go'
            from #textdroplogin
            where seq = 1

         insert into #textdroplogin
            select dbid, db, 3,
                   'exec sp_textdroplogin_generate '  +   @suidchar
            from #textdroplogin
            where seq = 1

         insert into #textdroplogin
            select dbid, db, 4, 'go'
            from #textdroplogin
            where seq = 1

         insert into #textdroplogin
            values (0, @maxdbname, 5, 'select *')

         insert into #textdroplogin
            values (0, @maxdbname, 6, 'from #textdellgn')

         insert into #textdroplogin
            values (0, @maxdbname, 7, 'go')

         insert into #textdroplogin
            values (0, @maxdbname, 8, 'drop table #textdellgn')

         insert into #textdroplogin
            values (0, @maxdbname, 9, 'go')

         update #textdroplogin
         set dbid = 2
         where dbid > 1

         select 'DROP ALIAS/USER/LOGIN SQL' = txt
         from #textdroplogin
         order by dbid desc, db, seq

         drop table #textdroplogin
      end
go
sp_addcomment sp_textdroplogin, 16, 'Generates SQL to drop alias/user/login for LOGIN'
go
sp_procxmode sp_textdroplogin, anymode
go

create procedure sp_textgroupsright as
   set ansinull                      on
   set string_rtruncation            on

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

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

         print ' '

         return 1
      end

   declare @maxdbname  varchar (30)

   create table #textgroupsright
      (db      varchar (30)  not null,
       seq     int           not null,
       txt     varchar (76)  null)

   insert into #textgroupsright
      values (' ', -6, 'create table #tgr')

   insert into #textgroupsright
      values (' ', -5, '   (db        varchar (30)  not null,')

   insert into #textgroupsright
      values (' ', -4, '    suid      smallint      not null,')

   insert into #textgroupsright
      values (' ', -3, '    username  varchar (30)  not null,')

   insert into #textgroupsright
      values (' ', -2, '    groupname varchar (30)  not null)')

   insert into #textgroupsright
      values (' ', -1, 'go')

   select @maxdbname = max (name)
   from master.dbo.sysdatabases

   insert into #textgroupsright
      select name, 1, 'use ' + name
      from master.dbo.sysdatabases

   insert into #textgroupsright
      select db, 2, 'go'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 11,
             'insert into #tgr'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 12,
             '   select db_name (), uu.suid, uu.name, ug.name'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 13,
             '   from sysusers ug, sysusers uu'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 14,
             '   where ug.uid = uu.gid'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 15,
             '     and ((uu.uid < 16384)  and  (uu.uid > 0))'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 16,
             '     and ug.uid > 16389'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 17, 'go'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 21,
             'insert into #tgr'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 22,
             '   select db_name (), -1, "(none)", ug.name'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 23,
             '   from sysusers ug'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 24,
             '   where ug.uid > 16389'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 25,
             '     and not exists (select uu.*'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 26,
             '                     from sysusers uu'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 27,
             '                     where uu.uid < 16384'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 28,
             '                       and ug.uid = uu.gid)'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      select db, 29, 'go'
      from #textgroupsright
      where seq = 1

   insert into #textgroupsright
      values (@maxdbname, 31, 'exec sp_textgroupsright_print')

   insert into #textgroupsright
      values (@maxdbname, 32, 'go')

   select 'CHECK SERVER GROUPS SQL' = txt
   from #textgroupsright
   order by db, seq
go
sp_addcomment sp_textgroupsright, 12, 'Generates SQL to check server group consistencies'
go
sp_procxmode sp_textgroupsright, anymode
go

create procedure sp_textreconstrain (@TABLE  varchar (30)) as
   -- type/seq meanings
   --    0/1 = use <database>
   --    1/1 = alter table ...
   --    1/2 =    drop constraint ...
   --    2/1 = (slot for user code)
   --    3/1 = alter table ...
   --    3/2 =    add constraint ...
   --    3/3 =    foreign key ...
   --    3/4 =    references ...
   --    N/6 = go

   set ansinull                      on
   set flushmessage                  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 @sequence  int,
           @tabid     int,
           @t0        tinyint,
           @t1        tinyint,
           @t2        tinyint,
           @t3        tinyint

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

   select @t0 = 0,
          @t1 = 1,
          @t2 = 2,
          @t3 = 3

   print ' '

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

   if (@tabid IS NULL)
      begin
         print '    USER TABLE <%1!> DOES NOT EXIST.', @TABLE

         print ' '
      end
   else
      begin
         -- ARE THERE ANY PRIMARY/FOREIGN KEYS IN ANOTHER DATABASE?

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

               print ' '

               print '    USE sp_refkeysx.'

               print ' '
            end

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

               print ' '

               print '    USE sp_refkeysx.'

               print ' '
            end

         -- GENERATE COLUMN TEXT FOR ADDING THE CONSTRAINTS.

         insert into #textreconstrain
            select @t3, 1, tableid, reftabid, constrid, pmrydbid,
                   'alter table '  +  (object_name (tableid))
            from sysreferences
            where frgndbid = db_id ()
              and pmrydbid = db_id ()
              and ((tableid = @tabid)  or  (reftabid = @tabid))

         insert into #textreconstrain
            select @t3, 2, id1, id2, id3, id4,
                   '   add constraint '  +  (object_name (id3))
            from #textreconstrain
            where seq = 1

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

         update #textreconstrain
         set txt = t.txt + col_name (tableid, fokey1) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 0

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey2) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 1

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey3) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 2

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey4) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 3

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey5) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 4

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey6) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 5

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey7) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 6

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey8) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 7

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey9) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 8

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey10) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 9

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey11) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 10

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey12) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 11

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey13) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 12

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey14) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 13

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey15) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 14

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (tableid, fokey16) + ','
         from sysreferences r,
              #textreconstrain t
         where t.type     = @t3
           and t.seq      = 3
           and r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and r.keycnt   > 15

         insert into #textreconstrain
            select @t3, 4, id1, id2, id3, id4,
                   '   references  '  +  object_name (id2)  +  ' ('
            from #textreconstrain
            where seq = 1

         update #textreconstrain
         set txt = t.txt + col_name (reftabid, refkey1, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 0

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey2, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 1

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey3, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 2

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey4, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 3

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey5, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 4

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey6, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 5

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey7, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 6

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey8, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 7

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey9, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 8

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey10, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 9

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey11, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 10

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey12, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 11

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey13, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 12

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey14, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 13

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey15, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 14

         update #textreconstrain
         set txt = t.txt + ' ' + col_name (reftabid, refkey16, pmrydbid) + ','
         from sysreferences r,
              #textreconstrain t
         where r.tableid  = t.id1
           and r.reftabid = t.id2
           and r.constrid = t.id3
           and t.type     = @t3
           and t.seq      = 4
           and r.keycnt   > 15

         update #textreconstrain
         set txt = substring (txt, 1, char_length (txt) - 1) + ')'
         where type  = @t3
           and seq  in (3, 4)

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

         insert into #textreconstrain
            select @t1, 1, id1, id2, id3, id4, txt
            from #textreconstrain
            where type = @t3
              and seq  = 1

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

         -- GENERATE THE FINAL SQL

         if (exists (select *
                     from #textreconstrain
                     where type = @t3))
            begin
               insert into #textreconstrain
                  select type, 6, id1, id2, id3, id4, 'go'
                  from #textreconstrain
                  where seq = 1

               insert into #textreconstrain
                  values (2, 1, 0, 0, 0, 0, '-- ')

               insert into #textreconstrain
                  values (2, 2, 0, 0, 0, 0, '--  USER SUPPLIED CODE')

               insert into #textreconstrain
                  values (2, 3, 0, 0, 0, 0, '-- ')

               select 'RECONSTRAIN TEXT' = substring (txt, 1, 127)
               from #textreconstrain
               order by type,
                        object_name (id1),
                        object_name (id3),
                        seq
            end
         else
            begin
               print ' '

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

               print ' '
            end
      end
go
sp_addcomment sp_textreconstrain, 11, 'Generates SQL to drop/add referential constraints for TABLE'
go
sp_procxmode sp_textreconstrain, anymode
go

create procedure sp_textrecluster (@FILLFACTOR  tinyint = NULL) as

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

   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @ff        varchar ( 3),
           @sequence  int,
           @t0        tinyint,
           @t1        tinyint,
           @t2        tinyint,
           @t3        tinyint,
           @t4        tinyint,
           @t5        tinyint,
           @t6        tinyint,
           @t7        tinyint,
           @t8        tinyint,
           @t9        tinyint,
           @t10       tinyint,
           @username  varchar (30)

   create table #textrecluster
      (type    smallint                  not null,
       seq     int                       not null,
       id1     int                       not null,         -- tableid
       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))

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

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

         print ' '

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

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

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

         print ' '

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

         print ' '

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

         print ' '
      end

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

         print ' '

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

         print ' '

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

         print ' '
      end

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

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

         print ' '

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

   -- GENERATE COLUMN TEXT FOR CREATING CLUSTERED INDEXES/CONSTRAINTS

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

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

   insert into #textrecluster (type, seq, id1, txt)
      select @t5, 2, id1, '   on ' + object_name (id1) + ' ('
      from #textrecluster

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

   insert into #textrecluster (type, seq, id1, txt)
      values (@t1, 1, 0, 'sp_freespace')

   insert into #textrecluster (type, seq, id1, txt)
      select @t6, 3, i.id, '      ('
      from sysindexes i,
           sysobjects o
      where o.uid            = user_id ()
        and (o.sysstat & 15) = 3
        and o.id             = i.id
        and i.indid          = 1
        and (i.status & 16)  = 16
        and (i.status2 & 2)  = 2

   select @sequence = 1

   while (@sequence < 17)
      begin
         update #textrecluster
         set txt = t.txt
                   + ' ' + index_col (object_name (i.id), 1, @sequence) + ','
         from sysindexes i,
              #textrecluster t
         where t.seq    > 1
           and t.id1    = i.id
           and i.indid  = 1
           and i.keycnt > @sequence
           and index_col (object_name (i.id), 1, @sequence) IS NOT NULL

         update #textrecluster
         set txt = t.txt
                   + ' ' + index_col (object_name (i.id), 1, @sequence) + ')'
         from sysindexes i,
              #textrecluster t
         where t.seq    > 1
           and t.id1    = i.id
           and i.indid  = 1
           and i.keycnt = @sequence

         select @sequence = @sequence + 1
      end

   update #textrecluster
   set txt = stuff (txt, charindex ('( ', txt), 2, '(')
   where type                  = @t5
     and charindex ('( ', txt) > 0

   update #textrecluster
   set txt = '      (' + substring (txt, charindex ('( ', txt) + 2, 255)
   where type                  = @t6
     and charindex ('( ', txt) > 0

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

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

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

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

   insert into #textrecluster
      select @t5, 4, t.id1, t.id2, t.id3, t.id4, '   on ' + s.name
   from sysindexes i,
        syssegments s,
        #textrecluster t
   where t.type    = @t5
     and t.seq     = 3
     and i.id      = t.id1
     and i.indid   = 1
     and s.segment = i.segment

   insert into #textrecluster
      select @t6, 5, t.id1, t.id2, t.id3, t.id4, '   on ' + s.name
   from sysindexes i,
        syssegments s,
        #textrecluster t
   where t.type    = @t6
     and t.seq     = 3
     and i.id      = t.id1
     and i.indid   = 1
     and s.segment = i.segment

   update #textrecluster
   set txt = stuff (txt, charindex ('default', txt), 7, '''default''')
   where charindex ('default', txt) > 0
     and ((type = @t5  and  seq = 4)  or  (type = @t6  and  seq = 5))

   if (@ff IS NOT NULL)
      begin
         update #textrecluster
         set txt = txt + ', fillfactor = ' + @ff
         where type = @t5
           and seq  = 3

         insert into #textrecluster
            select @t6, 4, id1, id2, id3, id4, '   with fillfactor = ' + @ff
         from #textrecluster
         where type = @t6
           and seq  = 3
      end

   insert into #textrecluster (type, seq, id1, txt)
      select @t6, 1, id1, 'alter table ' + object_name (id1)
      from #textrecluster
      where type = @t6
        and seq  = 3

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

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

   -- GENERATE COLUMN TEXT FOR CREATING REFERENTIAL CONSTRAINTS

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

   update #textrecluster
   set txt = t.txt + col_name (tableid, fokey1) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 0

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey2) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 1

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey3) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 2

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey4) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 3

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey5) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 4

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey6) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 5

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey7) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 6

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey8) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 7

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey9) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 8

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey10) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 9

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey11) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 10

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey12) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 11

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey13) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 12

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey14) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 13

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey15) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 14

   update #textrecluster
   set txt = t.txt + ' ' + col_name (tableid, fokey16) + ','
   from sysreferences r,
        #textrecluster t
   where t.type     = @t7
     and r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and r.keycnt   > 15

   insert into #textrecluster
      select @t7, 4, id1, id2, id3, id4,
             '      references  ' + object_name (id2) + ' ('
      from #textrecluster
      where type = @t7
        and id4  = db_id ()

   insert into #textrecluster
      select @t7, 4, id1, id2, id3, id4,
             '      references  ' + db_name (id4) + '..' + object_name (id2, id4) + ' ('
      from #textrecluster
      where type  = @t7
        and id4  <> db_id ()

   update #textrecluster
   set txt = t.txt + col_name (reftabid, refkey1, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 0

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey2, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 1

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey3, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 2

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey4, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 3

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey5, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 4

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey6, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 5

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey7, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 6

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey8, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 7

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey9, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 8

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey10, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 9

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey11, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 10

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey12, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 11

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey13, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 12

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey14, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 13

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey15, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 14

   update #textrecluster
   set txt = t.txt + ' ' + col_name (reftabid, refkey16, pmrydbid) + ','
   from sysreferences r,
        #textrecluster t
   where r.tableid  = t.id1
     and r.reftabid = t.id2
     and r.constrid = t.id3
     and t.type     = @t7
     and t.seq      = 4
     and r.keycnt   > 15

   update #textrecluster
   set txt = substring (txt, 1, char_length (txt) - 1) + ')'
   where type = @t7

   insert into #textrecluster
      select @t7, 1, id1, id2, id3, id4, 'alter table ' + object_name (id1)
      from #textrecluster
      where type = @t7
        and seq  = 3

   insert into #textrecluster
      select @t7, 2, id1, id2, id3, id4,
             '   add constraint ' + object_name (id3)
      from #textrecluster
      where type = @t7
        and seq  = 1

   -- GENERATE PROLOGUE SQL FOR TYPES 1-4.

   insert into #textrecluster
      select @t2, 1, id1, id2, id3, id4, txt
      from #textrecluster
      where type = @t7
        and seq  = 1

   insert into #textrecluster
      select @t2, 2, id1, id2, id3, id4, '   drop ' + substring (txt, 8, 255)
      from #textrecluster
      where type = @t7
        and seq  = 2

   insert into #textrecluster (type, seq, id1, txt)
      select @t3, 1, i.id, 'drop index ' + object_name (i.id) + '.' + i.name
      from sysindexes i,
           #textrecluster t
      where t.type             = @t5
        and t.seq              = 1
        and t.id1              = i.id
        and i.indid            = 1
        and (i.status & 16)    = 16
        and (i.status & 2048) <> 2048

   insert into #textrecluster (type, seq, id1, txt)
      select @t4, 1, id1, txt
      from #textrecluster
      where type = @t6
        and seq  = 1

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

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

   -- GENERATE sp_bindcache TEXT


   insert into #textrecluster (type, seq, id1, id4, txt)
      select @t8, 1, a.object, i.indid,
             'sp_bindcache '''  + rtrim (a.char_value)
             +  ''', '  +  db_name ()
             +  ', '  +  object_name (a.object)
             +  ', '  +  i.name
      from sysattributes a,
           sysindexes i,
           sysobjects o
      where a.class       = 3
        and a.attribute   = 0
        and a.object_type = 'I'
        and i.id          = a.object
        and i.indid       = a.object_info1
        and i.indid       < 255
        and o.id          = i.id
        and o.uid         = user_id ()

   -- GENERATE sp_recompile AND sp_freespace TEXT

   insert into #textrecluster (type, seq, id1, txt)
      select @t9, 1, id1, 'sp_recompile ' + object_name (id1)
      from #textrecluster
      where type in (@t5, @t6)
        and seq   = 1

   insert into #textrecluster (type, seq, id1, txt)
      values (@t10, 1, 0, 'sp_freespace')

   -- GENERATE THE FINAL SQL

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

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

         select 'RECLUSTER TEXT' = substring (txt, 1, 127)
         from #textrecluster
         order by type,
                  object_name (id1),
                  object_name (id2),
                  object_name (id3),
                  id4,
                  seq
      end
   else
      begin
         select @username = user_name ()

         print ' '

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

         print ' '
      end
go
sp_addcomment sp_textrecluster, 13, 'Generates SQL to recluster indexes with optional FILLFACTOR'
go
sp_procxmode sp_textrecluster, anymode
go

create procedure sp_textselectivity as
   set ansinull                      on
   set string_rtruncation            on

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

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

         print ' '

         return 1
      end

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

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

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

   -- CREATE THE REST OF THE ABOVE insert STATEMENTS.

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

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

   insert into #textsel
      select id, indid, uid, 4, keycnt,
             '   group by ' + index_col (object_name (id), indid, 1, uid)
      from #textsel
      where seq = 2
        and ((indid = 1 and keycnt = 1)  or  (indid > 1 and keycnt = 2))

   insert into #textsel
      select id, indid, uid, 4, keycnt,
             '   group by ' + index_col (object_name (id), indid, 1, uid)
             + ', ' + index_col (object_name (id), indid, 2, uid)
      from #textsel
      where seq = 2
        and ((indid = 1 and keycnt = 2)  or  (indid > 1 and keycnt = 3))

   insert into #textsel
      select id, indid, uid, 4, keycnt,
             '   group by ' + index_col (object_name (id), indid, 1, uid)
             + ', ' + index_col (object_name (id), indid, 2, uid)
             + ', ' + index_col (object_name (id), indid, 3, uid)
      from #textsel
      where seq = 2
        and ((indid = 1 and keycnt = 3)  or  (indid > 1 and keycnt = 4))

   insert into #textsel
      select id, indid, uid, 4, keycnt,
             '   group by ' + index_col (object_name (id), indid, 1, uid)
             + ', ' + index_col (object_name (id), indid, 2, uid)
             + ', ' + index_col (object_name (id), indid, 3, uid)
             + ', ' + index_col (object_name (id), indid, 4, uid)
      from #textsel
      where seq = 2
        and ((indid = 1 and keycnt = 4)  or  (indid > 1 and keycnt = 5))

   insert into #textsel
      select id, indid, uid, 4, keycnt,
             '   group by ' + index_col (object_name (id), indid, 1, uid)
             + ', ' + index_col (object_name (id), indid, 2, uid)
             + ', ' + index_col (object_name (id), indid, 3, uid)
             + ', ' + index_col (object_name (id), indid, 4, uid)
             + ', ' + index_col (object_name (id), indid, 5, uid)
      from #textsel
      where seq = 2
        and ((indid = 1 and keycnt = 5)  or  (indid > 1 and keycnt = 6))

   insert into #textsel
      select id, indid, uid, 4, keycnt,
             '   group by ' + index_col (object_name (id), indid, 1, uid)
             + ', ' + index_col (object_name (id), indid, 2, uid)
             + ', ' + index_col (object_name (id), indid, 3, uid)
             + ', ' + index_col (object_name (id), indid, 4, uid)
             + ', ' + index_col (object_name (id), indid, 5, uid)
             + ', ' + index_col (object_name (id), indid, 6, uid)
      from #textsel
      where seq = 2
        and ((indid = 1 and keycnt = 6)  or  (indid > 1 and keycnt = 7))

   insert into #textsel
      select id, indid, uid, 4, keycnt,
             '   group by ' + index_col (object_name (id), indid, 1, uid)
             + ', ' + index_col (object_name (id), indid, 2, uid)
             + ', ' + index_col (object_name (id), indid, 3, uid)
             + ', ' + index_col (object_name (id), indid, 4, uid)
             + ', ' + index_col (object_name (id), indid, 5, uid)
             + ', ' + index_col (object_name (id), indid, 6, uid)
             + ', ' + index_col (object_name (id), indid, 7, uid)
      from #textsel
      where seq = 2
        and ((indid = 1 and keycnt = 7)  or  (indid > 1 and keycnt = 8))

   insert into #textsel
      select id, indid, uid, 4, keycnt,
             '   group by ' + index_col (object_name (id), indid, 1, uid)
             + ', ' + index_col (object_name (id), indid, 2, uid)
             + ', ' + index_col (object_name (id), indid, 3, uid)
             + ', ' + index_col (object_name (id), indid, 4, uid)
             + ', ' + index_col (object_name (id), indid, 5, uid)
             + ', ' + index_col (object_name (id), indid, 6, uid)
             + ', ' + index_col (object_name (id), indid, 7, uid)
             + ', ' + index_col (object_name (id), indid, 8, uid)
      from #textsel
      where seq = 2
        and ((indid = 1 and keycnt = 8)  or  (indid > 1 and keycnt = 9))

   -- CREATE THE SQL FOR COMPUTING DISTRIBUTION PAGE SELECTIVITY.

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

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

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

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

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

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

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

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

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

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

   -- REPLACE SELECTIVITY SQL FOR UNIQUE INDEXES.

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

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

   -- CREATE THE PROLOG STATEMENTS.

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

   insert into #textsel
      values (0, 0, 0, 1, 0, 'create table #selectivity')

   insert into #textsel
      values (0, 0, 0, 2, 0, '   (id            int               not null,')

   insert into #textsel
      values (0, 0, 0, 3, 0, '    indid         tinyint           not null,')

   insert into #textsel
      values (0, 0, 0, 4, 0, '    uid           smallint          not null,')

   insert into #textsel
      values (0, 0, 0, 5, 0, '    keycnt        tinyint           not null,')

   insert into #textsel
      values (0, 0, 0, 6, 0, '    keytext       char (42)         not null,')

   insert into #textsel
      values (0, 0, 0, 7, 0, '    selectivity   double precision  not null,')

   insert into #textsel
      values (0, 0, 0, 8, 0, '    dselectivity  int               not null)')

   -- CREATE THE THE EPILOG STATEMENTS.

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

   -- CREATE THE go STATEMENTS.

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

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

   -- select THE FINISHED SQL.

   select 'SELECTIVITY SQL' = txt
   from #textsel
   order by id, indid, seq
go
sp_addcomment sp_textselectivity, 13, 'Generates SQL to report index selectivities'
go
sp_procxmode sp_textselectivity, anymode
go

dump transaction sybsystemprocs with truncate_only
go
