use sybsystemprocs
go

drop procedure sp_checks
go
drop procedure sp_columns1
go
drop procedure sp_columns2
go
drop procedure sp_common
go
drop procedure sp_datatype_decnum
go
drop procedure sp_datatype_dp
go
drop procedure sp_datatype_int
go
drop procedure sp_datatype_float
go
drop procedure sp_datatype_real
go
drop procedure sp_datatype_smallint
go
drop procedure sp_datatype_tinyint
go
drop procedure sp_datatype_usertype
go
drop procedure sp_datatype
go
drop procedure sp_dbdiff_load
go
drop procedure sp_dbdiff_print
go
drop procedure sp_dbdiff
go
drop procedure sp_defaultcols
go
drop procedure sp_dependx
go
drop procedure sp_depend1
go
drop procedure sp_depend2
go
drop procedure sp_depend3
go
drop procedure sp_loginwhere_db
go
drop procedure sp_loginwhere
go
drop procedure sp_maxdepend               -- In 1151A release
go
drop procedure sp_ownedby
go
drop procedure sp_refkeys
go
drop procedure sp_refkeysx
go
drop procedure sp_ruleuse
go
drop procedure sp_typeagg
go
drop procedure sp_uses
go

set quoted_identifier on
go

create procedure sp_checks (@TABLE varchar (63) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @CR          char (1),
           @LF          char (1),
           @chkcount    varchar (7),
           @maxnamelen  tinyint,
           @space1      char (1),
           @space3      char (3),
           @tabcount    varchar (7),
           @tableid     int,
           @textlen     tinyint,
           @tcstring    char (18)

   select @CR       = char (13),
          @LF       = char (10),
          @space1   = space (1),
          @space3   = space (3),
          @tcstring = '(Table Constraint)'

   create table #checks
      (id         int            not null,
       uid        smallint       not null,
       colid      tinyint        not null,
       colname    varchar (30)   not null,
       prntname   varchar (60)   not null,
       seq        tinyint        not null,
       chktext    varchar (255)  not null,
       sorttext   varchar (255)  not null)

   select @tableid = id
   from sysobjects
   where id              = object_id (@TABLE)
     and (sysstat & 15) in (1, 3)

   if ((@TABLE IS NULL)  or (@tableid IS NOT NULL))
      begin
         insert into #checks            -- "Column level" Column checks
            select o.id,
                   o.uid,
                   c.colid,
                   col_name (c.tableid, c.colid),
                   object_name (c.tableid),
                   1,
                   substring (cm.text, 1, char_length (cm.text) - 1),
                   @space1
            from sysconstraints c,
                 syscomments cm,
                 sysobjects o
            where c.tableid         = isnull (@tableid, c.tableid)
              and (c.status & 128)  = 128
              and c.colid          <> 0
              and o.id              = c.tableid
              and cm.id             = c.constrid
              and ((cm.text like 'CHECK %')  or  (cm.text like 'CONSTRAINT %'))

         insert into #checks            -- "Table level" Column checks
            select o.id,
                   o.uid,
                   0,
                   @tcstring,
                   object_name (c.tableid),
                   1,
                   substring (cm.text, 1, char_length (cm.text) - 1),
                   @space1
            from sysconstraints c,
                 syscomments cm,
                 sysobjects o
            where c.tableid        = isnull (@tableid, c.tableid)
              and (c.status & 128) = 128
              and c.colid          = 0
              and o.id             = c.tableid
              and c.constrid       = cm.id
              and ((cm.text like 'CHECK %')  or  (cm.text like 'CONSTRAINT %'))
      end

   if (exists (select *
               from #checks))
      begin
         if (@TABLE IS NULL)
            begin
               update #checks
               set prntname = substring (c.prntname + ' (' + user_name (c.uid) + ')', 1, 30)
               from #checks c
               where c.uid <> 1
                  or 1      < (select count (o.uid)
                               from sysobjects o
                               where o.name            = c.prntname
                                 and (o.sysstat & 15) in (1, 3))
            end

         update #checks
         set chktext = substring (chktext, charindex ('(', chktext) + 1, 255)

         update #checks
         set sorttext = chktext

         while (exists (select *
                        from #checks
                        where charindex (@CR, chktext) > 0))
            begin
               update #checks
               set chktext = substring (chktext, 1, charindex (@CR, chktext) - 1)
                             + @space1
                             + ltrim (substring (chktext, charindex (@CR, chktext) + 1, 255))
               where charindex (@CR, chktext) > 0
            end

         while (exists (select *
                        from #checks
                        where charindex (@LF, chktext) > 0))
            begin
               update #checks
               set chktext = substring (chktext, 1, charindex (@LF, chktext) - 1)
                             + @space1
                             + ltrim (substring (chktext, charindex (@LF, chktext) + 1, 255))
               where charindex (@LF, chktext) > 0
            end

         if (@TABLE IS NULL)
            begin
               select @maxnamelen = max (char_length (prntname))
               from #checks

               if (@maxnamelen > 26)
                  begin
                     select @textlen = 44
                  end
               else if (@maxnamelen > 20)
                  begin
                     select @textlen = 50
                  end
               else
                  begin
                     select @textlen = 56
                  end

               insert into #checks
                  select id, uid, colid, colname, prntname, 2,
                         @space3  +  ltrim (substring (chktext, @textlen + 1, 255)),
                         sorttext
                  from #checks
                  where seq                   = 1
                    and char_length (chktext) > @textlen

               insert into #checks
                  select id, uid, colid, colname, prntname, 3,
                         @space3  +  ltrim (substring (chktext, @textlen + 1, 255)),
                         sorttext
                  from #checks
                  where seq                   = 2
                    and char_length (chktext) > @textlen

               update #checks
               set chktext = substring (chktext, 1, @textlen)

               update #checks
               set sorttext = substring (prntname
                                         +  '    0'
                                         +  substring (sorttext, 1, 95)
                                         +  '  '
                                         +  ltrim (convert (varchar (3), seq)),
                                         1,
                                         255)
               where colid = 0

               update #checks
               set sorttext = substring (prntname
                                         +  '    '
                                         +  ltrim (convert (varchar (1), colid))
                                         +  '  '
                                         +  ltrim (convert (varchar (3), seq))
                                         +  '  '
                                         +  chktext,
                                         1,
                                         255)
               where colid > 0
                 and colid < 10

               update #checks
               set sorttext = substring (prntname
                                         +  '   '
                                         +  ltrim (convert (varchar (2), colid))
                                         +  '  '
                                         +  ltrim (convert (varchar (3), seq))
                                         +  '  '
                                         +  chktext,
                                         1,
                                         255)
               where colid > 9
                 and colid < 100

               update #checks
               set sorttext = substring (prntname
                                         +  '  '
                                         +  ltrim (convert (varchar (3), colid))
                                         +  '  '
                                         +  ltrim (convert (varchar (3), seq))
                                         +  '  '
                                         +  chktext,
                                         1,
                                         255)
               where colid > 99

               update #checks
               set prntname = @space1
               from #checks c1
               where exists (select *
                             from #checks c2
                             where c2.id       = c1.id
                               and c2.colid    = c1.colid
                               and c2.seq      = c1.seq
                               and c2.chktext  = c1.chktext
                               and c2.sorttext > (select min (sorttext)
                                                  from #checks c3
                                                  where c3.id = c2.id))

               if (exists (select *
                           from #checks
                           where seq > 1))
                  begin
                     select @chkcount = ltrim (substring (convert (char (10), convert (money, count (*)), 1), 1, 7))
                     from #checks
                     where seq = 1

                     select @tabcount = ltrim (substring (convert (char (10), convert (money, count (distinct (id))), 1), 1, 7))
                     from #checks
                     where seq = 1

                     if (@tabcount = '1')
                        begin
                           print '    THERE ARE %1! CHECK CONSTRAINTS ON ONE TABLE.',
                                 @chkcount
                        end
                     else
                        begin
                           print '    THERE ARE %1! CHECK CONSTRAINTS ON %2! TABLES.',
                                 @chkcount, @tabcount
                        end

                     print @space1
                  end

               if (@maxnamelen > 26)
                  begin
                     select 'TABLE' = substring (prntname, 1, 32),
                            'CHECK' = substring (chktext, 1, 44)
                     from #checks
                     order by sorttext
                  end
               else if (@maxnamelen > 20)
                  begin
                     select 'TABLE' = substring (prntname, 1, 26),
                            'CHECK' = substring (chktext, 1, 50)
                     from #checks
                     order by sorttext
                  end
               else
                  begin
                     select 'TABLE' = substring (prntname, 1, 20),
                            'CHECK' = substring (chktext, 1, 56)
                     from #checks
                     order by sorttext
                  end
            end
         else
            begin
               select @maxnamelen = max (char_length (colname))
               from #checks

               if (@maxnamelen > 20)
                  begin
                     select @textlen = 52
                  end
               else if (@maxnamelen > 15)
                  begin
                     select @textlen = 57
                  end
               else
                  begin
                     select @textlen = 62
                  end

               insert into #checks
                  select id, uid, colid, ' ', prntname, 2,
                         @space3  +  ltrim (substring (chktext, @textlen + 1, 255)),
                         chktext
                  from #checks
                  where seq                   = 1
                    and char_length (chktext) > @textlen

               insert into #checks
                  select id, uid, colid, ' ', prntname, 3,
                         @space3  +  ltrim (substring (chktext, @textlen + 1, 255)),
                         chktext
                  from #checks
                  where seq                   = 2
                    and char_length (chktext) > @textlen

               if (exists (select *
                           from #checks
                           where seq > 1))
                  begin
                     select @chkcount = ltrim (substring (convert (char (10), convert (money, count (*)), 1), 1, 7))
                     from #checks
                     where seq = 1

                     select @tabcount = ltrim (substring (convert (char (10), convert (money, count (distinct (id))), 1), 1, 7))
                     from #checks
                     where seq = 1

                     if (@tabcount = '1')
                        begin
                           print '    THERE ARE %1! CHECK CONSTRAINTS ON ONE TABLE.',
                                 @chkcount
                        end
                     else
                        begin
                           print '    THERE ARE %1! CHECK CONSTRAINTS ON %2! TABLES.',
                                 @chkcount, @tabcount
                        end

                     print @space1
                  end

               if (@maxnamelen > 20)
                  begin
                     select 'COLUMN' = substring (colname, 1, 24),
                            'CHECK'  = substring (chktext, 1, 52)
                     from #checks
                     order by colid, sorttext, seq
                  end
               else if (@maxnamelen > 15)
                  begin
                     select 'COLUMN' = substring (colname, 1, 19),
                            'CHECK'  = substring (chktext, 1, 57)
                     from #checks
                     order by colid, sorttext, seq
                  end
               else
                  begin
                     select 'COLUMN' = substring (colname, 1, 14),
                            'CHECK'  = substring (chktext, 1, 62)
                     from #checks
                     order by colid, sorttext, seq
                  end

            end
      end
   else
      begin
         if (@TABLE IS NULL)
            begin
               print '    THERE ARE NO CHECK CONSTRAINTS.'
            end
         else if (@tableid IS NULL)
            begin
               print '    TABLE <%1!> DOES NOT EXIST.', @TABLE
            end
         else
            begin
               print '    THERE ARE NO CHECK CONSTRAINTS ON <%1!>.', @TABLE
            end
      end

   print @space1
go
sp_addcomment sp_checks, 17, 'Lists check constraints for TABLE or (default) all tables'
go
sp_procxmode sp_checks, anymode
go

create procedure sp_columns1 (@COLUMN varchar (30))  as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

   create table #columns1
      (objname   varchar (62)  not null,
       uid       smallint      not null,
       typname   char (20)     not null,
       utype     smallint      not null,
       stype     tinyint       not null,
       len       tinyint       not null,
       prec      tinyint       null,
       scale     tinyint       null,
       nulltype  int           not null,
       objtype   int           not null)

   if (exists (select c.*
               from syscolumns c,
                    sysobjects o
               where c.name            = @COLUMN
                 and c.id              = o.id
                 and (o.sysstat & 15) in (1, 2, 3)))
      begin
         insert into #columns1
            select o.name,
                   o.uid,
                   ' ',
                   c.usertype,
                   c.type,
                   c.length,
                   c.prec,
                   c.scale,
                   convert (int, convert (bit, (c.status & 8))),
                   convert (int, o.sysstat & 15)
            from syscolumns c,
                 sysobjects o
            where c.name            = @COLUMN
              and c.id              = o.id
              and (o.sysstat & 15) in (1, 2, 3)

         update #columns1
         set objname = c.objname + ' (' + user_name (c.uid) + ')'
         from #columns1 c
         where c.uid <> 1
            or 1      < (select count (o.uid)
                         from sysobjects o
                         where o.name            = c.objname
                           and (o.sysstat & 15) in (1, 2, 3))

         update #columns1
         set typname = t.name
         from systypes t
         where #columns1.utype > 99
           and #columns1.utype = t.usertype

         update #columns1
         set typname = t.name
         from systypes t
         where #columns1.utype in (11, 12, 16, 18, 19, 20, 21, 22, 80)
           and #columns1.utype  = t.usertype

         update #columns1
         set len = len / @@ncharsize
         where ((utype = 24)  and  (stype = 47))
            or ((utype = 25)  and  (stype = 39))

         update #columns1
         set typname = t.name + ' (' + convert (varchar (3), #columns1.len) + ')'
         from systypes t
         where #columns1.utype in (1, 2, 3, 4, 24, 25)
           and #columns1.utype  = t.usertype

         update #columns1
         set typname = t.name + ' (' + convert (varchar (3), #columns1.prec)
                       + ', ' +  convert (varchar (3), #columns1.scale) + ')'
         from systypes t
         where #columns1.utype in (10, 26)
           and #columns1.utype  = t.usertype

         update #columns1
         set typname = 'float (' +  convert (varchar (2), prec) + ')'
         where utype in (8, 23)
           and prec  IS NOT NULL

         update #columns1
         set typname = 'real'
         where utype  = 23
           and prec  IS NULL

         update #columns1
         set typname = 'double precision'
         where utype in (8, 14)
           and prec  IS NULL

         update #columns1
         set typname = 'tinyint'
         where utype in (5, 13)
           and len    = 1

         update #columns1
         set typname = 'smallint'
         where utype in (6, 13)
           and len    = 2

         update #columns1
         set typname = 'int'
         where utype in (7, 13)
           and len    = 4

         select 'OBJECT'      = substring (c1.objname, 1, 34),
                'COLUMN TYPE' = c1.typname,
                NULLABLE      = substring (vB.name, 1, 8),
                'OBJECT TYPE' = substring (vO.name, 1, 12)
         from #columns1 c1,
              sybsystemprocs.dbo.usptvalues vB,
              sybsystemprocs.dbo.usptvalues vO
         where vB.type   = 'B'
           and vB.number = c1.nulltype
           and vO.type   = 'O'
           and vO.number = c1.objtype
         order by c1.objname, c1.typname, vB.name, vO.name
      end
   else if (exists (select c.*
                    from syscolumns c,
                         sysobjects o
                    where c.name           like (@COLUMN + '%')
                      and c.id                = o.id
                      and (o.sysstat & 15)   in (1, 2, 3)))
      begin
         print '    THERE ARE COLUMNS LIKE <%1!%%>.', @COLUMN

         print ' '

         print '    USE ONE OF THE FOLLOWING AS AN ARGUMENT.'

         print ' '

         select distinct 'COLUMN NAME'  = c.name,
                         'TABLES/VIEWS' = count (o.id)
         from syscolumns c,
              sysobjects o
         where c.name           like (@COLUMN + '%')
           and c.id                = o.id
           and (o.sysstat & 15)   in (1, 2, 3)
         group by c.name
         having c.name           like (@COLUMN + '%')
            and c.id                = o.id
            and (o.sysstat & 15)   in (1, 2, 3)
         order by c.name
      end
   else
      begin
         print '    THERE IS NO TABLE/VIEW COLUMN <%1!>.', @COLUMN

         print ' '
      end
go
sp_procxmode sp_columns1, unchained
go

create procedure sp_columns2 (@COLUMN1 varchar (30),
                              @COLUMN2 varchar (30))  as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

   declare @titlestring  varchar (37)

   create table #columns2
      (oname     varchar (62)  not null,
       id        int           not null,
       uid       smallint      not null,
       sysstat   smallint      not null,
       col12     tinyint       not null,
       utype     smallint      not null,
       stype     tinyint       not null,
       len       tinyint       not null,
       prec      tinyint       null,
       scale     tinyint       null,
       coltext   varchar (22)  not null,
       nulltype  int           not null)

   if (ltrim (rtrim (@COLUMN1)) = ltrim (rtrim (@COLUMN2)))
      begin
         print '    <%1!> AND <%2!> ARE IDENTICAL.  SPECIFY ONLY ONE COLUMN.',
               @COLUMN1, @COLUMN2

         print ' '
      end
   else if (exists (select c1.*
                    from syscolumns c1,
                         syscolumns c2,
                         sysobjects o
                    where c1.name           = @COLUMN1
                      and c2.name           = @COLUMN2
                      and c1.id             = o.id
                      and c1.id             = o.id
                      and (o.sysstat & 15) in (1, 2, 3)))
      begin
         if (char_length (@COLUMN1) > 18)
            begin
               select @titlestring = substring (@COLUMN1, 1, 18)
                                     + ' '
                                     + substring (@COLUMN2, 1, 18)
            end
         else
            begin
               select @titlestring = @COLUMN1
                                     + space (19 - char_length (@COLUMN1))
                                     + substring (@COLUMN2, 1, 18)
            end

         insert into #columns2
            select o.name, o.id, o.uid, o.sysstat, 1, c1.usertype, c1.type,
                   c1.length, c1.prec, c1.scale, '',
                   convert (int, convert (bit, (c1.status & 8)))
            from syscolumns c1,
                 syscolumns c2,
                 sysobjects o
            where c1.name           = @COLUMN1
              and c1.id             = o.id
              and c2.name           = @COLUMN2
              and c2.id             = o.id
              and (o.sysstat & 15) in (1, 2, 3)

         insert into #columns2
            select o.name, o.id, o.uid, o.sysstat, 2, c2.usertype, c2.type,
                   c2.length, c2.prec, c2.scale, '',
                   convert (int, convert (bit, (c2.status & 8)))
            from syscolumns c1,
                 syscolumns c2,
                 sysobjects o
            where c1.name           = @COLUMN1
              and c1.id             = o.id
              and c2.name           = @COLUMN2
              and c2.id             = o.id
              and (o.sysstat & 15) in (1, 2, 3)

         update #columns2
         set oname = c.oname + ' (' + user_name (c.uid) + ')'
         from #columns2 c
         where c.uid <> 1
            or 1      < (select count (distinct o.uid)
                         from sysobjects o
                         where o.name            = c.oname
                           and (o.sysstat & 15) in (1, 2, 3))

         update #columns2
         set coltext = t.name
         from systypes t
         where #columns2.utype > 99
           and #columns2.utype = t.usertype

         update #columns2
         set coltext = t.name
         from systypes t
         where #columns2.utype in (11, 12, 16, 18, 19, 20, 21, 22, 80)
           and #columns2.utype  = t.usertype

         update #columns2
         set len = len / @@ncharsize
         where ((utype = 24)  and  (stype = 47))
            or ((utype = 25)  and  (stype = 39))

         update #columns2
         set coltext = t.name + ' (' + convert (varchar (3), #columns2.len) + ')'
         from systypes t
         where #columns2.utype in (1, 2, 3, 4, 24, 25)
           and #columns2.utype  = t.usertype

         update #columns2
         set coltext = t.name + ' (' + convert (varchar (3), #columns2.prec)
                       + ', ' +  convert (varchar (3), #columns2.scale) + ')'
         from systypes t
         where #columns2.utype in (10, 26)
           and #columns2.utype  = t.usertype

         update #columns2
         set coltext = 'float (' +  convert (varchar (2), prec) + ')'
         where utype in (8, 23)
           and prec  IS NOT NULL

         update #columns2
         set coltext = 'real'
         where utype  = 23
           and prec  IS NULL

         update #columns2
         set coltext = 'double precision'
         where utype in (8, 14)
           and prec  IS NULL

         update #columns2
         set coltext = 'tinyint'
         where utype in (5, 13)
           and len    = 1
         update #columns2
         set coltext = 'smallint'
         where utype in (6, 13)
           and len    = 2

         update #columns2
         set coltext = 'int'
         where utype in (7, 13)
           and len    = 4


         print '                            %1!', @titlestring

         select 'OBJECT'       = substring (c1.oname, 1, 26),
                'COLUMN TYPE'  = substring (c1.coltext + ' ' + char (32 + (10 * c1.nulltype)), 1, 18),
                'COLUMN TYPE ' = substring (c2.coltext + ' ' + char (32 + (10 * c2.nulltype)), 1, 18),
                'OBJECT TYPE'  = substring (vO.name, 1, 12)
         from #columns2 c1,
              #columns2 c2,
              sybsystemprocs.dbo.usptvalues vO
         where c1.col12  = 1
           and c2.col12  = 2
           and c1.id     = c2.id
           and vO.type   = 'O'
           and vO.number = convert (int, c1.sysstat & 15)
         order by c1.oname, vO.name
      end
   else if (exists (select c.*
                    from syscolumns c,
                         sysobjects o
                    where ((c.name like @COLUMN1 + '%')  or  (c.name like @COLUMN2 + '%'))
                      and c.id                = o.id
                      and (o.sysstat & 15)   in (1, 2, 3)))
      begin
         print '    THERE ARE COLUMNS LIKE <%1!%%> OR <%2!%%>.', @COLUMN1, @COLUMN2

         print ' '

         print '    CONSIDER THE FOLLOWING AS ARGUMENTS.'

         print ' '

         select distinct 'COLUMN NAME'  = c.name,
                         'TABLES/VIEWS' = count (o.id)
         from syscolumns c,
              sysobjects o
         where ((c.name like @COLUMN1 + '%')  or  (c.name like @COLUMN2 + '%'))
           and c.id                = o.id
           and (o.sysstat & 15)   in (1, 2, 3)
         group by c.name
         having ((c.name like @COLUMN1 + '%')  or  (c.name like @COLUMN2 + '%'))
            and c.id                = o.id
            and (o.sysstat & 15)   in (1, 2, 3)
         order by c.name
      end
   else
      begin
         print '    THERE ARE NO TABLE/VIEW COLUMNS NAMED EITHER <%1!> OR <%2!>.',
               @COLUMN1, @COLUMN2

         print ' '
      end
go
sp_procxmode sp_columns2, unchained
go

create procedure sp_common (@OBJECT1 varchar (63),
                            @OBJECT2 varchar (63))  as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @titlestring varchar (45),
           @id1         int,
           @id2         int,
           @objectcount int

   if (object_id (@OBJECT1) = object_id (@OBJECT2))
      begin
         print '    OBJECT1 AND OBJECT2 MUST BE DIFFERENT.'

         print ' '
      end
   else
      begin
         select @objectcount = count (*)
         from sysobjects
         where name           in (@OBJECT1, @OBJECT2)
           and (sysstat & 15) in (1, 2, 3)

         if (@objectcount = 2)
            begin
               select @id1 = id
               from sysobjects
               where name            = @OBJECT1
                 and (sysstat & 15) in (1, 2, 3)

               select @id2 = id
               from sysobjects
               where name            = @OBJECT2
                 and (sysstat & 15) in (1, 2, 3)

               create table #common
                  (name      char (30)     not null,
                   col12     tinyint       not null,
                   utype     smallint      not null,
                   stype     tinyint       not null,
                   len       tinyint       not null,
                   prec      tinyint       null,
                   scale     tinyint       null,
                   coltext   varchar (22)  not null,
                   nulltype  int           not null)

               insert into #common
                  select c1.name, 1, c1.usertype, c1.type,
                         c1.length, c1.prec, c1.scale, t1.name,
                         convert (int, convert (bit, (c1.status & 8)))
                  from syscolumns c1,
                       syscolumns c2,
                       systypes   t1
                  where c1.name     = c2.name
                    and c1.id       = @id1
                    and c1.usertype = t1.usertype
                    and c2.id       = @id2

               insert into #common
                  select c2.name, 2, c2.usertype, c2.type,
                         c2.length, c2.prec, c2.scale, t2.name,
                         convert (int, convert (bit, (c2.status & 8)))
                  from syscolumns c1,
                       syscolumns c2,
                       systypes   t2
                  where c1.name     = c2.name
                    and c1.id       = @id1
                    and c2.id       = @id2
                    and c2.usertype = t2.usertype

               if (exists (select *
                           from #common))
                  begin
                     if (char_length (@OBJECT1) > 22)
                        begin
                           select @titlestring = substring (@OBJECT1, 1, 22)
                                                 + ' '
                                                 + substring (@OBJECT2, 1, 22)
                        end
                     else
                        begin
                           select @titlestring = @OBJECT1
                                                 + space (23 - char_length (@OBJECT1))
                                                 + substring (@OBJECT2, 1, 22)
                        end

                     update #common
                     set len = len / @@ncharsize
                     where ((utype = 24)  and  (stype = 47))
                        or ((utype = 25)  and  (stype = 39))

                     update #common
                     set coltext = coltext
                                   + ' ('
                                   + convert (varchar (3), len)
                                   + ')'
                     where utype in (1, 2, 3, 4, 24, 25)

                     update #common
                     set coltext = coltext
                                   + ' ('
                                   + convert (varchar (3), prec)
                                   + ', '
                                   +  convert (varchar (3), scale)
                                   + ')'
                     where utype in (10, 26)

                     update #common
                     set coltext = 'float (' +  convert (varchar (2), prec) + ')'
                     where utype in (8, 23)
                       and prec  IS NOT NULL

                     update #common
                     set coltext = 'real'
                     where ((utype = 23)  or  ((utype = 8) and (len = 4)))
                       and prec  IS NULL

                     update #common
                     set coltext = 'double precision'
                     where utype = 8
                       and len   = 8
                       and prec  IS NULL

                     update #common
                     set coltext = 'tinyint'
                     where utype in (5, 13)
                       and len    = 1

                     update #common
                     set coltext = 'smallint'
                     where utype in (6, 13)
                       and len    = 2

                     update #common
                     set coltext = 'int'
                     where utype in (7, 13)
                       and len    = 4

                     print '                                %1!', @titlestring

                     select 'COLUMN NAME'  = c1.name,
                            'COLUMN TYPE'  = substring (c1.coltext + ' ' + char (32 + (10 * c1.nulltype)), 1, 22),
                            'COLUMN TYPE ' = substring (c2.coltext + ' ' + char (32 + (10 * c2.nulltype)), 1, 22)
                     from #common c1,
                          #common c2
                     where c1.col12 = 1
                       and c2.col12 = 2
                       and c1.name  = c2.name
                     order by c1.name
                  end
               else
                  begin
                     print '    <%1!> AND <%2!> DO NOT HAVE A COMMON COLUMN NAME.',
                           @OBJECT1, @OBJECT2

                     print ' '
                  end
            end
         else
            begin
               if (@objectcount > 2)
                  begin
                     print '    THERE ARE MULTIPLE OBJECTS (%1!).', @objectcount

                     print ' '

                     select TYPE     = o.type,
                            OWNER    = u.name,
                            'OBJECT' = o.name
                     from sysobjects o,
                          sysusers u
                     where o.name           in (@OBJECT1, @OBJECT2)
                       and (o.sysstat & 15) in (1, 2, 3)
                       and u.uid             = o.uid
                     order by o.name, u.name, o.type
                  end
               else
                  begin
                     print '    <%1!> AND/OR <%2!> IS INVALID.', @OBJECT1, @OBJECT2
                  end
               print ' '
            end
      end
go
sp_addcomment sp_common, 17, 'Lists column names common to OBJECT1 and OBJECT2'
go
sp_procxmode sp_common, anymode
go

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

create table #datatype
   (id        int           not null,
    sysstat   smallint      not null,
    objname   varchar (30)  not null,
    colid     tinyint       not null,
    colname   varchar (30)  not null,
    uid       int           not null,
    owner     varchar (30)  not null,
    prntname  varchar (36)  not null,
    len       tinyint       not null,
    size      char (5)      null,
    nullflag  int           not null,
    nulltext  char (4)      not null,
    dflt      int           null,
    dflttext  varchar (30)  null,
    domain    int           null,
    crtext    varchar (70)  null)
go

create procedure sp_datatype_decnum (@USERTYPE  smallint,
                                     @PREC      tinyint) as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             c.length,
             substring (space (convert (tinyint,
                                        3 - ceiling (log10 (1 + c.prec)) - ceiling (log10 (1 + c.scale))))
                        + ltrim (convert (varchar (3), c.prec)
                        +  ', '
                        +  convert (varchar (3), c.scale)),
                        1,
                        5),
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        = @USERTYPE
        and ((c.prec = @PREC)  or  ((c.prec IS NOT NULL)  and  (@PREC IS NULL)))
        and c.id              = o.id
        and (o.sysstat & 15) in (1, 2, 3)
go
sp_procxmode sp_datatype_decnum, unchained
go

create procedure sp_datatype_dp as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             c.length,
             NULL,
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        = 8
        and c.length          = 8
        and c.prec           IS NULL
        and c.id              = o.id
        and (o.sysstat & 15) in (1, 2, 3)
go
sp_procxmode sp_datatype_dp, unchained
go

create procedure sp_datatype_int as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             4,
             '    4',
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        in (7, 13)
        and c.length           = 4
        and c.id               = o.id
        and (o.sysstat & 15)  in (1, 2, 3)
go
sp_procxmode sp_datatype_int, unchained
go

create procedure sp_datatype_float (@PREC  tinyint) as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             c.prec,
             NULL,
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        in (8, 23)
        and ((c.prec = @PREC)  or  ((c.prec IS NOT NULL)  and  (@PREC IS NULL)))
        and c.id               = o.id
        and (o.sysstat & 15)  in (1, 2, 3)
go
sp_procxmode sp_datatype_float, unchained
go

create procedure sp_datatype_real as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             c.length,
             NULL,
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where ((c.usertype = 23)  or  ((c.usertype = 8) and (c.length = 4)))
        and c.prec           IS NULL
        and c.id              = o.id
        and (o.sysstat & 15) in (1, 2, 3)
go
sp_procxmode sp_datatype_real, unchained
go

create procedure sp_datatype_smallint as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             2,
             '    2',
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        in (6, 13)
        and c.length           = 2
        and c.id               = o.id
        and (o.sysstat & 15)  in (1, 2, 3)
go
sp_procxmode sp_datatype_smallint, unchained
go

create procedure sp_datatype_tinyint as
   set ansinull                      on
   set string_rtruncation            on

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             1,
             '    1',
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        in (5, 13)
        and c.length           = 1
        and c.id               = o.id
        and (o.sysstat & 15)  in (1, 2, 3)
go
sp_procxmode sp_datatype_tinyint, unchained
go

create procedure sp_datatype_usertype (@USERTYPE smallint) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

      select 'BASE TYPE' = space (20),
             'NULL'      = space (4),
             'DEFAULT'   = space (25),
             'RULE'      = space (25)
      where 1 = 2
   union all
      select substring (t2.name, 1, 20),
             substring (vB.name, 1, 4),
             substring (isnull (object_name (t.tdefault), '-'), 1, 25),
             substring (isnull (object_name (t.domain), '-'), 1, 25)
      from systypes t,
           systypes t2,
           sybsystemprocs.dbo.usptvalues vB
      where t.usertype   = @USERTYPE
        and t.type       = t2.type
        and t2.usertype in (5, 6, 7, 11, 12, 15, 16, 17, 19,
                            20, 21, 22)
        and vB.type      = 'B'
        and vB.number    = convert (int, t.allownulls)
   union all
      select substring (t2.name  +  ' ('  +  convert (varchar (3), t.length)  + ')', 1, 20),
             substring (vB.name, 1, 4),
             substring (isnull (object_name (t.tdefault), '-'), 1, 25),
             substring (isnull (object_name (t.domain), '-'), 1, 25)
      from systypes t,
           systypes t2,
           sybsystemprocs.dbo.usptvalues vB
      where t.usertype   = @USERTYPE
        and t.type       = t2.type
        and t2.usertype in (1, 2, 3, 4, 13)
        and vB.type      = 'B'
        and vB.number    = convert (int, t.allownulls)
   union all
      select substring (t2.name  +  ' ('
                        +  convert (varchar (3), t.prec) + ', '
                        +  convert (varchar (3), t.scale) + ')',
                        1,
                        20),
             substring (vB.name, 1, 4),
             substring (isnull (object_name (t.tdefault), '-'), 1, 25),
             substring (isnull (object_name (t.domain), '-'), 1, 25)
      from systypes t,
           systypes t2,
           sybsystemprocs.dbo.usptvalues vB
      where t.usertype   = @USERTYPE
        and t.type       = t2.type
        and t2.usertype in (10, 26, 27, 28)
        and vB.type      = 'B'
        and vB.number    = convert (int, t.allownulls)
   union all
      select 'double precision',
             substring (vB.name, 1, 4),
             substring (isnull (object_name (t.tdefault), '-'), 1, 25),
             substring (isnull (object_name (t.domain), '-'), 1, 25)
      from systypes t,
           sybsystemprocs.dbo.usptvalues vB
      where t.usertype  = @USERTYPE
        and t.type     in (62, 109)
        and t.length    = 8
        and t.prec     IS NULL
        and vB.type     = 'B'
        and vB.number   = convert (int, t.allownulls)
   union all
      select 'float ('  +  convert (varchar (3), t.prec)  +  ')',
             substring (vB.name, 1, 4),
             substring (isnull (object_name (t.tdefault), '-'), 1, 25),
             substring (isnull (object_name (t.domain), '-'), 1, 25)
      from systypes t,
           sybsystemprocs.dbo.usptvalues vB
      where t.usertype  = @USERTYPE
        and t.type     in (59, 62, 109)
        and t.prec     IS NOT NULL
        and vB.type     = 'B'
        and vB.number   = convert (int, t.allownulls)
   union all
      select 'real',
             substring (vB.name, 1, 4),
             substring (isnull (object_name (t.tdefault), '-'), 1, 16),
             substring (isnull (object_name (t.domain), '-'), 1, 16)
      from systypes t,
           sybsystemprocs.dbo.usptvalues vB
      where t.usertype  = @USERTYPE
        and t.type      = 59
        and t.length    = 4
        and t.prec     IS NULL
        and vB.type     = 'B'
        and vB.number   = convert (int, t.allownulls)

   print ' '

   insert into #datatype
      select o.id,
             (o.sysstat & 15),
             o.name,
             c.colid,
             c.name,
             o.uid,
             user_name (o.uid),
             substring (o.name + '.' + c.name, 1, 36),
             c.length,
             convert (varchar (5), c.length),
             convert (int, convert (bit, (c.status & 8))),
             ' ',
             c.cdefault,
             NULL,
             c.domain,
             NULL
      from syscolumns c,
           sysobjects o
      where c.usertype         = @USERTYPE
        and c.id               = o.id
        and (o.sysstat & 15)  in (1, 2, 3)
go
sp_procxmode sp_datatype_usertype, unchained
go

drop table #datatype
go

create procedure sp_datatype (@DATATYPE varchar (30),
                              @LEN      tinyint      = NULL)  as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @HT        char (1),
           @LF        char (1),
           @usertype  smallint

   create table #datatype
      (id        int           not null,
       sysstat   smallint      not null,
       objname   varchar (30)  not null,
       colid     tinyint       not null,
       colname   varchar (30)  not null,
       uid       int           not null,
       owner     varchar (30)  not null,
       prntname  varchar (36)  not null,
       len       tinyint       not null,
       size      char (5)      null,
       nullflag  int           not null,
       nulltext  char (4)      not null,
       dflt      int           null,
       dflttext  varchar (30)  null,
       domain    int           null,
       crtext    varchar (70)  null)

   select @HT       = char (9),                       -- Horizontal Tab
          @LF       = char (10),                      -- Line Feed
          @usertype = usertype
   from systypes
   where name = @DATATYPE

   if ((@LEN IS NOT NULL)  and
       (@DATATYPE in ('bit', 'datetime', 'double precision', 'image', 'int',
                      'money', 'real', 'smalldatetime', 'smallint', 'smallmoney',
                      'sysname', 'text', 'timestamp', 'tinyint')))
      begin
         print '    DO NOT SPECIFY A LENGTH/PRECISION (%1!) WITH DATATYPE <%2!>.',
               @LEN, @DATATYPE

         print ' '

         return
      end

   if ((@LEN IS NOT NULL)  and  (@usertype > 99))
      begin
         print '    DO NOT SPECIFY A LENGTH/PRECISION (%1!) WITH USERTYPE <%2!>.',
               @LEN, @DATATYPE

         print ' '

         return
      end

   if (@DATATYPE in ('binary', 'char', 'varbinary', 'varchar'))
      begin
         insert into #datatype
            select o.id,
                   (o.sysstat & 15),
                   o.name,
                   c.colid,
                   c.name,
                   o.uid,
                   user_name (o.uid),
                   substring (o.name + '.' + c.name, 1, 36),
                   c.length,
                   NULL,
                   convert (int, convert (bit, (c.status & 8))),
                   ' ',
                   c.cdefault,
                   NULL,
                   c.domain,
                   NULL
            from syscolumns c,
                 sysobjects o
            where c.usertype        = @usertype
              and ((c.length = @LEN)  or  (@LEN IS NULL))
              and c.id              = o.id
              and (o.sysstat & 15) in (1, 2, 3)

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <%1! (%2!)> IS NOT USED IN ANY TABLE/VIEW.',
                     @DATATYPE, @LEN

               print ' '
            end
      end
   else if (@DATATYPE in ('nchar', 'nvarchar'))
      begin
         insert into #datatype
            select o.id,
                   (o.sysstat & 15),
                   o.name,
                   c.colid,
                   c.name,
                   o.uid,
                   user_name (o.uid),
                   substring (o.name + '.' + c.name, 1, 36),
                   c.length / @@ncharsize,
                   NULL,
                   convert (int, convert (bit, (c.status & 8))),
                   ' ',
                   c.cdefault,
                   NULL,
                   c.domain,
                   NULL
            from syscolumns c,
                 sysobjects o
            where c.usertype        = @usertype
              and ((c.length = @LEN)  or  (@LEN IS NULL))
              and c.id              = o.id
              and (o.sysstat & 15) in (1, 2, 3)

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <%1! (%2!)> IS NOT USED IN ANY TABLE/VIEW.',
                     @DATATYPE, @LEN

               print ' '
            end
      end
   else if (@DATATYPE in ('bit', 'datetime', 'image', 'money', 'smalldatetime',
                          'smallmoney', 'sysname', 'text', 'timestamp'))
      begin
         insert into #datatype
            select o.id,
                   (o.sysstat & 15),
                   o.name,
                   c.colid,
                   c.name,
                   o.uid,
                   user_name (o.uid),
                   substring (o.name + '.' + c.name, 1, 36),
                   c.length,
                   NULL,
                   convert (int, convert (bit, (c.status & 8))),
                   ' ',
                   c.cdefault,
                   NULL,
                   c.domain,
                   NULL
            from syscolumns c,
                 sysobjects o
            where c.usertype        = @usertype
              and c.id              = o.id
              and (o.sysstat & 15) in (1, 2, 3)

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <%1!> IS NOT USED IN ANY TABLE/VIEW.', @DATATYPE

               print ' '
            end
      end
   else if (@DATATYPE in ('decimal', 'numeric'))
      begin
         execute sp_datatype_decnum @usertype, @LEN

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <%1! (%2!)> IS NOT USED IN ANY TABLE/VIEW.',
                     @DATATYPE, @LEN

               print ' '
            end
      end
   else if (@DATATYPE = 'double precision')
      begin
         execute sp_datatype_dp

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <double precision> IS NOT USED IN ANY TABLE/VIEW.'

               print ' '
            end
      end
   else if (@DATATYPE = 'float')
      begin
         select @usertype = -1

         execute sp_datatype_float @LEN

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <float (%1!)> IS NOT USED IN ANY TABLE/VIEW.',
                     @LEN

               print ' '
            end
      end
   else if (@DATATYPE = 'int')
      begin
         execute sp_datatype_int

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <int> IS NOT USED IN ANY TABLE/VIEW.'

               print ' '
            end
      end
   else if (@DATATYPE = 'real')
      begin
         execute sp_datatype_real

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <real> IS NOT USED IN ANY TABLE/VIEW.'

               print ' '
            end
      end
   else if (@DATATYPE = 'smallint')
      begin
         execute sp_datatype_smallint

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <smallint> IS NOT USED IN ANY TABLE/VIEW.'

               print ' '
            end
      end
   else if (@DATATYPE = 'tinyint')
      begin
         execute sp_datatype_tinyint

         if (not exists (select *
                         from #datatype))
            begin
               print '    DATATYPE <tinyint> IS NOT USED IN ANY TABLE/VIEW.'

               print ' '
            end
      end
   else if (@usertype > 99)
      begin
         execute sp_datatype_usertype @usertype

         if (not exists (select *
                         from #datatype))
            begin
               print '    USERTYPE <%1!> IS NOT USED IN ANY TABLE/VIEW.', @DATATYPE

               print ' '
            end
      end
   else
      begin
         print '    DATATYPE <%1!> DOES NOT EXIST.', @DATATYPE

         print ' '
      end

   if (exists (select *
               from #datatype))
      begin
         if (@LEN IS NOT NULL)
            begin
               delete from #datatype
               where len <> @LEN
            end

         update #datatype
         set size = space (convert (tinyint, 5 - ceiling (log10 (1 + len))))
                    + ltrim (convert (varchar (3), len))
         where size IS NULL

         update #datatype
         set nulltext = substring (vB.name, 1, 4)
         from sybsystemprocs.dbo.usptvalues vB
         where vB.type   = 'B'
           and vB.number = nullflag

         update #datatype
         set prntname = substring (prntname + ' (' + owner + ')', 1, 36)
         where #datatype.uid <> 1
            or 1              < (select count (distinct o.uid)
                                 from sysobjects o
                                 where o.name           = #datatype.objname
                                   and (o.sysstat & 15) = #datatype.sysstat)

         update #datatype
         set dflttext = ltrim (substring (c.text, 8, 30))
         from syscomments c,
              sysprocedures p
         where #datatype.dflt    = p.id
           and p.sequence        = 1
           and (p.status & 4096) = 4096
           and c.id              = p.id
           and c.colid           = 1
           and c.texttype        = 0

         update #datatype
         set dflttext = substring (dflttext, 2, 50)
         where substring (dflttext, 1, 1) in (@HT, @LF)

         while (exists (select *
                        from #datatype
                        where charindex (@HT, dflttext) > 0))
            begin
               update #datatype
               set dflttext = substring (dflttext, 1, charindex (@HT, dflttext) - 1)
                              + ' '
                              + substring (dflttext, charindex (@HT, dflttext) + 1, 70)
               where charindex (@HT, dflttext) > 0
            end

         while (exists (select *
                        from #datatype
                        where charindex (@LF, dflttext) > 0))
            begin
               update #datatype
               set dflttext = substring (dflttext, 1, charindex (@LF, dflttext) - 1)
                              + ' '
                              + substring (dflttext, charindex (@LF, dflttext) + 1, 70)
               where charindex (@LF, dflttext) > 0
            end

         update #datatype
         set dflttext = object_name (dflt)
         from sysprocedures p
         where #datatype.dflt     = p.id
           and p.sequence         = 1
           and (p.status & 4096) <> 4096

         update #datatype
         set dflttext = '-'
         where dflttext IS NULL

         update #datatype
         set crtext = substring (c.text, 1, 70)
         from syscomments c,
              sysprocedures p
         where #datatype.domain  = p.id
           and p.sequence        = 1
           and (p.status & 4096) = 4096
           and c.id              = p.id
           and c.colid           = 1
           and c.colid2          = 0
           and c.texttype        = 0

         update #datatype
         set crtext = substring (crtext, 2, 70)
         where substring (crtext, 1, 1) in (@HT, @LF)

         while (exists (select *
                        from #datatype
                        where charindex (@HT, crtext) > 0))
            begin
               update #datatype
               set crtext = substring (crtext, 1, charindex (@HT, crtext) - 1)
                            + ' '
                            + substring (crtext, charindex (@HT, crtext) + 1, 70)
               where charindex (@HT, crtext) > 0
            end

         while (exists (select *
                        from #datatype
                        where charindex (@LF, crtext) > 0))
            begin
               update #datatype
               set crtext = substring (crtext, 1, charindex (@LF, crtext) - 1)
                            + ' '
                            + substring (crtext, charindex (@LF, crtext) + 1, 70)
               where charindex (@LF, crtext) > 0
            end

         update #datatype
         set crtext = object_name (domain)
         from sysprocedures p
         where #datatype.domain   = p.id
           and p.sequence         = 1
           and (p.status & 4096) <> 4096

         update #datatype
         set crtext = '-'
         where crtext IS NULL

         if (@usertype > 99)
            begin
               select 'OBJECT.COLUMN' = prntname,
                      'NULL'          = nulltext,
                      'DEFAULT'       = substring (dflttext, 1, 11),
                      'RULE'          = substring (crtext, 1, 23)
               from #datatype
               order by objname, owner, colid
            end
         else if ((@DATATYPE in ('bit', 'datetime', 'double precision', 'image',
                                 'int', 'money', 'real', 'smalldatetime',
                                 'smallint', 'smallmoney', 'sysname', 'text',
                                 'timestamp', 'tinyint')))
            begin
               select 'OBJECT.COLUMN' = prntname,
                      'NULL'          = nulltext,
                      'DEFAULT'       = substring (dflttext, 1, 11),
                      'CHECK / RULE'  = substring (crtext, 1, 23)
               from #datatype
               order by objname, owner, colid
            end
         else if (@LEN IS NULL)
            begin
               select 'OBJECT.COLUMN' = prntname,
                      SIZE            = size,
                      'NULL'          = nulltext,
                      'DEFAULT'       = substring (dflttext, 1, 11),
                      'CHECK / RULE'  = substring (crtext, 1, 17)
               from #datatype
               order by objname, owner, colid
            end
         else
            begin
               select 'OBJECT.COLUMN' = prntname,
                      'NULL'          = nulltext,
                      'DEFAULT'       = substring (dflttext, 1, 11),
                      'CHECK / RULE'  = substring (crtext, 1, 23)
               from #datatype
               order by objname, owner, colid
            end
      end
go
sp_addcomment sp_datatype, 17, 'Lists columns of type DATATYPE and (optional) LENGTH/PRECISION'
go
sp_procxmode sp_datatype, anymode
go

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

create table #bothcache
   (objid      int            not null,
    objsuid    smallint       not null,
    dbname     varchar (30)   not null,
    objname    varchar (30)   not null,
    prntname   varchar (94)   not null,
    type       char (10)      not null,
    cachename  varchar (120)  not null,
    indid      tinyint        not null)
go

create table #bothlockcfg
  (sort        tinyint        not null,
   dbname      varchar (30)   not null,
   tabname     varchar (30)   not null,
   txt         varchar (33)   not null)
go

create table #currdb
   (tabname    varchar (30)   not null,
    tabsuid    smallint       not null,
    tabid      int            not null,
    ttype      tinyint        not null,
    colname    varchar (30)   not null,
    cutype     tinyint        not null,
    ctype      tinyint        not null,
    clen       tinyint        not null,
    cprec      tinyint        null,
    cscale     tinyint        null,
    ctypetxt   varchar (16)   not null,
    nul        char (1)       not null)
go

create table #otherdb
   (tabname    varchar (30)   not null,
    tabsuid    smallint       not null,
    tabid      int            not null,
    ttype      tinyint        not null,
    colname    varchar (30)   not null,
    cutype     tinyint        not null,
    ctype      tinyint        not null,
    clen       tinyint        not null,
    cprec      tinyint        null,
    cscale     tinyint        null,
    ctypetxt   varchar (16)   not null,
    nul        char (1)       not null)
go

create table #currprocs
   (procname   varchar (30)   not null,
    procsuid   smallint       not null,
    trigname   varchar (30)   null,
    ostatus    smallint       not null,
    ostatus2   smallint       not null,
    pstatus    smallint       not null,
    typetxt    varchar (16)   not null)
go

create table #otherprocs
   (procname  varchar (30)    not null,
    procsuid  smallint        not null,
    trigname  varchar (30)    null,
    ostatus   smallint        not null,
    ostatus2  smallint        not null,
    pstatus   smallint        not null,
    typetxt   varchar (16)    not null)
go

create table #currinds
   (tabname   varchar (30)    not null,
    tabsuid   smallint        not null,
    indid     tinyint         not null,
    status    smallint        not null,
    typetxt   varchar (75)    not null,
    keytxt    varchar (75)    not null,
    key1      varchar (30)    not null,
    key2      varchar (30)    null,
    key3      varchar (30)    null,
    key4      varchar (30)    null,
    key5      varchar (30)    null,
    key6      varchar (30)    null,
    key7      varchar (30)    null,
    key8      varchar (30)    null)
go

create table #otherinds
   (tabname   varchar (30)  not null,
    tabsuid   smallint      not null,
    indid     tinyint       not null,
    status    smallint      not null,
    typetxt   varchar (75)  not null,
    keytxt    varchar (75)  not null,
    key1      varchar (30)  not null,
    key2      varchar (30)  null,
    key3      varchar (30)  null,
    key4      varchar (30)  null,
    key5      varchar (30)  null,
    key6      varchar (30)  null,
    key7      varchar (30)  null,
    key8      varchar (30)  null)
go


create procedure sp_dbdiff_load (@TRANSFER  char (1))
                 with recompile as

   set ansinull                      off              -- Non-ANSI
   set nocount                       on
   set string_rtruncation            on

   -- ANSINULL IS OFF TO SIMPLIFY LENGTH/PREC/SCALE/NULL COLUMN COMPARISONS.

   declare @dbownersuid  smallint,
           @sequence     int

   -- LOCK CONFIGURATIONS ------------------------------------------------

   insert into #bothlockcfg
      select 1,
             db_name (),
             object_name (a.object),
             substring (a.char_value, 1, 33)
      from sysattributes a,
           sysindexes i
      where a.object_type = 'T'
        and a.class       = 5
        and i.id          = a.object
        and i.indid       < 2

   insert into #bothlockcfg
      select 2,
             db_name (),
             '(database)',
             substring (a.char_value, 1, 33)
      from master.dbo.sysattributes a,
           master.dbo.sysattributes an
      where a.object_type   = 'D'
        and a.object        = db_id ()
        and an.object       = a.class
        and an.object_info1 = a.attribute
        and an.class        = 0
        and an.attribute    = 1
        and an.char_value   = 'Lock promotion'

   -- TABLES/VIEWS WITH COLUMNS ------------------------------------------

   select @dbownersuid = suid
   from master.dbo.sysdatabases
   where dbid = db_id ()

   insert into #otherdb
      select o.name,
             u.suid,
             c.id,
             (o.sysstat & 15),
             c.name,
             c.usertype,
             c.type,
             c.length,
             c.prec,
             c.scale,
             t.name,
             char (32 + (10 * convert (int, convert (bit, (c.status & 8)))))
      from syscolumns c,
           sysobjects o,
           systypes t,
           sysusers u
      where c.id              = o.id
        and u.uid             = o.uid
        and (o.sysstat & 15) in (2, 3)
        and t.usertype        = c.usertype

   update #otherdb
   set ctypetxt = ctypetxt  +  ' ('  +  convert (varchar (3), clen)  +  ')'
   where cutype in (4, 80, 13, 2, 18, 25, 3, 1, 24)

   update #otherdb
   set ctypetxt = ctypetxt
                  +  ' ('
                  +  convert (varchar (3), cprec)
                  +  ', '
                  +  convert (varchar (3), cscale)
                  +  ')'
   where cutype in (26, 10, 27, 28)

   update #otherdb
   set ctypetxt = 'float ('  +  convert (varchar (3), cprec)  +  ')'
   where cutype in (8, 23)
     and cprec IS NOT NULL

   update #otherdb
   set ctypetxt = 'real'
   where cutype in (8, 23)
     and cprec  IS NULL
     and clen    = 4

   update #otherdb
   set ctypetxt = 'double precision'
   where cutype  = 8
     and cprec  IS NULL
     and clen    = 8

   update #otherdb
   set tabsuid = -1
   where tabsuid = @dbownersuid

   -- NAMED DEFAULTS, RULES, TRIGGERS, AND STORED PROCEDURES -------------

   insert into #otherprocs
      select o.name, u.suid, NULL, o.sysstat, o.sysstat2, p.status, 'default'
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15)   = 6
        and u.uid              = o.uid
        and p.id               = o.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096

   insert into #otherprocs
      select o.name, u.suid, NULL, o.sysstat, o.sysstat2, p.status, 'rule'
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15)   = 7
        and u.uid              = o.uid
        and p.id               = o.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096

   insert into #otherprocs
      select o.name, u.suid, NULL, o.sysstat, o.sysstat2, p.status, 'stored procedure'
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15) = 4
        and u.uid            = o.uid
        and p.id             = o.id
        and p.sequence       = 1

   insert into #otherprocs
      select o.name, u.suid, object_name (o.deltrig), 0, 0, -4, 'delete trigger'
      from sysobjects o,
           sysusers u
      where (o.sysstat & 15)  = 3
        and o.deltrig        <> 0
        and u.uid             = o.uid

   insert into #otherprocs
      select o.name, u.suid, object_name (o.instrig), 0, 0, -3, 'insert trigger'
      from sysobjects o,
           sysusers u
      where (o.sysstat & 15)  = 3
        and o.instrig        <> 0
        and u.uid             = o.uid

   insert into #otherprocs
      select o.name, u.suid, object_name (o.updtrig), 0, 0, -2, 'update trigger'
      from sysobjects o,
           sysusers u
      where (o.sysstat & 15)  = 3
        and o.updtrig        <> 0
        and u.uid             = o.uid

   update #otherprocs
   set procsuid = -1
   where procsuid = @dbownersuid

   -- INDEXES ------------------------------------------------------------

   insert into #otherinds
      select o.name,
             u.suid,
             i.indid,
             i.status,
             ' ',
             ' ',
             index_col (o.name, i.indid, 1, o.uid),
             index_col (o.name, i.indid, 2, o.uid),
             index_col (o.name, i.indid, 3, o.uid),
             index_col (o.name, i.indid, 4, o.uid),
             index_col (o.name, i.indid, 5, o.uid),
             index_col (o.name, i.indid, 6, o.uid),
             index_col (o.name, i.indid, 7, o.uid),
             index_col (o.name, i.indid, 8, o.uid)
      from sysindexes i,
           sysobjects o,
           sysusers u
      where i.id    = o.id
        and i.id    > 100
        and i.indid > 0
        and i.indid < 254
        and u.uid   = o.uid

   update #otherinds
   set tabsuid = -1
   where tabsuid = @dbownersuid

   -- CONSTRUCT INDEX TYPE TEXT

   select @sequence = 1

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

         select @sequence = @sequence + 1
      end

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

   update #otherinds
   set typetxt = substring (typetxt + ' (' + convert (varchar (3), indid) + ')', 1, 75)

   -- ONLY STATUS VALUES OF 1, 2, 4, 16, 64, AND 2048 ARE OF INTEREST.
   -- 30632 = 8 + 32 + 128 + 256 + 512 + 1024 + 4096 + 8192 + 16384.

   update #otherinds
   set status = (status | 30632)

   -- CONSTRUCT KEY TEXT

   update #otherinds
   set keytxt = key1

   update #otherinds
   set keytxt = keytxt  +  ', '  +  key2
   where key2 IS NOT NULL

   update #otherinds
   set keytxt = keytxt  +  ', '  +  key3
   where key3 IS NOT NULL

   update #otherinds
   set keytxt = keytxt  +  ', '  +  key4
   where key4 IS NOT NULL

   update #otherinds
   set keytxt = keytxt  +  ', '  +  key5
   where key5 IS NOT NULL

   update #otherinds
   set keytxt = keytxt  +  ', '  +  key6
   where key6 IS NOT NULL

   update #otherinds
   set keytxt = keytxt  +  ', '  +  key7
   where key7 IS NOT NULL

   update #otherinds
   set keytxt = keytxt  +  ', '  +  key8
   where key8 IS NOT NULL

   -- KEY VALUES OF NULL ARE NO GOOD FOR LATER COMPARISONS

   update #otherinds
   set key2 = isnull (key2, ' '),
       key3 = isnull (key3, ' '),
       key4 = isnull (key4, ' '),
       key5 = isnull (key5, ' '),
       key6 = isnull (key6, ' '),
       key7 = isnull (key7, ' '),
       key8 = isnull (key8, ' ')

   -- CACHED OBJECTS -----------------------------------------------------

   insert into #bothcache
      select object, -1, db_name (), db_name (object), ' ', 'Database', char_value, 0
      from sysattributes
      where class       = 3
        and attribute   = 0
        and object_type = 'D'

   insert into #bothcache
      select a.object,
             -1,
             db_name (),
             object_name (a.object) + '.' + i.name,
             ' ',
             'Index (' + convert (varchar (3), i.indid) + ')',
             a.char_value,
             i.indid
      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       < 255

   insert into #bothcache
      select object, -1, db_name (), object_name (object), ' ', 'Log', char_value, 0
      from sysattributes
      where class       = 3
        and attribute   = 0
        and object_type = 'T'
        and object      = 8

   insert into #bothcache
      select a.object,
             -1,
             db_name (),
             object_name (a.object),
             ' ',
             'Table (' + convert (varchar (3), i.indid) + ')',
             a.char_value,
             0
      from sysattributes a,
           sysindexes i
      where a.class        = 3
        and a.attribute    = 0
        and a.object_type  = 'T'
        and a.object      <> 8
        and i.id           = a.object
        and i.indid       in (0, 1)

   insert into #bothcache
      select object, -1, db_name (), object_name (object), ' ', 'Image/Text', char_value, 0
      from sysattributes
      where class        = 3
        and attribute    = 0
        and object_info1 > 254

   update #bothcache
   set objsuid  = u.suid,
       prntname = c.objname + ' (' + suser_name (o.uid) + ')'
   from #bothcache c,
        sysobjects o,
        sysusers u
   where c.dbname = db_name ()
     and o.id     = c.objid
     and u.uid    = o.uid
     and ((c.type = 'Image/Text')  or  (c.type like 'Table (%'))
     and ((o.uid <> 1)  or
          (1      < (select count (o2.uid)
                     from sysobjects o2
                     where o2.name            = c.objname
                       and (o2.sysstat & 15) in (2, 3))))

   update #bothcache
   set objsuid  = u.suid,
       prntname = c.objname + ' (' + suser_name (o.uid) + ')'
   from #bothcache c,
        sysobjects o,
        sysusers u
   where c.dbname  = db_name ()
     and c.type like 'Index%'
     and o.id      = c.objid
     and u.uid     = o.uid
     and ((o.uid <> 1)  or
          (1      < (select count (o2.uid)
                     from sysobjects o2
                     where o2.name            = substring (c.objname, 1, charindex ('.', c.objname) - 1)
                       and (o2.sysstat & 15) in (2, 3))))

   update #bothcache
   set prntname = objname
   where prntname = ' '

   update #bothcache
   set objsuid = -1
   where dbname  = db_name ()
     and objsuid = @dbownersuid

   if (@TRANSFER = 'Y')
      begin
         insert into #currdb
            select *
            from #otherdb

         truncate table #otherdb

         insert into #currinds
            select *
            from #otherinds

         truncate table #otherinds

         insert into #currprocs
            select *
            from #otherprocs

         truncate table #otherprocs
      end
go
sp_procxmode sp_dbdiff_load, unchained
go

create procedure sp_dbdiff_print (@CURRDB   varchar (30),
                                  @OTHERDB  varchar (30))
                 with recompile as

   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   declare @cachetxt     char (78),     -- section 7
           @cextra       tinyint,
           @coldifftxt   char (78),     -- section 3
           @clength      tinyint,
           @currtxt1     char (78),     -- section 1
           @currtxt2     char (78),     -- section 2
           @currtxt5     char (78),     -- section 5
           @currtxt7     char (78),     -- section 7
           @lockcfgtxt   char (78),     -- section 6
           @oextra       tinyint,
           @olength      tinyint,
           @othertxt1    char (78),     -- section 1
           @othertxt2    char (78),     -- section 2
           @othertxt5    char (78),     -- section 5
           @othertxt7    char (78),     -- section 7
           @procdifftxt  char (78)      -- section 4

   print ' '

   -- CONSTRUCT HEADERS WITH DATABASE NAMES ------------------------------

   select @clength = char_length (@CURRDB),
          @olength = char_length (@OTHERDB)

   select @cextra = (@clength % 2),
          @oextra = (@olength % 2)

   -- VALUES ARE ASSIGNED IN OUTPUT USAGE ORDER --------------------------

   select @othertxt1   = ' <'
                         +  replicate ('=', 28 - (@olength / 2))
                         +  ' OBJECTS ONLY IN '
                         +  @OTHERDB
                         +  ' '
                         +  replicate ('=', 29 - (@olength / 2)  -  @oextra)
                         +  '>',
          @currtxt1    = ' <'
                         +  replicate ('=', 28 - (@clength / 2))
                         +  ' OBJECTS ONLY IN '
                         +  @CURRDB
                         +  ' '
                         +  replicate ('=', 29 - (@clength / 2)  -  @cextra)
                         +  '>',
          @othertxt2   = ' <'
                         +  replicate ('=', 28 - (@olength / 2))
                         +  ' COLUMNS ONLY IN '
                         +  @OTHERDB
                         +  ' '
                         +  replicate ('=', 29 - (@olength / 2)  -  @oextra)
                         +  '>',
          @currtxt2    = ' <'
                         +  replicate ('=', 28 - (@clength / 2))
                         +  ' COLUMNS ONLY IN '
                         +  @CURRDB
                         +  ' '
                         +  replicate ('=', 29 - (@clength / 2)  -  @cextra)
                         +  '>',
          @coldifftxt  = space (41)
                         +  convert (char (18), substring (@OTHERDB, 1, 18))
                         +  ' '
                         +  convert (char (18), substring (@CURRDB, 1, 18)),
          @procdifftxt = space (32)
                         +  convert (char (20), substring (@OTHERDB, 1, 20))
                         +  ' '
                         +  convert (char (20), substring (@CURRDB, 1, 20)),
          @othertxt5   = ' <'
                         +  replicate ('=', 28 - (@olength / 2))
                         +  ' INDEXES ONLY IN '
                         +  @OTHERDB
                         +  ' '
                         +  replicate ('=', 29 - (@olength / 2)  -  @oextra)
                         +  '>',
          @currtxt5    = ' <'
                         +  replicate ('=', 28 - (@clength / 2))
                         +  ' INDEXES ONLY IN '
                         +  @CURRDB
                         +  ' '
                         +  replicate ('=', 29 - (@clength / 2)  -  @cextra)
                         +  '>',
          @lockcfgtxt  = space (19)
                         +  convert (char (29), substring (@OTHERDB, 1, 29))
                         +  ' '
                         +  convert (char (29), substring (@CURRDB, 1, 29)),
          @othertxt7   = ' <'
                         +  replicate ('=', 25 - (@olength / 2))
                         +  ' CACHE BINDINGS ONLY IN '
                         +  @OTHERDB
                         +  ' '
                         +  replicate ('=', 25 - (@olength / 2)  -  @oextra)
                         +  '>',
          @currtxt7    = ' <'
                         +  replicate ('=', 25 - (@clength / 2))
                         +  ' CACHE BINDINGS ONLY IN '
                         +  @CURRDB
                         +  ' '
                         +  replicate ('=', 25 - (@clength / 2)  -  @cextra)
                         +  '>',
          @cachetxt    = space (37)
                         +  convert (char (20), substring (@OTHERDB, 1, 20))
                         +  ' '
                         +  convert (char (20), substring (@CURRDB, 1, 20))

   -- DEFAULTS, PROCEDURES, RULES, TRIGGERS, TABLES, AND VIEWS -----------

   select op.*                                            -- defaults, procs & rules
   into #other2procs
   from #otherprocs op
   where op.pstatus > 0
     and not exists (select cp.*
                     from #currprocs cp
                     where cp.procname = op.procname
                       and cp.procsuid = op.procsuid)

   insert into #other2procs                                 -- triggers
      select op.*
      from #otherprocs op,
           #currprocs cp
      where op.pstatus   < 0
        and cp.pstatus   = op.pstatus
        and cp.procname  = op.procname
        and cp.procsuid  = op.procsuid
        and cp.trigname <> op.trigname

   select cp.*                                            -- defaults, procs & rules
   into #curr2procs
   from #currprocs cp
   where cp.pstatus > 0
     and not exists (select op.*
                     from #otherprocs op
                     where cp.procname = op.procname
                       and cp.procsuid = op.procsuid)

   insert into #curr2procs                                  -- triggers
      select cp.*
      from #currprocs cp,
           #otherprocs op
      where cp.pstatus   < 0
        and op.pstatus   = cp.pstatus
        and op.procname  = cp.procname
        and op.procsuid  = cp.procsuid
        and op.trigname <> cp.trigname

   select distinct o.tabname, o.tabsuid, o.ttype
   into #other2db
   from #otherdb o
   where not exists (select c.*
                     from #currdb c
                     where c.tabname = o.tabname
                       and c.tabsuid = o.tabsuid
                       and c.ttype   = o.ttype)

   if ((exists (select *
                from #other2procs))  or
       (exists (select *
                from #other2db)))
      begin
         print '%1!', @othertxt1

            select OBJECT = space (30),
                   OWNER  = space (29),
                   TYPE   = space (15)
            where 1 = 2
         union
            select procname,
                   substring (isnull (suser_name (procsuid), 'dbo'), 1, 29),
                   typetxt
            from #other2procs
            where pstatus > 0
         union
            select substring (procname  +  '.'  +  trigname, 1, 30),
                   substring (isnull (suser_name (procsuid), 'dbo'), 1, 29),
                   typetxt
            from #other2procs
            where pstatus < 0
         union
            select d.tabname,
                   substring (isnull (suser_name (d.tabsuid), 'dbo'), 1, 29),
                   substring (vO.name, 1, 10)
            from #other2db d,
                 sybsystemprocs.dbo.usptvalues vO
            where vO.type   = 'O'
              and vO.number = d.ttype
         order by OBJECT, OWNER
      end
   else
      begin
         print '    THERE ARE NO DEFAULTS/PROCEDURES/RULES/TRIGGERS/TABLES/VIEWS ONLY IN %1!.',
               @OTHERDB
      end

   drop table #other2procs, #other2db

   print ' '

   select distinct c.tabname, c.tabsuid, c.ttype
   into #curr2db
   from #currdb c
   where not exists (select o.*
                     from #otherdb o
                     where c.tabname = o.tabname
                       and c.tabsuid = o.tabsuid
                       and c.ttype   = o.ttype)

   if ((exists (select *
                from #curr2procs))  or
       (exists (select *
                from #curr2db)))
      begin
         print '%1!', @currtxt1

            select OBJECT = space (30),
                   OWNER  = space (29),
                   TYPE   = space (15)
            where 1 = 2
         union
            select procname,
                   substring (isnull (suser_name (procsuid), 'dbo'), 1, 29),
                   typetxt
            from #curr2procs
            where pstatus > 0
         union
            select substring (procname  +  '.'  +  trigname, 1, 30),
                   substring (isnull (suser_name (procsuid), 'dbo'), 1, 29),
                   typetxt
            from #curr2procs
            where pstatus < 0
         union
            select d.tabname,
                   substring (isnull (suser_name (d.tabsuid), 'dbo'), 1, 29),
                   substring (vO.name, 1, 10)
            from #curr2db d,
                 sybsystemprocs.dbo.usptvalues vO
            where vO.type   = 'O'
              and vO.number = d.ttype
         order by OBJECT, OWNER
      end
   else
      begin
         print '    THERE ARE NO DEFAULTS/PROCEDURES/RULES/TRIGGERS/TABLES/VIEWS ONLY IN %1!.',
               @CURRDB
      end

   drop table #curr2procs, #curr2db

   print ' '

   print ' '

   -- COLUMNS ------------------------------------------------------------

   if (exists (select o.*
               from #otherdb o
               where exists     (select c.*
                                 from #currdb c
                                 where c.tabname = o.tabname
                                   and c.tabsuid = o.tabsuid
                                   and c.ttype   = o.ttype)
                 and not exists (select c.*
                                 from #currdb c
                                 where c.tabname = o.tabname
                                   and c.tabsuid = o.tabsuid
                                   and c.ttype   = o.ttype
                                   and c.colname = o.colname)))
      begin
         print '%1!', @othertxt2

            select 'TABLE/VIEW' = space (29),
                   'COLUMN'     = space (28),
                   'DATA TYPE'  = space (18)
            where 1 = 2
         union
            select substring (o.tabname, 1, 29),
                   substring (o.colname, 1, 28),
                   substring (o.ctypetxt  +  ' '  +   o.nul, 1, 18)
            from #otherdb o
            where o.tabsuid = -1
              and exists     (select c.*
                              from #currdb c
                              where c.tabname = o.tabname
                                and c.tabsuid = o.tabsuid
                                and c.ttype   = o.ttype)
              and not exists (select c.*
                              from #currdb c
                              where c.tabname = o.tabname
                                and c.tabsuid = o.tabsuid
                                and c.ttype   = o.ttype
                                and c.colname = o.colname)
         union
            select substring (o.tabname + ' (' + suser_name (o.tabsuid) + ')', 1, 29),
                   substring (o.colname, 1, 28),
                   substring (o.ctypetxt  +  ' '  +   o.nul, 1, 18)
            from #otherdb o
            where o.tabsuid <> -1
              and exists     (select c.*
                              from #currdb c
                              where c.tabname = o.tabname
                                and c.tabsuid = o.tabsuid
                                and c.ttype   = o.ttype)
              and not exists (select c.*
                              from #currdb c
                              where c.tabname = o.tabname
                                and c.tabsuid = o.tabsuid
                                and c.ttype   = o.ttype
                                and c.colname = o.colname)
         order by 'TABLE/VIEW', 'COLUMN'
      end
   else
      begin
         print '    THERE ARE NO COLUMNS ONLY IN %1! FOR COMMON TABLES/VIEWS.',
               @OTHERDB
      end

   print ' '

   if (exists (select c.*
               from #currdb c
               where exists     (select o.*
                                 from #otherdb o
                                 where o.tabname = c.tabname
                                   and o.tabsuid = c.tabsuid
                                   and o.ttype   = c.ttype)
                 and not exists (select o.*
                                 from #otherdb o
                                 where o.tabname = c.tabname
                                   and o.tabsuid = c.tabsuid
                                   and o.ttype   = c.ttype
                                   and o.colname = c.colname)))
      begin
         print '%1!', @currtxt2

            select 'TABLE/VIEW' = space (29),
                   'COLUMN'     = space (28),
                   'DATA TYPE'  = space (18)
            where 1 = 2
         union
            select substring (c.tabname, 1, 29),
                   substring (c.colname, 1, 28),
                   substring (c.ctypetxt  +  ' '  +   c.nul, 1, 18)
            from #currdb c
            where c.tabsuid = -1
              and exists     (select o.*
                              from #otherdb o
                              where o.tabname = c.tabname
                                and o.tabsuid = c.tabsuid
                                and o.ttype   = c.ttype)
              and not exists (select o.*
                              from #otherdb o
                              where o.tabname = c.tabname
                                and o.tabsuid = c.tabsuid
                                and o.ttype   = c.ttype
                                and o.colname = c.colname)
         union
            select substring (c.tabname + ' (' + suser_name (c.tabsuid) + ')', 1, 29),
                   substring (c.colname, 1, 28),
                   substring (c.ctypetxt  +  ' '  +   c.nul, 1, 18)
            from #currdb c
            where c.tabsuid <> -1
              and exists     (select o.*
                              from #otherdb o
                              where o.tabname = c.tabname
                                and o.tabsuid = c.tabsuid
                                and o.ttype   = c.ttype)
              and not exists (select o.*
                              from #otherdb o
                              where o.tabname = c.tabname
                                and o.tabsuid = c.tabsuid
                                and o.ttype   = c.ttype
                                and o.colname = c.colname)
         order by 'TABLE/VIEW', 'COLUMN'
      end
   else
      begin
         print '    THERE ARE NO COLUMNS ONLY IN %1! FOR COMMON TABLES/VIEWS.',
               @CURRDB
      end

   print ' '

   -- COLUMN ATTRIBUTES --------------------------------------------------

   select c.tabname,
          c.tabsuid,
          c.colname,
          ctypetxt = c.ctypetxt  +  ' '  +   c.nul,
          otypetxt = o.ctypetxt  +  ' '  +   o.nul
   into #colattrib
   from #currdb c,
        #otherdb o
   where c.tabname = o.tabname
     and c.tabsuid = o.tabsuid
     and c.colname = o.colname
     and ((c.ctype  <> o.ctype)   or
          (c.clen   <> o.clen)    or
          (c.cprec  <> o.cprec)   or
          (c.cscale <> o.cscale)  or
          (c.nul    <> o.nul))

   if (exists (select *
               from #colattrib))
      begin
         print '    THERE ARE COLUMN ATTRIBUTE DIFFERENCES FOR COMMON TABLES/VIEWS.'

         print ' '

         print '%1!', @coldifftxt

            select 'TABLE/VIEW' = space (20),
                   'COLUMN'     = space (18),
                   'DATA TYPE'  = space (18),
                   'DATA TYPE ' = space (18)
            where 1 = 2
         union
            select substring (tabname, 1, 20),
                   substring (colname, 1, 18),
                   substring (otypetxt, 1, 18),
                   substring (ctypetxt, 1, 18)
            from #colattrib
            where tabsuid = -1
         union
            select substring (tabname + ' (' + suser_name (tabsuid) + ')', 1, 20),
                   substring (colname, 1, 18),
                   substring (otypetxt, 1, 18),
                   substring (ctypetxt, 1, 18)
            from #colattrib
            where tabsuid <> -1
         order by 'TABLE/VIEW', COLUMN
      end
   else
      begin
         print '    THERE ARE NO COLUMN ATTRIBUTE DIFFERENCES FOR COMMON TABLES/VIEWS.'
      end

   drop table #colattrib

   print ' '

   print ' '

   -- STORED PROCEDURE ATTRIBUTES ----------------------------------------

   select c.procname,
          c.procsuid,
          oostatus2 = o.ostatus2,
          opstatus  = o.pstatus,
          otxt      = convert (varchar (20), ' '),
          costatus2 = c.ostatus2,
          cpstatus  = c.pstatus,
          ctxt      = convert (varchar (20), ' ')
   into #procattrib
   from #currprocs c,
        #otherprocs o
   where c.procname = o.procname
     and c.procsuid = o.procsuid
     and c.typetxt  = 'stored procedure'
     and o.typetxt  = 'stored procedure'
     and (((c.ostatus2 & 16) <> (o.ostatus2 & 16))   or
          ((c.ostatus2 & 32) <> (o.ostatus2 & 32))   or
          ((c.pstatus & 64)  <> (o.pstatus & 64)))

   update #procattrib
   set otxt = 'chained'
   where (oostatus2 & 16) = 16

   update #procattrib
   set otxt = 'anymode'
   where (oostatus2 & 32) = 32

   update #procattrib
   set otxt = 'unchained'
   where otxt = ' '

   update #procattrib
   set otxt = otxt  +  ', recompile'
   where (opstatus & 64)  = 64

   update #procattrib
   set otxt = 'recompile'
   where otxt            = ' '
     and (opstatus & 64) = 64

   update #procattrib
   set ctxt = 'chained'
   where (costatus2 & 16) = 16

   update #procattrib
   set ctxt = 'anymode'
   where (costatus2 & 32) = 32

   update #procattrib
   set ctxt = 'unchained'
   where ctxt = ' '

   update #procattrib
   set ctxt = ctxt  +  ', recompile'
   where (cpstatus & 64)  = 64

   if (exists (select *
               from #procattrib))
      begin
         print '    THERE ARE ATTRIBUTE DIFFERENCES FOR COMMON STORED PRCEDURES.'

         print ' '

         print '%1!', @procdifftxt

            select 'STORED PROCEDURE' = space (30),
                   'ATTRIBUTE'        = space (20),
                   'ATTRIBUTE '       = space (20)
            where 1 = 2
         union
            select procname, otxt, ctxt
            from #procattrib
            where procsuid = -1
         union
            select substring (procname + ' (' + suser_name (procsuid) + ')', 1, 30),
                   otxt,
                   ctxt
            from #procattrib
            where procsuid <> -1
         order by 'STORED PROCEDURE'
      end
   else
      begin
         print '    THERE ARE NO ATTRIBUTE DIFFERENCES FOR COMMON STORED PROCEDURES.'
      end

   drop table #currprocs, #otherprocs

   print ' '

   print ' '

   -- INDEXES ------------------------------------------------------------

   select o.tabname, o.tabsuid, o.typetxt, o.keytxt
   into #other2inds
   from #otherinds o
   where exists     (select cdb.*
                     from #currdb cdb
                     where cdb.tabname = o.tabname
                       and cdb.tabsuid = o.tabsuid)
     and not exists (select c.*
                     from #currinds c
                     where c.tabname = o.tabname
                       and c.tabsuid = o.tabsuid
                       and c.status  = o.status
                       and c.key1    = o.key1
                       and c.key2    = o.key2
                       and c.key3    = o.key3
                       and c.key4    = o.key4
                       and c.key5    = o.key5
                       and c.key6    = o.key6
                       and c.key7    = o.key7
                       and c.key8    = o.key8)

   if (exists (select *
               from #other2inds))
      begin
         print '%1!', @othertxt5

            select 'TABLE'         = space (20),
                   'INDEX (INDID)' = space (24),
                   KEYS            = space (31)
            where 1 = 2
         union
            select substring (tabname, 1, 20),
                   substring (typetxt, 1, 24),
                   substring (keytxt, 1, 31)
            from #other2inds
            where tabsuid = -1
         union
            select substring (tabname + ' (' + suser_name (tabsuid) + ')', 1, 20),
                   substring (typetxt, 1, 24),
                   substring (keytxt, 1, 31)
            from #other2inds
            where tabsuid <> -1
         order by 'TABLE', 'INDEX (INDID)'
      end
   else
      begin
         print '    THERE ARE NO INDEXES ONLY IN %1! FOR COMMON TABLES.',
               @OTHERDB
      end

   drop table #other2inds

   print ' '

   select c.tabname, c.tabsuid, c.typetxt, c.keytxt
   into #curr2inds
   from #currinds c
   where exists     (select odb.*
                     from #otherdb odb
                     where odb.tabname = c.tabname
                       and odb.tabsuid = c.tabsuid)
     and not exists (select o.*
                     from #otherinds o
                     where c.tabname = o.tabname
                       and c.tabsuid = o.tabsuid
                       and c.status  = o.status
                       and c.key1    = o.key1
                       and c.key2    = o.key2
                       and c.key3    = o.key3
                       and c.key4    = o.key4
                       and c.key5    = o.key5
                       and c.key6    = o.key6
                       and c.key7    = o.key7
                       and c.key8    = o.key8)

   if (exists (select *
               from #curr2inds))
      begin
         print '%1!', @currtxt5

            select 'TABLE'         = space (20),
                   'INDEX (INDID)' = space (24),
                   KEYS            = space (31)
            where 1 = 2
         union
            select substring (tabname, 1, 20),
                   substring (typetxt, 1, 24),
                   substring (keytxt, 1, 31)
            from #curr2inds
            where tabsuid = -1
         union
            select substring (tabname + ' (' + suser_name (tabsuid) + ')', 1, 20),
                   substring (typetxt, 1, 24),
                   substring (keytxt, 1, 31)
            from #curr2inds
            where tabsuid <> -1
         order by 'TABLE', 'INDEX (INDID)'
      end
   else
      begin
         print '    THERE ARE NO INDEXES ONLY IN %1! FOR COMMON TABLES.',
               @CURRDB
      end

   drop table #curr2inds

   print ' '

   print ' '

   -- LOCK CONFIGURATIONS ------------------------------------------------

   if (exists (select clc.*
               from #bothlockcfg clc,
                    #bothlockcfg olc
               where olc.tabname  = clc.tabname
                 and olc.dbname  <> clc.dbname
                 and olc.txt     <> clc.txt))
      begin
         print '    THERE ARE LOCK CONFIGURATION DIFFERENCES FOR COMMON TABLES.'

         print ' '

         print '%1!', @lockcfgtxt

         select 'OBJECT'              = substring (olc.tabname, 1, 17),
                'LOCK CONFIGURATION'  = substring (olc.txt, 1, 29),
                'LOCK CONFIGURATION ' = substring (clc.txt, 1, 29)
         from #bothlockcfg clc,
              #bothlockcfg olc
         where olc.tabname  = clc.tabname
           and clc.dbname   = @CURRDB
           and olc.dbname   = @OTHERDB
           and olc.txt     <> clc.txt
         order by 'OBJECT'
      end
   else
      begin
         print '    THERE ARE NO LOCK CONFIGURATION DIFFERENCES FOR COMMON TABLES.'
      end

   print ' '

   print ' '

   -- CACHED OBJECTS -----------------------------------------------------

   if (exists (select o.*
               from #bothcache o
               where o.dbname = @OTHERDB
                 and not exists (select c.*
                                 from #bothcache c
                                 where c.dbname  = @CURRDB
                                   and c.objname = o.objname
                                   and c.objsuid = o.objsuid
                                   and c.type    = o.type)))
      begin
         print '%1!', @othertxt7

         select OBJECT = substring (o.prntname, 1, 35),
                TYPE   = o.type,
                CACHE  = substring (o.cachename, 1, 30)
         from #bothcache o
         where o.dbname = @OTHERDB
           and not exists (select c.*
                           from #bothcache c
                           where c.dbname  = @CURRDB
                             and c.objname = o.objname
                             and c.objsuid = o.objsuid
                             and c.type    = o.type)
         order by OBJECT, TYPE
      end
   else
      begin
         print '    THERE ARE NO CACHE BINDINGS ONLY IN %1!.', @OTHERDB
      end

   print ' '

   if (exists (select c.*
               from #bothcache c
               where c.dbname = @CURRDB
                 and not exists (select o.*
                                 from #bothcache o
                                 where o.dbname  = @OTHERDB
                                   and o.objname = c.objname
                                   and o.objsuid = c.objsuid
                                   and o.type    = c.type)))
      begin
         print '%1!', @currtxt7

         select OBJECT = substring (c.prntname, 1, 35),
                TYPE   = c.type,
                CACHE  = substring (c.cachename, 1, 30)
         from #bothcache c
         where c.dbname = @CURRDB
           and not exists (select o.*
                           from #bothcache o
                           where o.dbname  = @OTHERDB
                             and o.objname = c.objname
                             and o.objsuid = c.objsuid
                             and o.type    = c.type)
         order by OBJECT, TYPE
      end
   else
      begin
         print '    THERE ARE NO CACHE BINDINGS ONLY IN %1!.', @CURRDB
      end

   print ' '

   if (exists (select *
               from #bothcache c,
                    #bothcache o
               where c.dbname     = @CURRDB
                 and o.dbname     = @OTHERDB
                 and c.objsuid    = o.objsuid
                 and c.objname    = o.objname
                 and c.cachename <> o.cachename))
      begin
         print '    THERE ARE DIFFERENT CACHES FOR COMMON OBJECTS.'

         print ' '

         print '%1!', @cachetxt

         select OBJECT   = substring (c.prntname, 1, 24),
                TYPE     = c.type,
                'CACHE'  = substring (o.cachename, 1, 20),
                'CACHE ' = substring (c.cachename, 1, 20)
         from #bothcache c,
              #bothcache o
         where c.dbname     = @CURRDB
           and o.dbname     = @OTHERDB
           and c.objsuid    = o.objsuid
           and c.objname    = o.objname
           and c.cachename <> o.cachename
         order by OBJECT, TYPE
      end
   else
      begin
         print '    CACHE BINDINGS ARE IDENTICAL FOR COMMON OBJECTS.'
      end

 print ' '
go
sp_procxmode sp_dbdiff_print, unchained
go

drop table #bothcache, #bothlockcfg
go
drop table #currdb, #currprocs, #currinds
go
drop table #otherdb, #otherprocs, #otherinds
go

create procedure sp_dbdiff (@OTHERDB  varchar (30))  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

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

   if (@@isolation > 1)
      begin
         set transaction isolation level 1
      end

   print ' '

   declare @cmdstring  varchar (60),
           @currdb     varchar (30),
           @dbowner    varchar (30),
           @transfer   char (1)

   select @dbowner = suser_name (suid)
   from master.dbo.sysdatabases
   where name = @OTHERDB

   if (@dbowner IS NULL)
      begin
         print '    DATABASE <%1!> DOES NOT EXIST.', @OTHERDB

         print ' '

         return 1
      end

   if (@currdb = @OTHERDB)
      begin
         print '    DATABASES %1! AND %2! MUST BE DIFFERENT.', @currdb, @OTHERDB

         print ' '

         return 1
      end

   execute  sp_date

   select @currdb = db_name ()

   create table #bothcache
      (objid      int            not null,
       objsuid    smallint       not null,
       dbname     varchar (30)   not null,
       objname    varchar (30)   not null,
       prntname   varchar (94)   not null,
       type       char (10)      not null,
       cachename  varchar (120)  not null,
       indid      tinyint        not null)

   create table #bothlockcfg
     (sort        tinyint        not null,
      dbname      varchar (30)   not null,
      tabname     varchar (30)   not null,
      txt         varchar (33)   not null)

   create table #currdb
      (tabname    varchar (30)   not null,
       tabsuid    smallint       not null,
       tabid      int            not null,
       ttype      tinyint        not null,
       colname    varchar (30)   not null,
       cutype     tinyint        not null,
       ctype      tinyint        not null,
       clen       tinyint        not null,
       cprec      tinyint        null,
       cscale     tinyint        null,
       ctypetxt   varchar (16)   not null,
       nul        char (1)       not null)

   create table #otherdb
      (tabname    varchar (30)   not null,
       tabsuid    smallint       not null,
       tabid      int            not null,
       ttype      tinyint        not null,
       colname    varchar (30)   not null,
       cutype     tinyint        not null,
       ctype      tinyint        not null,
       clen       tinyint        not null,
       cprec      tinyint        null,
       cscale     tinyint        null,
       ctypetxt   varchar (16)   not null,
       nul        char (1)       not null)

   create table #currprocs
      (procname   varchar (30)   not null,
       procsuid   smallint       not null,
       trigname   varchar (30)   null,
       ostatus    smallint       not null,
       ostatus2   smallint       not null,
       pstatus    smallint       not null,
       typetxt    varchar (16)   not null)

   create table #otherprocs
      (procname   varchar (30)   not null,
       procsuid   smallint       not null,
       trigname   varchar (30)   null,
       ostatus    tinyint        not null,
       ostatus2   smallint       not null,
       pstatus    smallint       not null,
       typetxt    varchar (16)   not null)

   create table #currinds
      (tabname    varchar (30)   not null,
       tabsuid    smallint       not null,
       indid      tinyint        not null,
       status     smallint       not null,
       typetxt    varchar (75)   not null,
       keytxt     varchar (75)   not null,
       key1       varchar (30)   not null,
       key2       varchar (30)   null,
       key3       varchar (30)   null,
       key4       varchar (30)   null,
       key5       varchar (30)   null,
       key6       varchar (30)   null,
       key7       varchar (30)   null,
       key8       varchar (30)   null)

   create table #otherinds
      (tabname    varchar (30)   not null,
       tabsuid    smallint       not null,
       indid      tinyint        not null,
       status     smallint       not null,
       typetxt    varchar (75)   not null,
       keytxt     varchar (75)   not null,
       key1       varchar (30)   not null,
       key2       varchar (30)   null,
       key3       varchar (30)   null,
       key4       varchar (30)   null,
       key5       varchar (30)   null,
       key6       varchar (30)   null,
       key7       varchar (30)   null,
       key8       varchar (30)   null)

   print ' '

   if (user_id () = 1)
      begin
         print '    UPDATING STATISTICS ON SYSTEM TABLES IN %1!.', @currdb

         print ' '

         update statistics sysattributes

         update statistics syscolumns

         update statistics syscomments

         update statistics sysindexes

         update statistics sysobjects

         update statistics sysprocedures

         update statistics systypes

         update statistics sysusers
      end

   select @dbowner  = suser_name (suid),
          @transfer = 'Y'
   from master.dbo.sysdatabases
   where name = @currdb

   print '    LOADING WORK TABLES FROM DATABASE %1! OWNED BY %2!.',
         @currdb, @dbowner

   execute  sp_dbdiff_load @transfer

   print ' '

   select @cmdstring = @OTHERDB  +  '.dbo.sp_dbdiff_load',
          @transfer  = 'N'

   print '    LOADING WORK TABLES FROM DATABASE %1! OWNED BY %2!.',
         @OTHERDB, @dbowner

   execute  @cmdstring @transfer

   print ' '

   create unique clustered index UC on #bothcache (objname, dbname, type)

   update statistics #bothcache

   create unique clustered index UC on #bothlockcfg (tabname, dbname)

   update statistics #bothlockcfg

   create unique clustered index UC on #currdb (tabname, tabsuid, colname)

   update statistics #currdb

   create unique clustered index UC on #currprocs (procname, procsuid, trigname)

   update statistics #currprocs

   create        clustered index UC on #currinds (tabname, tabsuid, key1, key2, key3, key4)

   update statistics #currinds

   create unique clustered index UC on #otherdb (tabname, tabsuid, colname)

   update statistics #otherdb

   create unique clustered index UC on #otherprocs (procname, procsuid, trigname)

   update statistics #otherprocs

   create        clustered index UC on #otherinds (tabname, tabsuid, key1, key2, key3, key4)

   update statistics #otherinds

   execute  sp_dbdiff_print @currdb, @OTHERDB
go
sp_addcomment sp_dbdiff, 17, 'Lists differences between the current database and OTHERDB'
go
sp_procxmode sp_dbdiff, anymode
go

create procedure sp_defaultcols (@DEFAULT varchar (30) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @LF         char (1),
           @LFas       char (4),
           @LFasLF     char (4),
           @as         char (4),
           @asLF       char (4),
           @dfltcnt    tinyint,
           @dfltid     int,
           @maxcolid   tinyint,
           @sequence   tinyint

   select @LF = char (10)                             -- Line Feed

   select @as     = ' AS ',
          @asLF   = ' AS' + @LF,
          @LFas   = @LF + 'AS ',
          @LFasLF = @LF + 'AS' + @LF

   create table #defaultcols
      (dfltname  varchar (60)  not null,
       sortname  varchar (60)  not null,
       dfltid    int           not null,
       dfltuid   smallint      not null,
       tabname   varchar (60)  null,
       tabid     int           null,
       tabuid    smallint      null,
       coltext   varchar (50)  null,
       colid     smallint      not null)

   if (@DEFAULT IS NULL)
      begin
         if (exists (select o.id
                     from sysobjects o,
                          sysprocedures p
                     where (o.sysstat & 15)   = 6
                       and o.id               = p.id
                       and p.sequence         = 1
                       and (p.status & 4096) <> 4096))
            begin
               select @dfltcnt = 1
            end
         else
            begin
               print '    THERE ARE NO USER-NAMED DEFAULTS.'

               print ' '
            end
      end
   else if (charindex ('.', @DEFAULT) > 0)
      begin
         select @dfltid = object_id (@DEFAULT)
         from sysobjects o,
              sysprocedures p
         where o.id               = object_id (@DEFAULT)
           and (o.sysstat & 15)   = 6
           and o.id               = p.id
           and p.sequence         = 1
           and (p.status & 4096) <> 4096

         if (@dfltid IS NULL)
            begin
               print '    USER-NAMED DEFAULT <%1!> DOES NOT EXIST.', @DEFAULT

               print ' '
            end
         else
            begin
               select @dfltcnt = 1
            end
      end
   else
      begin
         select @dfltcnt = count (o.id)
         from sysobjects o,
              sysprocedures p
         where o.name             = @DEFAULT
           and (o.sysstat & 15)   = 6
           and o.id               = p.id
           and p.sequence         = 1
           and (p.status & 4096) <> 4096

         if (@dfltcnt = 0)
            begin
               print '    USER-NAMED DEFAULT <%1!> DOES NOT EXIST.', @DEFAULT

               print ' '
            end
         else if (@dfltcnt = 1)
            begin
               select @dfltid = object_id (@DEFAULT)
               from sysobjects o,
                    sysprocedures p
               where o.name             = @DEFAULT
                 and (o.sysstat & 15)   = 6
                 and o.id               = p.id
                 and p.sequence         = 1
                 and (p.status & 4096) <> 4096
            end
         else
            begin
               print '    USER-NAMED DEFAULT <%1!> IS NOT UNIQUE.', @DEFAULT

               print ' '

               print '    RETRY WITH AN OWNER QUALIFICATION.'

               print ' '
            end
      end

   if (@dfltcnt = 1)
      begin
         insert into #defaultcols
            select o.name, ' ', o.id, o.uid, NULL, NULL, NULL, NULL, 0
            from sysobjects o,
                 sysprocedures p
            where ((@DEFAULT IS NULL)  or  (o.id = @dfltid))
              and (o.sysstat & 15)   = 6
              and o.id               = p.id
              and p.sequence         = 1
              and (p.status & 4096) <> 4096
              and not (exists (select c.id
                               from syscolumns c
                               where c.cdefault = o.id))

         insert into #defaultcols
            select od.name, ' ', od.id, od.uid, ot.name, ot.id, ot.uid, c.name,
                   c.colid
            from syscomments cm,
                 syscolumns c,
                 sysobjects od,
                 sysobjects ot,
                 sysprocedures p
            where ((@DEFAULT IS NULL)  or  (od.id = @dfltid))
              and (od.sysstat & 15)  = 6
              and p.id               = od.id
              and p.sequence         = 1
              and (p.status & 4096) <> 4096
              and c.cdefault         = od.id
              and ot.id              = c.id
              and cm.id              = od.id
              and cm.colid           = 1
              and cm.texttype        = 0
              and (charindex (@as, upper (cm.text))     > 0  or
                   charindex (@asLF, upper (cm.text))   > 0  or
                   charindex (@LFas, upper (cm.text))   > 0  or
                   charindex (@LFasLF, upper (cm.text)) > 0)

         update #defaultcols
         set dfltname = d.dfltname + ' (' + user_name (d.dfltuid) + ')'
         from #defaultcols d
         where d.dfltuid <> 1
            or 1          < (select count (distinct o2.uid)
                             from sysobjects o2,
                                  sysprocedures p2
                             where o2.name             = d.dfltname
                               and (o2.sysstat & 15)   = 6
                               and p2.id               = o2.id
                               and p2.sequence         = 1
                               and (p2.status & 4096) <> 4096)

         update #defaultcols
         set tabname = d.tabname + ' [' + user_name (d.tabuid) + ']'
         from #defaultcols d
         where d.tabuid <> 1
            or 1         < (select count (distinct o2.uid)
                            from sysobjects o2
                            where o2.name            = d.tabname
                              and (o2.sysstat & 15) in (1, 2, 3))

         select @maxcolid = max (colid),
                @sequence = 1
         from #defaultcols

         update #defaultcols
         set colid = -1
         from #defaultcols d
         where d.colid > 0
           and d.colid = (select min (d2.colid)
                          from #defaultcols d2
                          where d2.dfltid = d.dfltid
                            and d2.tabid  = d.tabid
                            and d2.colid  > 0)

         while (@sequence <= @maxcolid)
            begin
               update #defaultcols
               set coltext = substring (d.coltext + ', ' + d2.coltext, 1, 50)
               from #defaultcols d,
                    #defaultcols d2
               where d.colid   = -1
                 and d2.dfltid = d.dfltid
                 and d2.tabid  = d.tabid
                 and d2.colid  = @sequence

               select @sequence = @sequence + 1
            end

         if (@DEFAULT IS NULL)
            begin
               update #defaultcols
               set sortname = dfltname

  update #defaultcols
  set dfltname = ' '
  from #defaultcols d1
  where exists (select *
                from #defaultcols d2
                where d2.sortname = d1.sortname
                  and d2.tabname  = d1.tabname
                  and d2.tabname > (select min (d3.tabname)
                                    from #defaultcols d3
                                    where d3.sortname = d2.sortname))

               select 'DEFAULT'    = substring (dfltname, 1, 25),
                      'TABLE/VIEW' = isnull (substring (tabname, 1, 25), '-'),
                      'COLUMNS'    = isnull (substring (coltext, 1, 25), '-')
               from #defaultcols
               where colid <= 0
               order by sortname, tabname
            end
         else if (exists (select *
                          from #defaultcols
                          where tabid IS NOT NULL))
            begin
               select 'TABLE/VIEW' = substring (tabname, 1, 35),
                      'COLUMNS'    = substring (coltext, 1, 41)
               from #defaultcols
               where colid <= 0
               order by tabname
            end
         else
            begin
               print '    DEFAULT <%1!> IS NOT USED.', @DEFAULT

               print ' '
            end
      end
go
sp_addcomment sp_defaultcols, 17, 'Lists columns with user-named DEFAULT or (default) all columns with user-named defaults'
go
sp_procxmode sp_defaultcols, anymode
go

create procedure sp_depend1 (@OBJECT varchar (63)) as

   -- Sybase does not update sysdepends.readobj correctly when a stored
   -- procedure or trigger reads an object ... but resultobj will be "off".

   set ansinull                      on
   set string_rtruncation            on

   print ' '

   declare @objectid  int,
           @systype   tinyint

   select @objectid = id,
          @systype  = (sysstat & 15)
   from sysobjects
   where id              = object_id (@OBJECT)
     and (sysstat & 15) in (1, 2, 3, 4)

   if (@objectid IS NULL)
      begin
         print '    PROC/TABLE/VIEW <%1!> DOES NOT EXIST.', @OBJECT

         print ' '
      end
   else if (exists (select *
                    from sysdepends
                    where depid = @objectid))
      begin
         if (@systype = 4)
            begin
               print '    USE sp_backexec FOR MORE INFO ON CALLS TO <%1!>.',
                      @OBJECT

               print ' '

                  select 'STORED PROCEDURE' = space (30),
                         DEPENDENT          = space (30)
                  where 1 = 2
               union
                  select o.name,
                         substring (@OBJECT, 1, 30)
                  from sysdepends d,
                       sysobjects o
                  where d.depid          = @objectid
                    and o.id             = d.id
                    and (o.sysstat & 15) = 4
                    and o.uid            = 1
                    and 1                = (select count (o2.id)
                                            from sysobjects o2
                                            where o2.name = o.name)
               union
                  select substring (o.name
                                    +  ' ('
                                    +  user_name (o.uid)
                                    +  ')',
                                    1,
                                    30),
                         substring (@OBJECT, 1, 30)
                  from sysdepends d,
                       sysobjects o
                  where d.depid           = @objectid
                    and o.id              = d.id
                    and (o.sysstat & 15)  = 4
                    and ((o.uid <> 1)  or
                         (1      < (select count (o2.id)
                                    from sysobjects o2
                                    where o2.name           = o.name
                                      and (o2.sysstat & 15) = 4)))
               order by 'STORED PROCEDURE', DEPENDENT
            end
         else
            begin
               create table #depend1
                  (id          int           not null,
                   uid         smallint      not null,
                   printname   varchar (26)  not null,
                   type        tinyint       not null,
                   readflag    tinyint       not null,
                   writeflag   tinyint       not null,
                   mode        varchar (10)  not null)

               insert into #depend1
                  select o.id, o.uid, o.name, 2, 0, 0, '-'
                  from sysdepends d,
                       sysobjects o
                  where d.depid          = @objectid
                    and o.id             = d.id
                    and (o.sysstat & 15) = 2

               insert into #depend1
                  select o.id, o.uid, o.name, (o.sysstat & 15), d.readobj,
                         d.resultobj, ' '
                  from sysdepends d,
                       sysobjects o
                  where d.depid           = @objectid
                    and o.id              = d.id
                    and (o.sysstat & 15) in (4, 8)

               update #depend1
               set mode = 'read'
               where type      <> 2
                 and writeflag  = 0

               update #depend1
               set mode = 'write'
               where type      <> 2
                 and readflag   = 0
                 and writeflag  = 1

               update #depend1
               set mode = 'read/write'
               where type      <> 2
                 and readflag   = 1
                 and writeflag  = 1

               update #depend1
               set printname = d.printname  +  ' ('  +  user_name (d.uid)  +  ')'
               from #depend1 d
               where d.uid <> 1
                  or 1      < (select count (o.id)
                               from sysobjects o
                               where o.name           = d.printname
                                 and (o.sysstat & 15) = d.type)

               select OBJECT       = substring (@OBJECT, 1, 16),
                      'IS USED BY' = substring (printname, 1, 26),
                      TYPE         = substring (vO.name, 1, 16),
                      MODE         = mode
               from #depend1 d,
                    sybsystemprocs.dbo.usptvalues vO
               where vO.number = d.type
                 and vO.type   = 'O'
               order by printname, vO.name
            end
      end
   else
      begin
         print '    THERE ARE NO PROCS/TRIGGERS/VIEWS DEPENDENT UPON <%1!>.',
               @OBJECT

         print ' '
      end
go
sp_procxmode sp_depend1, unchained
go

create procedure sp_depend2 (@OBJECT1 varchar (63),
                             @OBJECT2 varchar (63))  as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

   if (object_id (@OBJECT1) = object_id (@OBJECT2))
      begin
         print '    <%1!> AND <%2!> ARE IDENTICAL.  USE sp_depend1.',
               @OBJECT1, @OBJECT2

         print ' '
      end
   else if (exists (select o.*
               from sysdepends d1,
                    sysdepends d2,
                    sysobjects o
               where d1.depid = object_id (@OBJECT1)
                 and o.id     = d1.id
                 and d2.depid = object_id (@OBJECT2)
                 and o.id     = d2.id))
      begin
         print '    <%1!> AND <%2!> HAVE COMMON DEPENDENTS.',
               @OBJECT1, @OBJECT2

         print ' '

         select distinct OBJECT = o.name,
                         OWNER  = substring (user_name (o.uid), 1, 29),
                         TYPE   = substring (vO.name, 1, 16)
         from sysdepends d1,
              sysdepends d2,
              sysobjects o,
              sybsystemprocs.dbo.usptvalues vO
         where d1.depid  = object_id (@OBJECT1)
           and o.id      = d1.id
           and d2.depid  = object_id (@OBJECT2)
           and o.id      = d2.id
           and vO.number = convert (int, o.sysstat & 15)
           and vO.type   = 'O'
         order by o.name, user_name (o.uid)
      end
   else
      begin
         print '    THERE ARE NO PROCS/TRIGGERS/VIEWS DEPENDENT UPON <%1!> AND <%2!>.',
               @OBJECT1, @OBJECT2

         print ' '
      end
go
sp_procxmode sp_depend2, unchained
go

create procedure sp_depend3 (@OBJECT1 varchar (63),
                             @OBJECT2 varchar (63),
                             @OBJECT3 varchar (63))  as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @titlestring  varchar (37)

   if ((object_id (@OBJECT1) = object_id (@OBJECT2))  or
       (object_id (@OBJECT2) = object_id (@OBJECT3))  or
       (object_id (@OBJECT3) = object_id (@OBJECT1)))
      begin
         print '    <%1!> AND/OR <%2!> AND/OR <%3!> ARE IDENTICAL.',
               @OBJECT1, @OBJECT2, @OBJECT3

         print ' '

         print '    USE sp_depend1 OR sp_depend2.'

         print ' '
      end
   else if (exists (select o.*
               from sysdepends d1,
                    sysdepends d2,
                    sysdepends d3,
                    sysobjects o
               where d1.depid = object_id (@OBJECT1)
                 and o.id     = d1.id
                 and d2.depid = object_id (@OBJECT2)
                 and o.id     = d2.id
                 and d3.depid = object_id (@OBJECT3)
                 and o.id     = d3.id))
      begin
         print '    <%1!>, <%2!>, AND <%3!> HAVE COMMON DEPENDENTS.',
               @OBJECT1, @OBJECT2, @OBJECT3

         print ' '

         select distinct OBJECT = o.name,
                         OWNER  = substring (user_name (o.uid), 1, 29),
                         TYPE   = substring (vO.name, 1, 16)
         from sysdepends d1,
              sysdepends d2,
              sysdepends d3,
              sysobjects o,
              sybsystemprocs.dbo.usptvalues vO
         where d1.depid  = object_id (@OBJECT1)
           and o.id      = d1.id
           and d2.depid  = object_id (@OBJECT2)
           and o.id      = d2.id
           and d3.depid  = object_id (@OBJECT3)
           and o.id      = d3.id
           and vO.number = convert (int, o.sysstat & 15)
           and vO.type   = 'O'
         order by o.name, user_name (o.uid)
      end
   else
      begin
         print '    THERE ARE NO PROCS/TRIGGERS/VIEWS DEPENDENT UPON <%1!>, <%2!> AND <%3!>.',
               @OBJECT1, @OBJECT2, @OBJECT3

         print ' '
      end
go
sp_procxmode sp_depend3, unchained
go


create procedure sp_dependx (@OBJECT1 varchar (63),
                             @OBJECT2 varchar (63) = NULL,
                             @OBJECT3 varchar (63) = NULL)  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 ' '

         print '    <sp_dependx> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   if ((@OBJECT2 IS NULL)  and  (@OBJECT3 IS NULL))
      begin
         execute sp_depend1 @OBJECT1
      end
   else if (@OBJECT3 IS NULL)
      begin
         execute sp_depend2 @OBJECT1, @OBJECT2
      end
   else if (@OBJECT2 IS NULL)                        -- Weird!  But ...
      begin
         execute sp_depend2 @OBJECT1, @OBJECT3
      end
   else
      begin
         execute sp_depend3 @OBJECT1, @OBJECT2, @OBJECT3
      end
go
sp_addcomment sp_dependx, 17, 'Lists procs/triggers/views dependent upon OBJECT1 [, OBJECT2 [, OBJECT3]]'
go
sp_procxmode sp_dependx, anymode
go

------------ Temporary table needed for creation of sp_loginwhere_db subroutine

create table #loginwhere
   (dbname    varchar (30)  not null,
    uid       smallint      not null,
    username  varchar (30)  not null,
    alias     tinyint       not null,
    ownedcnt  int           null)
go

create procedure sp_loginwhere_db (@SUIDCHAR varchar (10))  as
   set ansinull                      on
   set string_rtruncation            on

   declare @suid   smallint,
           @uid    smallint,
           @uname  varchar (30)

   select @suid = convert (smallint, @SUIDCHAR)

   select @uid   = uid,
          @uname = name
   from sysusers
   where suid = @suid

   if (@uid IS NULL)
      begin
         select @uid   = u.uid,
                @uname = u.name
         from sysalternates a,
              sysusers u
         where a.suid = @suid
           and u.suid = a.altsuid

         if (@uid IS NOT NULL)
            begin
               insert into #loginwhere
                  select db_name (), @uid, @uname + ' #', 1, 0
            end
      end
   else
      begin
         insert into #loginwhere
            select db_name (), @uid, @uname, 0, count (id)
            from sysobjects
            where uid = @uid

         update #loginwhere
         set ownedcnt = (select l.ownedcnt + isnull (count (o.id), 0)
                         from sysobjects o
                         where l.dbname = db_name ()
                           and o.uid    = @uid)
         from #loginwhere l
         where l.dbname = db_name ()

         update #loginwhere
         set ownedcnt = (select l.ownedcnt + isnull (count (p.uid), 0)
                         from sysprotects p
                         where l.dbname  = db_name ()
                           and p.grantor = @uid)
         from #loginwhere l
         where l.dbname = db_name ()

         update #loginwhere
         set ownedcnt = (select l.ownedcnt + isnull (count (t.type), 0)
                         from systypes t
                         where l.dbname = db_name ()
                           and t.uid    = @uid)
         from #loginwhere l
         where l.dbname = db_name ()

         update #loginwhere
         set ownedcnt = (select l.ownedcnt + isnull (count (m.error), 0)
                         from sysusermessages m
                         where l.dbname = db_name ()
                           and m.uid    = @uid)
         from #loginwhere l
         where l.dbname = db_name ()
      end
go
sp_procxmode sp_loginwhere_db, unchained
go

drop table #loginwhere
go


create procedure sp_loginwhere (@LOGIN  varchar (30))  as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

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

   create table #loginwhere
      (dbname    varchar (30)  not null,
       uid       smallint      not null,
       username  varchar (30)  not null,
       alias     tinyint       not null,
       ownedcnt  int           null)

   select @cmdstring = 'sp_loginwhere_db'

   if (@LOGIN = 'guest')
      begin
         select @suidchar  = '-1'
      end
   else
      begin
         select @suidchar  = convert (varchar (10), suid)
         from master.dbo.syslogins
         where name = @LOGIN
      end


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

         if (exists (select *
                     from #loginwhere))
            begin
               if (exists (select *
                           from #loginwhere
                           where alias = 1))
                  begin
                     print '    # INDICATES AN ALIAS FOR %1!.', @LOGIN

                     print ' '
                  end

               print '                                        OWNED'

               select 'DATABASE'  = substring (dbname, 1, 18),
                      'USER NAME' = substring (username, 1, 18),
                      OBJECTS     = isnull (substring (convert (char (10), convert (money, ownedcnt), 1), 1, 7), '      -')
               from #loginwhere
               order by 'DATABASE'
            end
         else
            begin
               print '    LOGIN %1! IS NOT A USER IN ANY DATABASE.', @LOGIN
            end
      end

   print ' '

   drop table #loginwhere
go
sp_addcomment sp_loginwhere, 17, 'Lists all databases where LOGIN is a user'
go
sp_procxmode sp_loginwhere, anymode
go

create procedure sp_ownedby (@USER       varchar (30),
                             @DROP_FLAG  varchar (1)  = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @alias         varchar (30),
           @dbowner_flag  tinyint,
           @suid          smallint,
           @uid           smallint

   create table #ownedby
      (objname    varchar (30)  not null,
       objtype    varchar (95)  not null,
       crdate     varchar (17)  null,
       class      tinyint       not null,
       sort       varchar (90)  not null)

   -- @USER COULD BE (1) "sa", (2) A LOGIN NAME THAT IS THE DATABASE OWNER,
   -- (3) "dbo", (4) AN ALIAS, OR (5) A USER.

   select @dbowner_flag = 0,
          @suid         = suser_id (@USER)

   if (@suid = 1)
      begin
         select @uid = uid
         from sysusers
         where suid = 1

         if ((@uid IS NULL)  and
             (exists (select *
                      from systhresholds
                      where suid = 1)))
            begin
               if (@DROP_FLAG IS NULL)
                  begin
                     select 'THRESHOLD PROCEDURE' = t.proc_name,
                            SEGMENT               = s.name,
                            'FREE SPACE'          = t.free_space
                     from syssegments s,
                          systhresholds t
                     where t.suid    = 1
                       and s.segment = t.segment
                     order by 'THRESHOLD PROCEDURE', SEGMENT, 'FREE SPACE'
                  end
               else
                  begin
                     insert into #ownedby
                        select t.proc_name,
                               'sp_dropthreshold '
                               +  db_name ()
                               +  ', '
                               +  s.name
                               +  ', '
                               +  convert (varchar (10), t.free_space),
                               NULL,
                               1,
                               ' '
                        from syssegments s,
                             systhresholds t
                        where t.suid    = 1
                          and s.segment = t.segment

                     update #ownedby
                     set sort = objtype

                     insert into #ownedby
                        select ' ', 'go', crdate, class,
                               convert (char (64), sort) + 'zz'
                        from #ownedby

                     select 'DROP TEXT' = objtype
                     from #ownedby
                     where objtype <> 'database'
                     order by class, sort
                  end
            end
      end
   else if ((db_id () = 1)                  and
            (exists (select *
                     from master.dbo.sysdatabases
                     where suid = @suid))   and
            (not exists (select *
                         from master.dbo.sysusers
                         where suid = @suid)))
      begin
         select @dbowner_flag = 1

         print '    <%1!> ONLY OWNS DATABASES AND IS NOT A USER.', @USER

         print ' '

         select distinct 'DATABASE'    = d.name,
                         CREATED       = convert (char (8), d.crdate, 11)
                                         +  ' '
                                         +  convert (char (8), d.crdate, 8),
                         'TOTAL PAGES' = substring (convert (char (14),
                                                             convert (money,
                                                                      sum (u.size)),
                                                             1),
                                                    1,
                                                    11)
         from master.dbo.sysdatabases d,
              master.dbo.sysusages u
         where d.suid = @suid
           and u.dbid = d.dbid
         group by d.dbid
         having d.suid = @suid
            and u.dbid = d.dbid
         order by d.name

         print ' '

         select'TOTAL DB PAGES' = substring (convert (char (17),
                                                      convert (money,
                                                               sum (u.size)),
                                                      1),
                                             1,
                                             14)
         from master.dbo.sysdatabases d,
              master.dbo.sysusages u
         where d.suid = @suid
           and u.dbid = d.dbid

         print ' '
      end

    if (exists (select *
               from master.dbo.sysdatabases
               where dbid = db_id ()
                 and suid = @suid))
      begin
         print '    %1! IS THE DATABASE OWNER.', @USER

         print ' '

         select @alias = 'dbo',
                @uid   = 1
      end
   else
      begin
         select @uid  = user_id (@USER)

         if (@uid = 1)
            begin
               select @suid = suid
               from sysusers
               where uid = 1
            end
         else if (@uid IS NULL)
            begin
               select @alias = u.name,
                      @suid  = u.suid,
                      @uid   = u.uid
               from sysalternates a,
                    sysusers u
               where a.suid = @suid
                 and u.suid = a.altsuid

               if (@alias IS NULL)
                  begin
                     if (@dbowner_flag = 0)
                        begin
                           print '    <%1!> IS NOT A USER.', @USER

                           print ' '
                        end
                  end
               else
                  begin
                     print '    %1! IS AN ALIAS FOR %2!.', @USER, @alias

                     print ' '
                  end
            end
      end

   if (@uid IS NOT NULL)
      begin
         insert into #ownedby
            select o.name,
                   substring (vO.name, 1, 16),
                   convert (char (8), o.crdate, 11)
                   +  ' '
                   +  convert (char (8), o.crdate, 8),
                   vO.number,
                   ' '
            from sysobjects o,
                 sybsystemprocs.dbo.usptvalues vO
            where o.uid      = @uid
              and vO.number  = convert (int, o.sysstat & 15)
              and vO.number in (2, 3, 4, 8)
              and vO.type    = 'O'

         insert into #ownedby
            select o.name,
                   'default',
                   convert (char (8), o.crdate, 11)
                    +  ' '
                    +  convert (char (8), o.crdate, 8),
                   12,
                   ' '
            from sysobjects o,
                 sysprocedures p
            where o.uid              = @uid
              and (o.sysstat & 15)   = 6
              and p.id               = o.id
              and p.sequence         = 1
              and (p.status & 4096) <> 4096

         insert into #ownedby
            select distinct substring (o.name  +  ' (' +  user_name (o.uid)  +  ')', 1, 30),
                            'grant/revoke',
                            NULL,
                            1,
                            ' '
            from sysobjects o,
                 sysprotects p
            where p.grantor  = @uid
              and o.id       = p.id
              and o.uid     <> @uid

         insert into #ownedby
            select o.name,
                   'rule',
                   convert (char (8), o.crdate, 11)
                    +  ' '
                    +  convert (char (8), o.crdate, 8),
                   13,
                   ' '
            from sysobjects o,
                 sysprocedures p
            where o.uid              = @uid
              and (o.sysstat & 15)   = 7
              and p.id               = o.id
              and p.sequence         = 1
              and (p.status & 4096) <> 4096

         insert into #ownedby
            select t.proc_name,
                   'threshold ('  +  convert (varchar (10), t.free_space)  +  ')',
                   NULL,
                   1,
                   s.name  +  ', '  +  convert (varchar (10), t.free_space)
            from syssegments s,
                 systhresholds t
            where t.suid    = @suid
              and s.segment = t.segment

         insert into #ownedby
            select convert (varchar (11), error), 'user message', NULL, 1, ' '
            from sysusermessages
            where uid = @uid

         insert into #ownedby
            select name, 'usertype', NULL, 10, ' '
            from systypes
            where uid      = @uid
              and usertype > 99

         if (db_id () = 1)
            begin
               insert into #ownedby
                  select name,
                  'database',
                  convert (char (8), crdate, 11)
                   +  ' '
                   +  convert (char (8), crdate, 8),
                  10,
                  ' '
                  from master.dbo.sysdatabases
                  where suid = @suid
            end

         if (exists (select *
                     from #ownedby))
            begin
               if (@DROP_FLAG IS NULL)
                  begin
                     select 'OBJECT'      = objname,
                            'OBJECT TYPE' = substring (objtype, 1, 18),
                            CREATED       = isnull (crdate, space (14) + 'N/A')
                     from #ownedby
                     order by 'OBJECT', 'OBJECT TYPE'

                     print ' '
                  end
               else
                  begin
                     delete from #ownedby
                     where objtype = 'database'

                     if (exists (select *
                                 from #ownedby
                                 where objtype = 'grant/revoke'))
                        begin
                           print '    USER %1! HAS GRANTED PERMISSIONS ON OBJECTS OWNED BY OTHER USERS.',
                                 @USER

                           print ' '

                           print '    THESE WILL HAVE TO BE ADDRESSED SEPARATELY.'

                           print ' '

                           select 'OBJECT' = objname
                           from #ownedby
                           where objtype = 'grant/revoke'
                           order by 'OBJECT'

                           print ' '

                           delete from #ownedby
                           where objtype = 'grant/revoke'
                        end

                     --
                     --  Drop triggers before dropping tables since triggers
                     --  may be defined on tables owned by somebody else.
                     --
                     update #ownedby
                     set class = 1
                     where class = 8

                     update #ownedby
                     set objtype = 'sp_dropthreshold '
                                   +  db_name ()
                                   +  ', '
                                   +  sort,
                         sort    = '1 '  +  objtype  +  ' '  +  objname
                     where objtype like 'threshold %'

                     update #ownedby
                     set objtype = 'drop '  +  objtype,
                         sort    = '3 '  +  objtype  +  ' '  +  objname
                     where substring (objtype, 1, 9) not in
                                   ('database ', 'sp_dropth', 'user mess',
                                    'usertype ')

                     update #ownedby
                     set objtype = stuff (objtype, charindex ('stored procedure', objtype), 16, 'proc')
                     where charindex ('stored procedure', objtype) > 0

                     update #ownedby
                     set objtype = stuff (objtype, charindex ('user table', objtype), 10, 'table')
                     where charindex ('user table', objtype) > 0

                     update #ownedby
                     set objtype = 'sp_dropmessage '  +  objname,
                         sort    = '4 '  +  objtype  +  ' '  +  objname
                     where objtype = 'user message'

                     update #ownedby
                     set objtype = 'sp_droptype    '  +  objname,
                         sort    = '5 '  +  objtype  +  ' '  +  objname
                     where objtype = 'usertype'

                     if (exists (select *
                                 from #ownedby
                                 where objtype              not like 'sp_dropmessage %'
                                   and valid_name (objname)        =  0))
                        begin
                           update #ownedby
                           set objtype = convert (char (22), objtype)
                                         +  isnull (@alias, @USER)
                                         +  '.'
                                         +  objname
                           where objtype              not like 'sp_dropmessage %'
                             and objtype              not like 'sp_dropthreshol%'
                             and objtype              not like 'sp_droptype %'
                             and valid_name (objname)       <> 0

                           update #ownedby
                           set objtype = convert (char (22), objtype)
                                         +  isnull (@alias, @USER)
                                         +  '."'
                                         +  objname
                                         +  '"'
                           where objtype              not like 'sp_dropmessage %'
                             and objtype              not like 'sp_dropthreshol%'
                             and objtype              not like 'sp_droptype %'
                             and valid_name (objname)        = 0

                           insert into #ownedby
                              values(' ', 'set quoted_identifier on', NULL, 1, ' ')
                        end
                     else
                        begin
                           update #ownedby
                           set objtype = convert (char (22), objtype)
                                         +  ' '
                                         +   isnull (@alias, @USER)
                                         +  '.'
                                         +   objname
                           where objtype not like 'sp_dropmessage %'
                             and objtype not like 'sp_dropthreshol%'
                             and objtype not like 'sp_droptype %'
                        end

                     if (exists (select *
                                 from #ownedby))
                        begin
                           insert into #ownedby
                              values(' ', 'use ' + db_name (), NULL, 0, ' ')

                           insert into #ownedby
                              select object_cinfo,
                                     'sp_unbindexeclass '
                                     +  object_cinfo
                                     +  ', pr, '
                                     +  isnull (@alias, @USER),
                                     NULL,
                                     1,
                                     '1 bound sp'  +  ' '  +  object_cinfo
                              from sysattributes
                              where class        = 6
                                and object_type  = 'PR'
                                and object_info1 = @uid

                           update #ownedby
                           set objtype = substring (objtype, 1, 15)
                                         +  ltrim (substring (objtype, 22, 64))
                           where objtype like 'drop %'

                           insert into #ownedby
                              select ' ', 'go', crdate, class,
                                     convert (char (64), sort) + 'zz'
                              from #ownedby

                           select 'DROP TEXT' = substring (objtype, 1, 77)
                           from #ownedby
                           where objtype <> 'database'
                           order by class, sort
                        end
                     else
                        begin
                           print '    USER <%1!> DOES NOT OWN ANY NON-DATABASE OBJECTS.',
                                 @USER

                           print ' '
                        end
                  end
            end
         else
            begin
               print '    THERE ARE NO OBJECTS OWNED BY <%1!>.', @USER

               print ' '
            end
      end
go
sp_addcomment sp_ownedby, 17, 'Displays all objects owned by USER'
go
sp_addcomment sp_ownedby, 17, '   If DROP_FLAG is non-null output will drop all USER.object'
go
sp_procxmode sp_ownedby, anymode
go

create procedure sp_refkeys (@TABLE  varchar (63) = NULL)  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @continue     tinyint,
           @maxreflines  tinyint,
           @rkcount      tinyint,
           @sequence     tinyint,
           @tableid      int

   create table #refkeys
      (tableid    int            not null,
       ftable     varchar (30)   null,
       fkey       varchar (255)  null,
       fkeysort   varchar (255)  null,
       reftabid   int            not null,
       rtable     varchar (30)   null,
       pkey       varchar (255)  null,
       seq        tinyint        not null)

   select @continue    = 0,
          @maxreflines = 4,
          @sequence    = 1

   if (@TABLE IS NULL)
      begin
         if (exists (select *
                     from sysreferences
                     where ((frgndbid <> db_id ())  or  (pmrydbid <> db_id ()))))
            begin
               print '    THERE ARE EXTERNAL REFERENTIAL KEYS (USE sp_refkeysx).'

               print ' '
            end

         if (exists (select *
                     from sysreferences
                     where ((frgndbid = db_id ())  and  (pmrydbid = db_id ()))))
            begin
               select @continue = 1
            end
         else
            begin
               print '    THERE ARE NO INTERNAL REFERENTIAL KEYS.'

               print ' '
            end
      end
   else
      begin
         select @tableid = id
         from sysobjects
         where id              = object_id (@TABLE)
           and (sysstat & 15) in (1, 3)

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

               print ' '
            end
         else
            begin
               if (exists (select *
                           from sysreferences
                           where ((tableid = @tableid)  or  (reftabid = @tableid))
                             and ((frgndbid <> db_id ())  or  (pmrydbid <> db_id ()))))
                  begin
                     print '    THERE ARE EXTERNAL REFERENTIAL KEYS FOR <%1!> (USE sp_refeysx).',
                           @TABLE

                     print ' '
                  end

               if (exists (select *
                           from sysreferences
                           where ((tableid = @tableid)  or  (reftabid = @tableid))
                             and frgndbid = db_id ()
                             and pmrydbid = db_id ()))
                  begin
                     select @continue = 1
                  end
               else
                  begin
                     print '    THERE ARE NO INTERNAL REFERENTIAL KEYS FOR <%1!>.',
                           @TABLE

                     print ' '
                  end
            end
      end

   if (@continue = 1)
      begin
         insert into #refkeys
            select tableid,
                   object_name (tableid),
                   col_name (tableid, fokey1)
                   +  ', '  +  col_name (tableid, fokey2)
                   +  ', '  +  col_name (tableid, fokey3)
                   +  ', '  +  col_name (tableid, fokey4)
                   +  ', '  +  col_name (tableid, fokey5)
                   +  ', '  +  col_name (tableid, fokey6)
                   +  ', '  +  col_name (tableid, fokey7)
                   +  ', '  +  col_name (tableid, fokey8)
                   +  ', '  +  col_name (tableid, fokey9)
                   +  ', '  +  col_name (tableid, fokey10)
                   +  ', '  +  col_name (tableid, fokey11)
                   +  ', '  +  col_name (tableid, fokey12)
                   +  ', '  +  col_name (tableid, fokey13)
                   +  ', '  +  col_name (tableid, fokey14)
                   +  ', '  +  col_name (tableid, fokey15)
                   +  ', '  +  col_name (tableid, fokey16),
                   object_name (constrid),
                   reftabid,
                   object_name (reftabid),
                   col_name (reftabid, refkey1)
                   +  ', '  +  col_name (reftabid, refkey2)
                   +  ', '  +  col_name (reftabid, refkey3)
                   +  ', '  +  col_name (reftabid, refkey4)
                   +  ', '  +  col_name (reftabid, refkey5)
                   +  ', '  +  col_name (reftabid, refkey6)
                   +  ', '  +  col_name (reftabid, refkey7)
                   +  ', '  +  col_name (reftabid, refkey8)
                   +  ', '  +  col_name (reftabid, refkey9)
                   +  ', '  +  col_name (reftabid, refkey10)
                   +  ', '  +  col_name (reftabid, refkey11)
                   +  ', '  +  col_name (reftabid, refkey12)
                   +  ', '  +  col_name (reftabid, refkey13)
                   +  ', '  +  col_name (reftabid, refkey14)
                   +  ', '  +  col_name (reftabid, refkey15)
                   +  ', '  +  col_name (reftabid, refkey16),
                   1
            from sysreferences
            where frgndbid = db_id ()
              and pmrydbid = db_id ()
              and (@TABLE IS NULL  or  @tableid = tableid  or  @tableid  = reftabid)

         if (1 < any (select count (tableid)
                      from #refkeys
                      group by ftable, fkey, rtable, pkey))
            begin
               print '    THERE ARE DUPLICATE FOREIGN KEY CONSTRAINTS.'

               print ' '
            end

         update #refkeys
         set fkeysort = substring (fkey  +  ' '  +  fkeysort, 1, 255)

         while (@sequence < 16)
            begin
               update #refkeys
               set fkey = rtrim (substring (fkey, 1, char_length (fkey) - 1))
               where right (fkey, 1) = ','

               update #refkeys
               set pkey = rtrim (substring (pkey, 1, char_length (pkey) - 1))
               where right (pkey, 1) = ','

               select @sequence = @sequence + 1
            end

         if (@TABLE IS NULL)
            begin
               select @rkcount = count (*)
               from #refkeys

               print '    THERE ARE %1! INTERNAL REFERENTIAL CONSTRAINTS.', @rkcount

               print ' '
            end

         -- ADD OWNER IF NOT OWNED BY DBO OR TABLE NAME IS NON-UNIQUE

         update #refkeys
         set ftable = substring (user_name (o.uid) + '.' + ftable, 1, 77)
         from sysobjects o
         where o.id = #refkeys.tableid
           and (o.uid <> 1  or
                1      < (select count (o2.uid)
                          from sysobjects o2
                          where o2.name           = o.name
                            and (o2.sysstat & 15) = 3))

         update #refkeys
         set rtable = substring (user_name (o.uid) + '.' + rtable, 1, 70)
         from sysobjects o
         where o.id = #refkeys.reftabid
           and (o.uid <> 1  or
                1      < (select count (o2.uid)
                          from sysobjects o2
                          where o2.name           = o.name
                            and (o2.sysstat & 15) = 3))

         -- SIMPLE TEXT MASSAGE WHICH SIMPLIFIES EARLIER CODE

         update #refkeys
         set fkey = ftable  +  ' ('  +  fkey  +  ')',
             pkey = rtable  +  ' ('  +  pkey  +  ')'

         select @sequence = 1

         while (@sequence < @maxreflines)
            begin
               -- SPLIT LONG TEXT (WITH COMMAS) ONTO A NEW LINE

               insert into #refkeys
                  select tableid,
                         ftable,
                         '    '
                         +  ltrim (substring (fkey,
                                              40 - charindex (',',
                                                              reverse (substring (fkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         fkeysort,
                         reftabid,
                         rtable,
                         '    '
                         +  ltrim (substring (pkey,
                                              40 - charindex (',',
                                                              reverse (substring (pkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) > 0
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) > 0

               insert into #refkeys
                  select tableid,
                         ftable,
                         '    '
                         +  ltrim (substring (fkey,
                                              40 - charindex (',',
                                                              reverse (substring (fkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         fkeysort,
                         reftabid,
                         rtable,
                         ' ',
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) > 0
                    and char_length (pkey)    < 39

               insert into #refkeys
                  select tableid,
                         ftable,
                         ' ',
                         fkeysort,
                         reftabid,
                         rtable,
                         '    '
                         +  ltrim (substring (pkey,
                                              40 - charindex (',',
                                                              reverse (substring (pkey,
                                                                                  1,
                                                                                  38))),
                                              255)),
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    < 39
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) > 0

               -- MASSAGE THE ORIGINAL SPLIT LINE AT COMMAS

               update #refkeys
               set fkey = substring (rk.fkey,
                                     1,
                                     39 - charindex (',',
                                                     reverse (substring (rk.fkey,
                                                                         1,
                                                                         38))))
               from #refkeys rk
               where rk.seq = @sequence
                 and exists (select rk2.*
                             from #refkeys rk2
                             where rk2.tableid   = rk.tableid
                               and rk2.reftabid  = rk.reftabid
                               and rk2.seq       = @sequence + 1
                               and rk2.fkey     <> ' ')

               update #refkeys
               set pkey = substring (rk.pkey,
                                     1,
                                     39 - charindex (',',
                                                     reverse (substring (rk.pkey,
                                                                         1,
                                                                         38))))
               from #refkeys rk
               where rk.seq = @sequence
                 and exists (select rk2.*
                             from #refkeys rk2
                             where rk2.tableid   = rk.tableid
                               and rk2.reftabid  = rk.reftabid
                               and rk2.seq       = @sequence + 1
                               and rk2.pkey     <> ' ')

               -- SPLIT OUT LONG LINES THAT HAVE NO COMMAS

               insert into #refkeys
                  select tableid,
                         ftable,
                         '    '  +  substring (fkey, charindex ('(', fkey), 38),
                         fkeysort,
                         reftabid,
                         rtable,
                         '    '  +  substring (pkey, charindex ('(', pkey), 38),
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) = 0
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) = 0

               insert into #refkeys
                  select tableid,
                         ftable,
                         '    '  +  substring (fkey, charindex ('(', fkey), 38),
                         fkeysort,
                         reftabid,
                         rtable,
                         ' ',
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    > 38
                    and charindex (',', fkey) = 0
                    and char_length (pkey)    < 39

               insert into #refkeys
                  select tableid,
                         ftable,
                         ' ',
                         fkeysort,
                         reftabid,
                         rtable,
                         '    '  +  substring (pkey, charindex ('(', pkey), 38),
                         @sequence + 1
                  from #refkeys
                  where seq                   = @sequence
                    and char_length (fkey)    < 39
                    and char_length (pkey)    > 38
                    and charindex (',', pkey) = 0

               select @sequence = @sequence + 1
            end

         -- MASSAGE THE LONG ORIGINAL LINES THAT HAD NO COMMAS

         update #refkeys
         set fkey = substring (fkey, 1, charindex ('(', fkey) - 1)
         where seq                   = 1
           and char_length (fkey)    > 38
           and charindex (',', fkey) = 0

         update #refkeys
         set pkey = substring (pkey, 1, charindex ('(', pkey) - 1)
         where seq                   = 1
           and char_length (pkey)    > 38
           and charindex (',', pkey) = 0

         select 'FOREIGN KEY' = substring (fkey, 1, 38),
                'PRIMARY KEY' = substring (pkey, 1, 38)
         from #refkeys
         order by ftable, fkeysort, rtable, seq
      end
go
sp_addcomment sp_refkeys, 17, 'Lists internal referential keys for TABLE or (default) all tables'
go
sp_procxmode sp_refkeys, anymode
go

create procedure sp_refkeysx (@TABLE varchar (63) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @continue  tinyint,
           @tableid   int,
           @sequence  tinyint

   create table #refkeysx
      (fdbid  smallint      not null,
       ftabid int           not null,
       fkey   varchar (62)  not null,
       pdbid  smallint      not null,
       ptabid int           not null,
       pkey   varchar (62)  not null)

   select @continue = 0,
          @sequence = 1

   if (@TABLE IS NULL)
      begin
         if (exists (select *
                     from sysreferences
                     where ((frgndbid <> db_id ())  or  (pmrydbid <> db_id ()))))
            begin
               select @continue = 1
            end
         else
            begin
               print '    THERE ARE NO EXTERNAL REFERENTIAL KEYS.'

               print ' '
            end
      end
   else
      begin
         select @tableid = id
         from sysobjects
         where id              = object_id (@TABLE)
           and (sysstat & 15) in (1, 3)

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

               print ' '
            end
         else
            begin
               if (exists (select *
                           from sysreferences
                           where ((tableid = @tableid)  or  (reftabid = @tableid))
                             and ((frgndbid <> db_id ())  or  (pmrydbid <> db_id ()))))
                  begin
                     select @continue = 1
                  end
               else
                  begin
                     print '    THERE ARE NO EXTERNAL REFERENTIAL KEYS FOR <%1!>.',
                           @TABLE

                     print ' '
                  end
            end
      end

   if (@continue = 1)
      begin
         insert into #refkeysx
            select frgndbid,
                   tableid,
                   substring (col_name (reftabid, fokey1, frgndbid)
                              + ', ' + col_name (reftabid, fokey2, frgndbid)
                              + ', ' + col_name (reftabid, fokey3, frgndbid)
                              + ', ' + col_name (reftabid, fokey4, frgndbid)
                              + ', ' + col_name (reftabid, fokey5, frgndbid)
                              + ', ' + col_name (reftabid, fokey6, frgndbid)
                              + ', ' + col_name (reftabid, fokey7, frgndbid)
                              + ', ' + col_name (reftabid, fokey8, frgndbid)
                              + ', ' + col_name (reftabid, fokey9, frgndbid)
                              + ', ' + col_name (reftabid, fokey10, frgndbid)
                              + ', ' + col_name (reftabid, fokey11, frgndbid)
                              + ', ' + col_name (reftabid, fokey12, frgndbid)
                              + ', ' + col_name (reftabid, fokey13, frgndbid)
                              + ', ' + col_name (reftabid, fokey14, frgndbid)
                              + ', ' + col_name (reftabid, fokey15, frgndbid)
                              + ', ' + col_name (reftabid, fokey16, frgndbid),
                              1,
                              62),
                   pmrydbid,
                   reftabid,
                   substring (col_name (tableid, refkey1, pmrydbid)
                              + ', ' + col_name (tableid, refkey2, pmrydbid)
                              + ', ' + col_name (tableid, refkey3, pmrydbid)
                              + ', ' + col_name (tableid, refkey4, pmrydbid)
                              + ', ' + col_name (tableid, refkey5, pmrydbid)
                              + ', ' + col_name (tableid, refkey6, pmrydbid)
                              + ', ' + col_name (tableid, refkey7, pmrydbid)
                              + ', ' + col_name (tableid, refkey8, pmrydbid)
                              + ', ' + col_name (tableid, refkey9, pmrydbid)
                              + ', ' + col_name (tableid, refkey10, pmrydbid)
                              + ', ' + col_name (tableid, refkey11, pmrydbid)
                              + ', ' + col_name (tableid, refkey12, pmrydbid)
                              + ', ' + col_name (tableid, refkey13, pmrydbid)
                              + ', ' + col_name (tableid, refkey14, pmrydbid)
                              + ', ' + col_name (tableid, refkey15, pmrydbid)
                              + ', ' + col_name (tableid, refkey16, pmrydbid),
                              1,
                              62)
            from sysreferences
            where frgndbid <> db_id ()
               or pmrydbid <> db_id ()

         if (@TABLE IS NOT NULL)
            begin
               delete from #refkeysx
               where ftabid <> @tableid
                 and ptabid <> @tableid
            end

         while (@sequence < 16)
            begin
               update #refkeysx
               set fkey = rtrim (substring (fkey, 1, char_length (fkey) - 1))
               where right (fkey, 1) = ','

               update #refkeysx
               set pkey = rtrim (substring (pkey, 1, char_length (pkey) - 1))
               where right (pkey, 1) = ','

               select @sequence = @sequence + 1
            end

         update #refkeysx
         set fkey = substring (db_name (fdbid)
                               +  '/'
                               +  object_name (ftabid, fdbid)
                               +  ' ('
                               +  fkey,
                               1,
                               62)
         where fdbid <> db_id ()

         update #refkeysx
         set fkey = substring (db_name (r.fdbid)
                               +  '..'
                               +  object_name (r.ftabid)
                               +  ' ('
                               +  r.fkey,
                               1,
                               62)
         from #refkeysx r,
              sysobjects o
         where r.fdbid  = db_id ()
           and r.ftabid = o.id
           and o.uid    = 1
           and 1        = (select count (o2.uid)
                           from sysobjects o2
                           where o2.id              = r.ftabid
                             and (o2.sysstat & 15) in (1, 2, 3))

         update #refkeysx
         set fkey = substring (db_name (r.fdbid)
                               +  '.'
                               +  user_name (o.uid)
                               +  '.'
                               +  object_name (r.ftabid)
                               +  ' ('
                               +  r.fkey,
                               1,
                               62)
         from #refkeysx r,
              sysobjects o
         where r.fdbid   = db_id ()
           and r.ftabid  = o.id
           and (o.uid   <> 1  or
                1        < (select count (o2.uid)
                            from sysobjects o2
                            where o2.id              = r.ftabid
                              and (o2.sysstat & 15) in (1, 2, 3)))

         update #refkeysx
         set pkey = substring (db_name (pdbid)
                               +  '/'
                               +  object_name (ptabid, pdbid)
                               +  ' ('
                               +  pkey,
                               1,
                               62)
         where pdbid <> db_id ()

         update #refkeysx
         set pkey = substring (db_name (r.pdbid)
                               +  '..'
                               +  object_name (r.ptabid)
                               +  ' ('
                               +  r.pkey,
                               1,
                               62)
         from #refkeysx r,
              sysobjects o
         where r.pdbid  = db_id ()
           and r.ptabid = o.id
           and o.uid    = 1
           and 1        = (select count (o2.uid)
                           from sysobjects o2
                           where o2.id              = r.ptabid
                             and (o2.sysstat & 15) in (1, 2, 3))

         update #refkeysx
         set pkey = substring (db_name (r.pdbid)
                               +  '.'
                               +  user_name (o.uid)
                               +  '.'
                               +  object_name (r.ptabid)
                               +  ' ('
                               +  r.pkey,
                               1,
                               62)
         from #refkeysx r,
              sysobjects o
         where r.pdbid   = db_id ()
           and r.ptabid  = o.id
           and (o.uid   <> 1  or
                1        < (select count (o2.uid)
                            from sysobjects o2
                            where o2.id              = r.ptabid
                              and (o2.sysstat & 15) in (1, 2, 3)))

         select 'FOREIGN KEY' = substring (fkey, 1, 38),
                'PRIMARY KEY' = substring (pkey, 1, 38)
         from #refkeysx
         order by fkey, pkey
      end
go
sp_addcomment sp_refkeysx, 17, 'Lists external referential keys for TABLE or (default) all tables.'
go
sp_procxmode sp_refkeysx, anymode
go

create procedure sp_ruleuse (@RULE varchar (63) = NULL) as
   set ansinull                      on
   set close on endtran              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_ruleuse> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @errorchar   varchar ( 4),
           @createdate  char    ( 8),
           @dbname      varchar (30),
           @maxrulelen  tinyint,
           @owner       varchar (30),
           @printmssg   varchar (70),
           @printrtxt   varchar (63),
           @rulename    varchar (63),
           @uidcount    tinyint

   if (@RULE IS NULL)
      begin
         set nocount on

         select @maxrulelen = 4  +  max (datalength (o.name  +  user_name (o.uid)))
         from sysobjects o,
              sysprocedures p
         where (o.sysstat & 15)   = 7
           and o.id               = p.id
           and p.sequence         = 1
           and (p.status & 4096) <> 4096

         if (@maxrulelen IS NULL)
            begin
               select @dbname = db_name ()

               print '    THERE ARE NO RULES IN DATABASE %1!.', @dbname

               print ' '
            end
         else
            begin
               declare rule_cursor cursor for
                  select o.name, user_name (o.uid), convert (char (8), o.crdate, 11)
                  from sysobjects o,
                       sysprocedures p
                  where (o.sysstat & 15)   = 7
                    and o.id               = p.id
                    and p.sequence         = 1
                    and (p.status & 4096) <> 4096
                  order by o.name, user_name (o.uid)
               for read only

               open rule_cursor

               fetch rule_cursor into @rulename, @owner, @createdate

               while (@@sqlstatus = 0)
                  begin
                     if (@owner = 'dbo')
                        begin
                           select @uidcount = count (uid)
                           from sysobjects
                           where name           = @rulename
                             and (sysstat & 15) = 7

                           if (@uidcount = 1)
                              begin
                                 select @printrtxt = @rulename
                              end
                           else
                              begin
                                 select @rulename  = @owner  +  '.'  +  @rulename,
                                        @printrtxt = @rulename  +  ' ('  +  @owner  +  ')'
                              end
                        end
                     else
                        begin
                           select @rulename  = @owner  +  '.'  +  @rulename,
                                  @printrtxt = @rulename  +  ' ('  +  @owner  +  ')'
                        end

                     select @printmssg = @printrtxt
                                         +  space (@maxrulelen - datalength (@printrtxt))
                                         +  '('  +  @createdate  +  ')'

                     print ' '

                     print ' '

                     print '    %1!', @printmssg

                     execute sp_ruleuse @rulename

                     fetch rule_cursor into @rulename, @owner, @createdate
                  end

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

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

                     return
                  end

               close rule_cursor

               deallocate cursor rule_cursor
            end
      end
   else
      begin
         if (exists (select o.*
                     from sysobjects o,
                          sysprocedures p
                     where o.id               = object_id (@RULE)
                       and (o.sysstat & 15)   = 7
                       and o.id               = p.id
                       and p.sequence         = 1
                       and (p.status & 4096) <> 4096))
            begin
               if (exists (select *
                           from syscolumns
                           where domain = object_id (@RULE)))
                  begin
                        select 'TABLE.COLUMN' = space (77)
                        where 1 = 2
                     union
                        select substring (object_name (c.id)
                                          +  '.'
                                          +   col_name (c.id, c.colid),
                                          1,
                                          77)
                        from syscolumns c,
                             sysobjects o,               -- for rule
                             sysobjects oc               -- for table/view
                        where c.domain = object_id (@RULE)
                          and o.id     = object_id (@RULE)
                          and oc.id    = c.id
                          and o.uid    = oc.uid
                     union
                        select substring (object_name (c.id)
                                          +  '.'
                                          +  col_name (c.id, c.colid)
                                          +  ' ('
                                          +  user_name (oc.uid)
                                          +  ')',
                                          1,
                                          77)
                        from syscolumns c,
                             sysobjects o,               -- for rule
                             sysobjects oc               -- for table/view
                        where c.domain  = object_id (@RULE)
                          and o.id      = object_id (@RULE)
                          and oc.id     = c.id
                          and o.uid    <> oc.uid
                     order by 'TABLE.COLUMN'
                  end
               else
                  begin
                     print '    RULE <%1!> IS NOT USED.', @RULE

                     print ' '
                  end
            end
         else
            begin
               print '    RULE <%1!> DOES NOT EXIST.', @RULE

               print ' '
            end
      end
go
sp_addcomment sp_ruleuse, 17, 'Lists all columns using RULE or (default) any rule'
go
sp_procxmode sp_ruleuse, anymode
go

create procedure sp_typeagg as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   create table #typeagg
      (name      varchar (30)  not null,
       c         smallint      not null,
       nul       tinyint       not null,
       size1     tinyint       not null,
       size2     tinyint       not null,
       comment   varchar (41)  null)

   insert into #typeagg
      select distinct t.name,
                      0,
                      t.allownulls,
                      0,
                      0,
                      'Unused usertype'
      from systypes t
      where t.usertype > 99
        and not exists (select c.id
                        from syscolumns c
                        where c.usertype = t.usertype)

   insert into #typeagg
      select distinct t.name,
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      0,
                      0,
                      NULL
      from syscolumns c,
           sysobjects o,
           systypes t
      where t.usertype       > 99
        and t.usertype       = c.usertype
        and c.id             = o.id
        and (o.sysstat & 15) = 3
      group by c.usertype, (c.status & 8)
      having t.usertype       > 99
         and t.usertype       = c.usertype
         and c.id             = o.id
         and (o.sysstat & 15) = 3

   insert into #typeagg
      select distinct t.name,
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      0,
                      0,
                      NULL
      from syscolumns c,
           sysobjects o,
           systypes t
      where t.usertype       in (11, 12, 16, 18, 19, 20, 21, 22, 80)
        and t.usertype        = c.usertype
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by t.usertype, (c.status & 8)
      having t.usertype       in (11, 12, 16, 18, 19, 20, 21, 22, 80)
         and t.usertype        = c.usertype
         and c.id              = o.id
         and (o.sysstat & 15)  = 3

   insert into #typeagg
      select distinct substring (t.name  +  ' ('
                                 +  convert (varchar (3), c.length)  + ')',
                                 1,
                                 20),
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      c.length,
                      0,
                      NULL
      from syscolumns c,
           sysobjects o,
           systypes t
      where c.usertype       in (1, 2, 3, 4)
        and c.usertype        = t.usertype
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by c.usertype, c.length, (c.status & 8)
      having c.usertype       in (1, 2, 3, 4)
         and c.usertype        = t.usertype
         and c.id              = o.id
         and (o.sysstat & 15)  = 3

   insert into #typeagg
      select distinct substring (t.name  +  ' ('
                                 +  convert (varchar (3), c.length / @@ncharsize)  + ')',
                                 1,
                                 20),
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      c.length,
                      0,
                      NULL
      from syscolumns c,
           sysobjects o,
           systypes t
      where c.usertype       in (24, 25)
        and c.usertype        = t.usertype
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by c.usertype, c.length, (c.status & 8)
      having c.usertype       in (24, 25)
         and c.usertype        = t.usertype
         and c.id              = o.id
         and (o.sysstat & 15)  = 3

   insert into #typeagg
      select distinct substring ('decimal ('
                                 +  convert (varchar (3), c.prec) + ', '
                                 +  convert (varchar (3), c.scale) + ')',
                                 1,
                                 20),
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      c.prec,
                      c.scale,
                      NULL
      from syscolumns c,
           sysobjects o
      where c.usertype       in (26, 27)
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by c.prec, c.scale, (c.status & 8)
      having c.usertype       in (26, 27)
         and c.id              = o.id
         and (o.sysstat & 15)  = 3

   insert into #typeagg
      select distinct substring ('numeric ('
                                 +  convert (varchar (3), c.prec) + ', '
                                 +  convert (varchar (3), c.scale) + ')',
                                 1,
                                 20),
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      c.prec,
                      c.scale,
                      NULL
      from syscolumns c,
           sysobjects o
      where c.usertype       in (10, 28)
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by c.prec, c.scale, (c.status & 8)
      having c.usertype       in (10, 28)
         and c.id              = o.id
         and (o.sysstat & 15)  = 3

   insert into #typeagg
      select distinct substring ('float ('
                                 +  convert (varchar (3), c.prec)  + ')',
                                 1,
                                 20),
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      c.prec,
                      0,
                      NULL
      from syscolumns c,
           sysobjects o
      where c.usertype       in (8, 23)
        and c.prec           IS NOT NULL
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by c.prec, (c.status & 8)
      having c.usertype       in (8, 23)
         and c.prec           IS NOT NULL
         and c.id              = o.id
         and (o.sysstat & 15)  = 3

   insert into #typeagg
      select distinct 'real',
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      4,
                      0,
                      NULL
      from syscolumns c,
           sysobjects o
      where ((c.usertype = 23)  or  ((c.usertype = 8) and (c.length = 4)))
        and c.prec           IS NULL
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by (c.status & 8)
      having ((c.usertype = 23)  or  ((c.usertype = 8) and (c.length = 4)))
         and c.prec           IS NULL
         and c.id              = o.id
         and (o.sysstat & 15)  = 3

   insert into #typeagg
      select distinct 'double precision',
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      8,
                      0,
                      NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        = 8
        and c.length          = 8
        and c.prec           IS NULL
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by (c.status & 8)
      having c.usertype        = 8
         and c.length          = 8
         and c.prec           IS NULL
         and c.id              = o.id
         and (o.sysstat & 15)  = 3

   insert into #typeagg
      select distinct 'tinyint',
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      0,
                      0,
                      NULL
      from syscolumns c,
           sysobjects o
      where c.usertype       in (5, 13)
        and c.length          = 1
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by (c.status & 8)
      having c.usertype       in (5, 13)
         and c.length          = 1
         and c.id              = o.id
         and (o.sysstat & 15)  = 3

   insert into #typeagg
      select distinct 'smallint',
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      0,
                      0,
                      NULL
      from syscolumns c,
           sysobjects o
      where c.usertype       in (6, 13)
        and c.length          = 2
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by (c.status & 8)
      having c.usertype       in (6, 13)
         and c.length          = 2
         and c.id              = o.id
         and (o.sysstat & 15)  = 3

   insert into #typeagg
      select distinct 'int',
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      0,
                      0,
                      NULL
      from syscolumns c,
           sysobjects o
      where c.usertype       in (7, 13)
        and c.length          = 4
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by (c.status & 8)
      having c.usertype       in (7, 13)
         and c.length          = 4
         and c.id              = o.id
         and (o.sysstat & 15)  = 3

   update #typeagg
   set comment = '"varchar ('
                 +  convert (varchar (3), size1)
                 +  ')  null" may be better'
   where name  like 'char%'
     and size1    > 6
     and nul      = 1

   update #typeagg
   set comment = '"varchar ('
                 +  convert (varchar (3), size1)
                 +  ')  not null" may be better'
   where name  like 'char%'
     and size1    > 15
     and nul      = 0

   update #typeagg
   set comment = '"fixed char ('
                 +  convert (varchar (3), size1)
                 +  ')  not null" may be better'
   where name  like 'varchar%'
     and size1    < 12
     and nul      = 0

   if (exists (select *
               from #typeagg
               where comment IS NOT NULL))
      begin
         select TYPE    = substring (name  +  ' '  +  char (32 + (10 * nul)), 1, 27),
                COLUMNS = substring (convert (char (10), convert (money, c)), 1, 7),
                COMMENT = isnull (comment, ' ')
         from #typeagg
         order by substring (name, 1, 6), size1, size2, nul
      end
   else
      begin
         select TYPE    = name  +  ' '  +  char (32 + (10 * nul)),
                COLUMNS = substring (convert (char (10), convert (money, c)), 1, 7)
         from #typeagg
         order by substring (name, 1, 6), size1, size2, nul
      end
go
sp_addcomment sp_typeagg, 17, 'Displays column type aggregates for user tables'
go
sp_procxmode sp_typeagg, anymode
go

create procedure sp_uses (@OBJECT varchar (63)) as

   -- The mode updates are 'incorrect'.  Sybase does not update
   -- sysdepends.readobj correctly when a stored procedure or trigger (only)
   -- reads an object.  In this case I am using two wrongs to make a right.

   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @maxmodetxt  tinyint,
           @maxusestxt  tinyint,
           @objectid    int,
           @objname     varchar (30),
           @sysstat     tinyint

   create table #uses
     (name       char (62)                  not null,
      uses       varchar (61)               not null,
      uid        smallint                   null,
      readflag   tinyint       default 0    not null,
      writeflag  tinyint       default 0    not null,
      mode       varchar (10)  default '-'  not null,
      sysstat    smallint                   null,
      type       char (16)     default '?'  not null)

   select @objectid = id,
          @sysstat  = (sysstat & 15)
   from sysobjects
   where id              = object_id (@OBJECT)
     and (sysstat & 15) in (2, 4, 8)

   if (@objectid IS NULL)
      begin
         print '    <%1!> IS NOT A PROC/TRIGGER/VIEW OR DOES NOT EXIST.', @OBJECT

         print ' '
      end
   else
      begin
         if (@sysstat = 2)
            begin
               insert into #uses (name, uses, uid, sysstat)
                  select @OBJECT,
                         isnull (o.name, 'Unknown Base Object ' + convert (char (10), d.depid)),
                         o.uid,
                         (o.sysstat & 15)
                  from sysdepends d,
                       sysobjects o
                  where d.id     = @objectid
                    and d.depid *= o.id

               update #uses
               set name = substring (u.name  +  ' ('  +  user_name (u.uid)  +  ')', 1, 62)
               from #uses u
               where u.uid <> 1
                  or 1      < (select count (o.uid)
                               from sysobjects o
                               where o.name           = u.name
                                 and (o.sysstat & 15) = u.sysstat)

               update #uses
               set type = substring (vO.name, 1, 16)
               from sybsystemprocs.dbo.usptvalues vO
               where vO.type   = 'O'
                 and vO.number = convert (int, sysstat & 15)

               if (exists (select *
                           from #uses))
                  begin
                     select @maxusestxt = max (char_length (uses))
                     from #uses

                     if (@maxusestxt > 30)
                        begin
                           select 'VIEW' = substring (name, 1, 20),
                                  USES   = substring (uses, 1, 43),
                                  TYPE   = substring (type, 1, 12)
                           from #uses
                           order by uses, type
                        end
                     else if (char_length (@OBJECT) > 30)
                        begin
                           select 'VIEW' = substring (name, 1, 33),
                                  USES   = substring (uses, 1, 30),
                                  TYPE   = substring (type, 1, 12)
                           from #uses
                           order by uses, type
                        end
                     else
                        begin
                           select 'VIEW' = substring (name, 1, 30),
                                  USES   = substring (uses, 1, 30),
                                  TYPE   = substring (type, 1, 12)
                           from #uses
                           order by uses, type
                        end
                  end
               else
                  begin
                     print '    THERE ARE NO TABLES/VIEWS USED BY <%1!>.', @OBJECT

                     print ' '
                  end
            end
         else
            begin
               insert into #uses (name, uses, uid, readflag, writeflag, sysstat)
                  select @OBJECT,
                         isnull (o.name, 'Unknown Base Object '  +  convert (char (10), d.depid)),
                         o.uid,
                         d.readobj,
                         d.resultobj,
                         (o.sysstat & 15)
                  from sysdepends d,
                       sysobjects o
                  where d.id     = @objectid
                    and d.depid *= o.id

               update #uses
               set type = substring (vO.name, 1, 16)
               from sybsystemprocs.dbo.usptvalues vO
               where vO.type   = 'O'
                 and vO.number = convert (int, sysstat & 15)

               update #uses
               set mode = 'read'
               where sysstat   <> 2
                 and writeflag  = 0

               update #uses
               set mode = 'write'
               where sysstat   <> 2
                 and readflag   = 0
                 and writeflag  = 1

               update #uses
               set mode = 'read/write'
               where sysstat   <> 2
                 and readflag   = 1
                 and writeflag  = 1

               update #uses
               set mode = 'execute'
               where sysstat = 4

               update #uses
               set uses = substring (d.uses  +  ' ('  +  user_name (d.uid)  +  ')',
                          1,
                          61)
               from #uses d
               where d.uid <> 1
                  or 1      < (select count (o.id)
                               from sysobjects o
                               where o.name           = d.uses
                                 and (o.sysstat & 15) = d.sysstat)

               if (exists (select *
                           from #uses
                           where type     = 'stored procedure'
                             and @sysstat = 4))
                  begin
                     print '    SP_TREE WILL DISPLAY A CALL TREE FOR <%1!>.',
                            @OBJECT

                     print ' '
                  end

               if (exists (select *
                           from #uses))
                  begin
                     select @maxmodetxt = max (char_length (mode)),
                            @maxusestxt = max (char_length (uses))
                     from #uses

                     if (@maxusestxt < 31)
                        begin
                           select OBJECT = substring (name, 1, 18),
                                  USES   = substring (uses, 1, 30),
                                  TYPE   = type,
                                  MODE   = mode
                           from #uses
                           order by uses, type
                        end
                     else if (@maxmodetxt < 6)
                        begin
                           select OBJECT = substring (name, 1, 18),
                                  USES   = substring (uses, 1, 41),
                                  TYPE   = substring (type, 1, 10),
                                  MODE   = substring (mode, 1, 5)
                           from #uses
                           order by uses, type
                        end
                     else if (@maxmodetxt < 8)
                        begin
                           select OBJECT = substring (name, 1, 18),
                                  USES   = substring (uses, 1, 33),
                                  TYPE   = type,
                                  MODE   = substring (mode, 1, 7)
                           from #uses
                           order by uses, type
                        end
                     else
                        begin
                           select OBJECT = substring (name, 1, 18),
                                  USES   = substring (uses, 1, 30),
                                  TYPE   = type,
                                  MODE   = mode
                           from #uses
                           order by uses, type
                        end
                  end
               else
                  begin
                     print '    THERE ARE NO PROCS/TABLES/VIEWS USED BY <%1!> IN THE CURRENT DATABASE.',
                           @OBJECT

                     print ' '
                  end
            end
      end
go
sp_addcomment sp_uses, 17, 'Lists procs/tables/views used by PROC/TRIGGER/VIEW'
go
sp_procxmode sp_uses, anymode
go

dump transaction sybsystemprocs with truncate_only
go
