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_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_maxdepend
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 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 @LF          char (1),
           @chkcount    varchar (7),
           @maxnamelen  tinyint,
           @space1      char (1),
           @space3      char (3),
           @tableid     int,
           @tcstring    char (18)

   select @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 (@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 > 31)
                  begin
                     insert into #checks
                        select id, uid, colid, colname, prntname, 2,
                               @space3  +  ltrim (substring (chktext, 45, 255)),
                               sorttext
                        from #checks
                        where seq                   = 1
                          and char_length (chktext) > 44

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

                     update #checks
                     set chktext = substring (chktext, 1, 44)
                  end
               else if (@maxnamelen > 25)
                  begin
                     insert into #checks
                        select id, uid, colid, colname, prntname, 2,
                               @space3  +  ltrim (substring (chktext, 51, 255)),
                               sorttext
                        from #checks
                        where seq                   = 1
                          and char_length (chktext) > 50

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

                     update #checks
                     set chktext = substring (chktext, 1, 50)
                  end
               else
                  begin
                     insert into #checks
                        select id, uid, colid, colname, prntname, 2,
                               @space3  +  ltrim (substring (chktext, 57, 255)),
                               sorttext
                        from #checks
                        where seq                   = 1
                          and char_length (chktext) > 56

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

                     update #checks
                     set chktext = substring (chktext, 1, 56)
                  end

               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
                     set nocount on

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

                     print '    THERE ARE %1! CHECK CONSTRAINTS.', @chkcount

                     print ' '
                  end

               if (@maxnamelen > 31)
                  begin
                     select 'TABLE' = substring (prntname, 1, 32),
                            'CHECK' = substring (chktext, 1, 44)
                     from #checks
                     order by sorttext
                  end
               else if (@maxnamelen > 25)
                  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
                     insert into #checks
                        select id, uid, colid, ' ', prntname, 2,
                               @space3  +  ltrim (substring (chktext, 53, 255)),
                               chktext
                        from #checks
                        where seq                   = 1
                          and char_length (chktext) > 52

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

                     if (exists (select *
                                 from #checks
                                 where seq > 1))
                        begin
                           set nocount on

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

                           print '    THERE ARE %1! CHECK CONSTRAINTS.', @chkcount

                           print ' '
                        end

                     select 'COLUMN' = substring (colname, 1, 24),
                            'CHECK'  = substring (chktext, 1, 52)
                     from #checks
                     order by colid, sorttext, seq
                  end
               else if (@maxnamelen > 15)
                  begin
                     insert into #checks
                        select id, uid, colid, ' ', prntname, 2,
                               @space3  +  ltrim (substring (chktext, 57, 255)),
                               chktext
                        from #checks
                        where seq                   = 1
                          and char_length (chktext) > 56

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

                     if (exists (select *
                                 from #checks
                                 where seq > 1))
                        begin
                           set nocount on

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

                           print '    THERE ARE %1! CHECK CONSTRAINTS.', @chkcount

                           print ' '
                        end

                     select 'COLUMN' = substring (colname, 1, 20),
                            'CHECK'  = substring (chktext, 1, 56)
                     from #checks
                     order by colid, sorttext, seq
                  end
               else
                  begin
                     insert into #checks
                        select id, uid, colid, ' ', prntname, 2,
                               @space3  +  ltrim (substring (chktext, 63, 255)),
                               chktext
                        from #checks
                        where seq                   = 1
                          and char_length (chktext) > 62

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

                     if (exists (select *
                                 from #checks
                                 where seq > 1))
                        begin
                           set nocount on

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

                           print '    THERE ARE %1! CHECK CONSTRAINTS.', @chkcount

                           print ' '
                        end

                     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

         print @space1
      end
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 = 'double precision'
         where utype in (8, 14)
           and prec  IS NULL

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

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

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

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

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

         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 = 'double precision'
         where utype in (8, 14)
           and prec  IS NULL

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

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

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

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

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

         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 = 'double precision'
                     where utype in (8, 14)
                       and prec  IS NULL

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

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

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

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

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

                     print '                                %1!', @titlestring

                     select 'COLUMN'       = 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.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
        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 (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 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_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
         exec 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
         exec 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

         exec 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
         exec 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
         exec 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
         exec 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
         exec 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
         exec 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

         set nocount off

         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

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,
       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
               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 dfltname, 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 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
         exec sp_depend1 @OBJECT1
      end
   else if (@OBJECT3 IS NULL)
      begin
         exec sp_depend2 @OBJECT1, @OBJECT2
      end
   else if (@OBJECT2 IS NULL)                        -- Weird!  But ...
      begin
         exec sp_depend2 @OBJECT1, @OBJECT3
      end
   else
      begin
         exec 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

create procedure sp_maxdepend (@MAX tinyint = 3)  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_maxdepend> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @tvcnt      int,
           @tvcntchar  varchar (7)

   create table #maxdependx
      (oid       int           not null,
       ouid      smallint      not null,
       oname     varchar (40)  not null,
       osysstat  smallint      not null,
       otype     char (16)     not null,
       did       int           not null,
       duid      smallint      not null,
       dname     varchar (40)  not null,
       dsysstat  smallint      not null,
       dtype     char (16)     not null)

   print ' '

   insert into #maxdependx
      select oo.id, oo.uid, oo.name, (oo.sysstat & 15), vOo.name,
             d.id, od.uid, od.name, (od.sysstat & 15), vOd.name
      from sysdepends d,
           sysobjects od,
           sysobjects oo,
           sybsystemprocs.dbo.usptvalues vOd,
           sybsystemprocs.dbo.usptvalues vOo
      where (oo.sysstat & 15) in (2, 3)
        and vOd.type           = 'O'
        and vOd.number         = convert (int, od.sysstat & 15)
        and vOo.type           = 'O'
        and vOo.number         = convert (int, oo.sysstat & 15)
        and od.id              = d.id
        and oo.id              = d.depid
        and @MAX               > (select count (d2.id)
                                  from sysdepends d2
                                  where d2.depid = oo.id)

   insert into #maxdependx
      select o.id, o.uid, o.name, (o.sysstat & 15), vO.name,
             0, 1, '-', 0, '-'
      from sysobjects o,
           sybsystemprocs.dbo.usptvalues vO
      where (o.sysstat & 15) in (2, 3)
        and vO.type           = 'O'
        and vO.number         = convert (int, o.sysstat & 15)
        and (not exists (select d.*
                         from sysdepends d
                         where o.id = d.depid))

   update #maxdependx
   set oname = substring (oname + ' (' + user_name (ouid) + ')', 1, 40)
   from #maxdependx md
   where md.ouid <> 1
      or 1        < (select count (o.uid)
                     from sysobjects o
                     where o.name           = md.oname
                       and (o.sysstat & 15) = md.osysstat)

   update #maxdependx
   set dname = substring (dname + ' (' + user_name (duid) + ')', 1, 40)
   from #maxdependx md
   where md.duid <> 1
      or 1        < (select count (o.uid)
                     from sysobjects o
                     where o.name           = md.dname
                       and (o.sysstat & 15) = md.dsysstat)

   update #maxdependx
   set dtype = 'SP'
   where dtype = 'stored procedure'

   update #maxdependx
   set dtype = 'table'
   where dtype = 'user table'

   update #maxdependx
   set otype = 'table'
   where otype = 'user table'

   select @tvcnt = count (distinct oid)
   from #maxdependx

   if (@tvcnt > 0)
      begin
         select @tvcntchar = ltrim (substring (convert (char (10), convert (money, @tvcnt), 1), 1, 7))

         print '    THERE ARE %1! USER TABLES/VIEWS WITH LESS THAN %2! DEPENDENTS.',
               @tvcntchar, @MAX

         print ' '

         select 'O TYPE'  = substring (otype, 1, 5),
                OBJECT    = substring (oname, 1, 31),
                DEPENDENT = substring (dname, 1, 31),
                'D TYPE'  = substring (dtype, 1, 5)
         from #maxdependx m
         order by oname, otype, dname
      end
   else
      begin
         print '    ALL USER TABLES AND VIEWS HAVE AT LEAST %1! DEPENDENTS.',
               @MAX

         print ' '
      end
go
sp_addcomment sp_maxdepend, 17, 'Lists user tables/views with less than MAX dependents'
go
sp_procxmode sp_maxdepend, anymode
go

create procedure sp_ownedby (@USER varchar (30)) 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_ownedby> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @uid  smallint

   select @uid = uid
   from sysusers
   where name = @USER
     and uid  > 0
     and uid  < 16384

   if (@uid IS NULL)
      begin
         print '    USER <%1!> IS A GROUP OR DOES NOT EXIST.', @USER

         print ' '
      end
   else if (exists (select *
                    from sysobjects
                    where uid = @uid))
        or (exists (select *
                    from systypes
                    where uid = @uid))
      begin
            select 'OBJECT'      = space (30),
                   'OBJECT TYPE' = space (16),
                   CREATED       = space (17)
            where 1 = 2
         union
            select o.name,
                   substring (vO.name, 1, 16),
                   convert (char (8), o.crdate, 11) + ' '  + convert (char (8), o.crdate, 8)
            from sysobjects o,
                 sybsystemprocs.dbo.usptvalues vO
            where o.uid      = @uid
              and vO.number  = convert (int, o.sysstat & 15)
              and vO.number in (1, 2, 3, 4, 8)
              and vO.type    = 'O'
         union
            select o.name,
                   'default',
                   convert (char (8), o.crdate, 11) + ' '  + convert (char (8), o.crdate, 8)
            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
         union
            select o.name,
                   'rule',
                   convert (char (8), o.crdate, 11) + ' '  + convert (char (8), o.crdate, 8)
            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
         union
            select name,
                   'usertype',
                   '-'
            from systypes
            where uid      = @uid
              and usertype > 99
         order by 'OBJECT', 'OBJECT TYPE'
      end
   else
      begin
         print '    THERE ARE NO OBJECTS OWNED BY <%1!>.', @USER

         print ' '
      end
go
sp_addcomment sp_ownedby, 17, 'Displays all objects owned by USER'
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,
       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),
                   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)

         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)),
                         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)),
                         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,
                         ' ',
                         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),
                         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),
                         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,
                         ' ',
                         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, 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),
           @maxrulelen  tinyint,
           @owner       varchar (30),
           @printmssg   varchar (70),
           @printrtxt   varchar (63),
           @rulename    varchar (63),
           @uidcount    tinyint

   if (@RULE IS NULL)
      begin
         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
               print '    THERE ARE NO RULES.'

               print ' '
            end
         else
            begin
               set nocount on

               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

                     exec 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 (78)
                        where 1 = 2
                     union
                        select object_name (c.id)
                               +  '.'
                               +   col_name (c.id, c.colid)
                        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,
                                          78)
                        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      char (30)     not null,
       c         smallint      not null,
       nul       tinyint       not null,
       size1     tinyint       not null,
       size2     tinyint       not null,
       comment   varchar (45)  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 '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.prec           IS NULL
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by (c.status & 8)
      having c.usertype        = 8
         and c.prec           IS NULL
         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 '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

   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 'real',
                      count (c.id),
                      convert (tinyint, convert (bit, (c.status & 8))),
                      4,
                      0,
                      NULL
      from syscolumns c,
           sysobjects o
      where c.usertype        = 23
        and c.prec           IS NULL
        and c.id              = o.id
        and (o.sysstat & 15)  = 3
      group by (c.status & 8)
      having c.usertype        = 23
         and c.prec           IS NULL
         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 '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

   update #typeagg
   set comment = 'Allowing nulls uses 8 bytes instead of 4'
   where name in ('real', 'smalldatetime', 'smallmoney')
     and nul   = 1

   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 (t.name, 1, 17),
                'NULL?' = substring (vB.name, 1, 3),
                COLUMNS = substring (convert (char (10), convert (money, t.c)), 1, 7),
                COMMENT = isnull (comment, ' ')
         from #typeagg t,
              sybsystemprocs.dbo.usptvalues vB
         where vB.number    = t.nul
           and vB.type      = 'B'
         order by substring (t.name, 1, 6), t.size1, t.size2, t.nul
      end
   else
      begin
         select 'TYPE'  = substring (t.name, 1, 17),
                'NULL?' = substring (vB.name, 1, 3),
                COLUMNS = substring (convert (char (10), convert (money, t.c)), 1, 7)
         from #typeagg t,
              sybsystemprocs.dbo.usptvalues vB
         where vB.number    = t.nul
           and vB.type      = 'B'
         order by substring (t.name, 1, 6), t.size1, t.size2, t.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
