use sybsystemprocs
go

drop procedure sp_columnagg
go
drop procedure sp_defaults
go
drop procedure sp_objectaggbyuser
go
drop procedure sp_objectagggeneral
go
drop procedure sp_objectaggmaster
go
drop procedure sp_objectagguser
go
drop procedure sp_objectaggusers        -- In 11031B release
go
drop procedure sp_owner                 -- In 1151A release
go
drop procedure sp_parameters
go
drop procedure sp_procs_fillprintname
go
drop procedure sp_procs
go
drop procedure sp_rules_all
go
drop procedure sp_rules
go
drop procedure sp_tabledetail           -- In 1151A release
go
drop procedure sp_showtable_detail
go
drop procedure sp_tablesx
go
drop procedure sp_triggers
go
drop procedure sp_usertypes
go
drop procedure sp_views
go

set quoted_identifier on
go

create procedure sp_columnagg (@NULL_FLAG varchar (1) = 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_columnagg> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   if (@NULL_FLAG IS NULL)
      begin
         print '    DATATYPES WILL NOT INCLUDE NULLABILITY.'
      end
   else
      begin
         print '    DATATYPES WILL INCLUDE NULLABILITY (*).'
      end

   print ' '

   create table #columnagg
     (column    varchar (30)  not null,
      ident     tinyint       not null,
      usertype  smallint      not null,
      stype     tinyint       not null,
      len       tinyint       not null,
      prec      tinyint       null,
      scale     tinyint       null,
      typetext  varchar (26)  not null,
      nulltype  int           not null)

   insert into #columnagg
      select c.name,
             convert (tinyint, convert (bit, (c.status & 128))),
             c.usertype,
             c.type,
             c.length,
             c.prec,
             c.scale,
             t.name,
             convert (int, convert (bit, (c.status & 8)))
      from syscolumns c,
           sysobjects o,
           systypes t
      where (o.sysstat & 15) in (2, 3)
        and o.id       = c.id
        and c.usertype = t.usertype

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

   update #columnagg
   set typetext = typetext + ' (' + convert (varchar (3), len) + ')'
   where usertype in (1, 2, 3, 4, 24, 25)

   update #columnagg
   set typetext = typetext
                  + ' ('
                  + convert (varchar (3), prec)
                  + ', '
                  + convert (varchar (3), scale)
                  + ')'
   where usertype in (10, 26)

   update #columnagg
   set typetext = 'float (' + convert (varchar (3), prec) + ')'
   where usertype in (8, 23)
     and prec     IS NOT NULL

   update #columnagg
   set typetext = 'real'
   where ((usertype = 23)  or  ((usertype = 8) and (len = 4)))
     and prec IS NULL

   update #columnagg
   set typetext = 'double precision'
   where usertype  = 8
     and len       = 8
     and prec     IS NULL

   update #columnagg
   set typetext = 'tinyint'
   where usertype in (5, 13)
     and len       = 1

   update #columnagg
   set typetext = 'smallint'
   where usertype in (6, 13)
     and len       = 2

   update #columnagg
   set typetext = 'int'
   where usertype in (7, 13)
     and len       = 4

   update #columnagg
   set typetext = typetext + ' (Identity)'
   where ident = 1

   if (@NULL_FLAG IS NOT NULL)
      begin
         update #columnagg
         set typetext = typetext + ' *'
         where nulltype = 1
      end

   select distinct 'COLUMN NAME' = substring (column, 1, 25),
                   'DATATYPE'    = substring (typetext, 1, 18),
                   'COUNT'       = substring (convert (char (8),
                                                       convert (money,
                                                                count (*)),
                                                       1),
                                              1,
                                              5)
   from #columnagg
   group by column, typetext
   order by column, typetext
go
sp_addcomment sp_columnagg, 14, 'Displays table/view column datatype aggregates (option: NULL_FLAG)'
go
sp_procxmode sp_columnagg, anymode
go

create procedure sp_defaults 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_defaults> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @CR      char (1),
           @LF      char (1),
           @as      char (4),
           @asLF    char (4),
           @LFas    char (4),
           @LFasLF  char (4)

   select @CR = char (13),                            -- Carriage Return
          @LF = char (10)                             -- Line Feed

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

   create table #defaults
      (dflt      char (30)    not null,
       dscrp     char (60)    null,
       owner     char (30)    not null,
       created   char ( 8)    not null)

   if (exists (select o.*
               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
         insert into #defaults
            select o.name,
                   substring (c.text, 1, 60),
                   isnull (user_name (o.uid), '?'),
                   convert (char (8), o.crdate, 11)
            from syscomments c,
                 sysobjects o,
                 sysprocedures p
            where (o.sysstat & 15)   = 6
              and o.id               = p.id
              and p.sequence         = 1
              and (p.status & 4096) <> 4096
              and o.id               = c.id
              and c.colid            = 1
              and c.texttype         = 0

         update #defaults
         set dscrp = substring (dscrp,
                                4 + charindex (@as, upper (dscrp)),
                                datalength (dscrp) - 3 - charindex (@as, upper (dscrp)))
         where charindex (@as, upper (dscrp)) > 0

         update #defaults
         set dscrp = substring (dscrp,
                                4 + charindex (@asLF, upper (dscrp)),
                                datalength (dscrp) - 3 - charindex (@asLF, upper (dscrp)))
         where charindex (@asLF, upper (dscrp)) > 0

         update #defaults
         set dscrp = substring (dscrp,
                                4 + charindex (@LFas, upper (dscrp)),
                                datalength (dscrp) - 3 - charindex (@LFas, upper (dscrp)))
         where charindex (@LFas, upper (dscrp)) > 0

         update #defaults
         set dscrp = substring (dscrp,
                                4 + charindex (@LFasLF, upper (dscrp)),
                                datalength (dscrp) - 3 - charindex (@LFasLF, upper (dscrp)))
         where charindex (@LFasLF, upper (dscrp)) > 0

         update #defaults
         set dscrp = ltrim (dscrp)

         update #defaults
         set dscrp = substring (dscrp, 1, charindex (@CR, dscrp) - 1)
                     + ' '
                     + substring (dscrp, charindex (@CR, dscrp) + 1, 40)
         where charindex (@CR, dscrp) > 1

         update #defaults
         set dscrp = substring (dscrp, 1, charindex (@LF, dscrp) - 1)
         where charindex (@LF, dscrp) > 1

         select 'DEFAULT'   = substring (dflt, 1, 21),
                DESCRIPTION = substring (dscrp, 1, 31),
                OWNER       = substring (owner, 1, 14),
                CREATED     = created
         from #defaults
         order by dflt, owner
      end
   else
      begin
         print '    THERE ARE NO USER-NAMED DEFAULTS.'

         print ' '
      end
go
sp_addcomment sp_defaults, 14, 'Lists all user-named defaults with their description'
go
sp_procxmode sp_defaults, anymode
go

------------ Temporary table needed for creation of sp_objectagg subroutines -

create table #objectagg
   (owner     varchar (30)                not null,
    type      varchar (37)                not null,
    qty       int                         not null,
    prntqty   char (8)      default ' '   not null,
    cached    char (6)      default null  null,
    sortorder varchar (36)  default ' '   not null)
go

create procedure sp_objectaggbyuser  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   declare @now  char (8)

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING TABLES AND VIEWS.                     (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      substring (vO.name, 1, 23),
                      count (o.id)
      from sysobjects o,
           sysusers u,
           sybsystemprocs.dbo.usptvalues vO
      where vO.number  = convert (int, o.sysstat & 15)
        and vO.type    = 'O'
        and vO.number in (1, 2, 3)
        and u.uid      = o.uid
      group by o.uid, (o.sysstat & 15)
      having vO.number  = convert (int, o.sysstat & 15)
         and vO.type    = 'O'
         and vO.number in (1, 2, 3)
         and u.uid      = o.uid

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (class)),
                                            1),
                                   1,
                                   6)
                 from sysattributes a,
                      sysobjects o
                 where a.class        = 3             -- buffer manager
                   and a.attribute    = 0             -- cache bindings
                   and a.object_type  = 'T'           -- table binding
                   and a.object       > 99            -- not a system table
                   and o.id           = a.object
                   and o.uid          = user_id (oa.owner))
   from #objectagg oa
   where type = 'user table'

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (class)),
                                            1),
                                   1,
                                   6)
                 from sysattributes
                 where class        = 3               -- buffer manager
                   and attribute    = 0               -- cache bindings
                   and object_type  = 'T'             -- table binding
                   and object       < 100)            -- system table
   from #objectagg
   where type = 'system table'

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING CHECK CONSTRAINTS.                    (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'check constraint',
                      count (o.id)
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15)  = 7
        and o.id              = p.id
        and p.sequence        = 1
        and (p.status & 4096) = 4096
        and u.uid             = o.uid
      group by o.uid, (o.sysstat & 15)
      having (o.sysstat & 15)  = 7
         and o.id              = p.id
         and p.sequence        = 1
         and (p.status & 4096) = 4096
         and u.uid             = o.uid

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING DEFAULTS.                             (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'default (user-named)',
                      count (o.id)
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15)   = 6
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and u.uid              = o.uid
      group by o.uid, (o.sysstat & 15)
      having (o.sysstat & 15)   = 6
         and o.id               = p.id
         and p.sequence         = 1
         and (p.status & 4096) <> 4096
         and u.uid              = o.uid

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'default (literal)',
                      count (o.id)
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15)  = 6
        and o.id              = p.id
        and p.sequence        = 1
        and (p.status & 4096) = 4096
        and u.uid             = o.uid
      group by o.uid, (o.sysstat & 15)
      having (o.sysstat & 15)  = 6
         and o.id              = p.id
         and p.sequence        = 1
         and (p.status & 4096) = 4096
         and u.uid             = o.uid

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING GRANTS AND REVOKES.                   (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct suser_name (u.suid), 'grant permission', count (p.id)
      from sysprotects p,
           sysusers u
      where p.protecttype       in (0, 1)
        and u.uid                = p.uid
        and suser_name (u.suid) IS NOT NULL
      group by p.uid
      having p.protecttype       in (0, 1)
         and u.uid                = p.uid
         and suser_name (u.suid) IS NOT NULL

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'), 'revoke permission', count (p.id)
      from sysprotects p,
           sysusers u
      where p.protecttype = 2
        and u.uid         = p.uid
      group by p.uid
      having p.protecttype = 2
         and u.uid         = p.uid

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING INDEXES.                              (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'system table index',
                      count (i.indid)
      from sysindexes i,
           sysobjects o,
           sysusers u
      where (o.sysstat & 15) = 1
        and o.id             = i.id
        and i.indid          > 0
        and i.indid          < 254
        and u.uid            = o.uid
      group by o.uid
      having (o.sysstat & 15) = 1
         and o.id             = i.id
         and i.indid          > 0
         and i.indid          < 254
         and u.uid            = o.uid

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'user table index',
                      count (i.indid)
      from sysindexes i,
           sysobjects o,
           sysusers u
      where (o.sysstat & 15) = 3
        and o.id             = i.id
        and i.indid          > 0
        and i.indid          < 254
        and u.uid            = o.uid
      group by o.uid
      having (o.sysstat & 15) = 3
         and o.id             = i.id
         and i.indid          > 0
         and i.indid          < 254
         and u.uid            = o.uid

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (class)),
                                            1),
                                   1,
                                   6)
                 from sysattributes
                 where class        = 3               -- buffer manager
                   and attribute    = 0               -- cache bindings
                   and object_type  = 'I'             -- index binding
                   and object       < 100)            -- system table
   from #objectagg
   where type = 'system table index'

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (a.class)),
                                            1),
                                   1,
                                   6)
                 from sysattributes a,
                      sysobjects o
                 where a.class        = 3             -- buffer manager
                   and a.attribute    = 0             -- cache bindings
                   and a.object_type  = 'I'           -- index binding
                   and a.object       > 99            -- not a system table
                   and o.id           = a.object
                   and o.uid          = user_id (oa.owner))
   from #objectagg oa
   where type = 'user table index'

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING LOGGED USER MESSAGES.                 (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'logged user message',
                      count (m.error)
      from sysusermessages m,
           sysusers u
      where (m.dlevel & 128) <> 128
        and u.uid             = m.uid
      group by m.uid
      having (m.dlevel & 128) <> 128
         and u.uid             = m.uid

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING REFERENTIAL CONSTRAINTS.              (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'referential constraint (internal)',
                      count (r.constrid)
      from sysobjects o,
           sysreferences r,
           sysusers u
      where r.constrid = o.id
        and r.frgndbid = db_id ()
        and r.pmrydbid = db_id ()
        and u.uid      = o.uid
      group by o.uid
      having r.constrid = o.id
         and r.frgndbid = db_id ()
         and r.pmrydbid = db_id ()
         and u.uid      = o.uid

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'referential constraint (external)',
                      count (r.constrid)
      from sysobjects o,
           sysreferences r,
           sysusers u
      where r.constrid = o.id
        and ((r.frgndbid <> db_id ())  or  (r.pmrydbid <> db_id ()))
        and u.uid      = o.uid
      group by o.uid
      having r.constrid = o.id
         and ((r.frgndbid <> db_id ())  or  (r.pmrydbid <> db_id ()))
         and u.uid      = o.uid

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING RULES.                                (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'), 'rule', count (o.id)
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and u.uid              = o.uid
      group by o.uid, (o.sysstat & 15)
      having (o.sysstat & 15)   = 7
         and o.id               = p.id
         and p.sequence         = 1
         and (p.status & 4096) <> 4096
         and u.uid              = o.uid

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING STORED PROCEDURES.                    (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'stored procedure',
                      count (o.id)
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15)         = 4
        and ((o.sysstat2 / 16) & 3)  = 0
        and o.type                  <> 'XP'
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 64)         <> 64
        and u.uid                    = o.uid
      group by o.uid, (o.sysstat & 15), (o.sysstat2 & 32)
      having (o.sysstat & 15)         = 4
         and ((o.sysstat2 / 16) & 3)  = 0
         and o.type                  <> 'XP'
         and o.id                     = p.id
         and p.sequence               = 1
         and (p.status & 64)         <> 64
         and u.uid                    = o.uid

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'stored procedure (any)',
                      count (o.id)
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15)         = 4
        and ((o.sysstat2 / 16) & 3)  = 2
        and o.type                  <> 'XP'
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 64)         <> 64
        and u.uid                    = o.uid
      group by o.uid, (o.sysstat & 15), (o.sysstat2 & 32)
      having (o.sysstat & 15)         = 4
         and ((o.sysstat2 / 16) & 3)  = 2
         and o.type                  <> 'XP'
         and o.id                     = p.id
         and p.sequence               = 1
         and (p.status & 64)         <> 64
         and u.uid                    = o.uid

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'stored procedure (chained)',
                      count (o.id)
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15)         = 4
        and ((o.sysstat2 / 16) & 3)  = 1
        and o.type                  <> 'XP'
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 64)         <> 64
        and u.uid                    = o.uid
      group by o.uid, (o.sysstat & 15), (o.sysstat2 & 32)
      having (o.sysstat & 15)         = 4
         and ((o.sysstat2 / 16) & 3)  = 1
         and o.type                  <> 'XP'
         and o.id                     = p.id
         and p.sequence               = 1
         and (p.status & 64)         <> 64
         and u.uid                    = o.uid

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'stored procedure (recompile)',
                      count (o.id)
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15)        = 4
        and ((o.sysstat2 / 16) & 3) = 0
        and o.id                    = p.id
        and p.sequence              = 1
        and (p.status & 64)         = 64
        and u.uid                   = o.uid
      group by o.uid, (o.sysstat & 15), (o.sysstat2 & 32)
      having (o.sysstat & 15)        = 4
         and ((o.sysstat2 / 16) & 3) = 0
         and o.id                    = p.id
         and p.sequence              = 1
         and (p.status & 64)         = 64
         and u.uid                   = o.uid

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'stored procedure (any, recompile)',
                      count (o.id)
      from sysobjects o,
           sysprocedures p,
           sysusers u
      where (o.sysstat & 15)        = 4
        and ((o.sysstat2 / 16) & 3) = 2
        and o.id                    = p.id
        and p.sequence              = 1
        and (p.status & 64)         = 64
        and u.uid                   = o.uid
      group by o.uid, (o.sysstat & 15), (o.sysstat2 & 32)
      having (o.sysstat & 15)        = 4
         and ((o.sysstat2 / 16) & 3) = 2
         and o.id                    = p.id
         and p.sequence              = 1
         and (p.status & 64)         = 64
         and u.uid                   = o.uid

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'stored procedure (chained, recompile)',
                      count (o.id)
      from sysobjects o,
            sysprocedures p,
            sysusers u
      where (o.sysstat & 15)        = 4
        and ((o.sysstat2 / 16) & 3) = 1
        and o.id                    = p.id
        and p.sequence              = 1
        and (p.status & 64)         = 64
        and u.uid                   = o.uid
      group by o.uid, (o.sysstat & 15), (o.sysstat2 & 32)
      having (o.sysstat & 15)        = 4
         and ((o.sysstat2 / 16) & 3) = 1
         and o.id                    = p.id
         and p.sequence              = 1
         and (p.status & 64)         = 64
         and u.uid                   = o.uid

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING TRIGGERS.                             (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'), 'trigger (delete)', count (t.id)
      from sysobjects t,
           sysobjects d,
           sysusers u
      where t.deltrig         = d.id
        and d.id             <> 0
        and (d.sysstat & 15)  = 8
        and u.uid             = d.uid
      group by d.uid
      having t.deltrig         = d.id
         and d.id             <> 0
         and (d.sysstat & 15)  = 8
         and u.uid             = d.uid

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'), 'trigger (insert)', count (t.id)
      from sysobjects t,
           sysobjects i,
           sysusers u
      where t.instrig         = i.id
        and i.id             <> 0
        and (i.sysstat & 15)  = 8
        and u.uid             = i.uid
      group by i.uid
      having t.instrig         = i.id
         and i.id             <> 0
         and (i.sysstat & 15)  = 8
         and u.uid             = i.uid

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (uu.suid), '?'), 'trigger (update)', count (t.id)
      from sysobjects t,
           sysobjects u,
           sysusers   uu
      where t.updtrig         = u.id
        and u.id             <> 0
        and (u.sysstat & 15)  = 8
        and uu.uid            = u.uid
      group by u.uid
      having t.updtrig         = u.id
         and u.id             <> 0
         and (u.sysstat & 15)  = 8
         and uu.uid            = u.uid

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING USERTYPES.                            (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'), 'usertype', count (t.uid)
      from systypes t,
           sysusers u
      where t.usertype > 99
        and u.uid      = t.uid
      group by t.uid
      having t.usertype > 99
         and u.uid      = t.uid

   print ' '

   print '    COUNT TABLE OF USER/OBJECT TYPES.'

   print ' '

   delete from #objectagg
   where qty = 0

   update #objectagg
   set prntqty   = substring (convert (char (11), convert (money, qty), 1), 1, 8),
       sortorder = substring (owner + type, 1, 36)

   insert into #objectagg
      select ' ', ' ', 0, ' ', '      ', min (sortorder)
      from #objectagg
      group by owner

   delete from #objectagg
   where owner     = ' '
     and sortorder = (select min (sortorder)
                      from #objectagg)

   if (exists (select *
               from #objectagg
               where cached IS NOT NULL
                 and cached <> '      '
                 and cached <> '     0'))
      begin
         select 'USER'        = substring (owner, 1, 15),
                'OBJECT TYPE' = type,
                QUANTITY      = prntqty,
                CACHED        = isnull (cached, '     -')
         from #objectagg
         order by sortorder
      end
   else
      begin
         select 'USER'        = substring (owner, 1, 15),
                'OBJECT TYPE' = type,
                QUANTITY      = prntqty
         from #objectagg
         order by sortorder
      end
go
sp_procxmode sp_objectaggbyuser, unchained
go

create procedure sp_objectagggeneral as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @now  char (8)

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING TABLES AND VIEWS.                     (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct ' ', substring (vO.name, 1, 23), count (o.id)
      from sysobjects o,
           sybsystemprocs.dbo.usptvalues vO
      where vO.number  = convert (int, o.sysstat & 15)
        and vO.type    = 'O'
        and vO.number in (1, 2, 3)
      group by (o.sysstat & 15)
      having vO.number  = convert (int, o.sysstat & 15)
         and vO.type    = 'O'
         and vO.number in (1, 2, 3)

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (class)),
                                            1),
                                   1,
                                   6)
                 from sysattributes
                 where class        = 3               -- buffer manager
                   and attribute    = 0               -- cache bindings
                   and object_type  = 'T'             -- table binding
                   and object       > 99)             -- not a system table
   where type = 'user table'

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (class)),
                                            1),
                                   1,
                                   6)
                   from sysattributes
                   where class        = 3             -- buffer manager
                     and attribute    = 0             -- cache bindings
                     and object_type  = 'T'           -- table binding
                     and object       < 100)          -- system table
   where type = 'system table'

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING CHECK CONSTRAINTS.                    (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'check constraint', count (p.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)  = 7
        and o.id              = p.id
        and p.sequence        = 1
        and (p.status & 4096) = 4096

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING DEFAULTS.                             (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'default (user-named)', count (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

   insert into #objectagg (owner, type, qty)
      select ' ', 'default (constant/function)', count (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

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING GRANTS AND REVOKES.                   (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'grant permission (group)', count (p.id)
      from sysprotects p,
           sysusers u
      where p.protecttype in (0, 1)
        and u.uid          = p.uid
        and u.uid          = u.gid
        and (not exists (select r.lrid
                         from sysroles r
                         where r.lrid = u.uid))

   insert into #objectagg (owner, type, qty)
      select ' ', 'grant permission (role)', count (p.id)
      from sysprotects p,
           sysusers u
      where p.protecttype in (0, 1)
        and u.uid          = p.uid
        and u.uid          = u.gid
        and (exists (select r.lrid
                     from sysroles r
                     where r.lrid = u.uid))

   insert into #objectagg (owner, type, qty)
      select ' ', 'grant permission (user)', count (p.id)
      from sysprotects p,
           sysusers u
      where p.protecttype in (0, 1)
        and u.uid          = p.uid
        and u.uid         <> u.gid

   insert into #objectagg (owner, type, qty)
      select ' ', 'revoke permission (group)', count (p.id)
      from sysprotects p,
           sysusers u
      where p.protecttype = 2
        and u.uid         = p.uid
        and u.uid         = u.gid
        and (not exists (select r.lrid
                         from sysroles r
                         where r.lrid = u.uid))

   insert into #objectagg (owner, type, qty)
      select ' ', 'revoke permission (role)', count (p.id)
      from sysprotects p,
           sysusers u
      where p.protecttype = 2
        and u.uid         = p.uid
        and u.uid         = u.gid
        and (exists (select r.lrid
                     from sysroles r
                     where r.lrid = u.uid))

   insert into #objectagg (owner, type, qty)
      select ' ', 'revoke permission (user)', count (p.id)
      from sysprotects p,
           sysusers u
      where p.protecttype  = 2
        and u.uid          = p.uid
        and u.uid         <> u.gid

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING GROUPS.                               (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'group', count (ug.uid)
      from sysusers ug
      where ug.uid = ug.gid
        and not exists (select r.lrid
                        from sysroles r
                        where r.lrid = ug.gid)

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING INDEXES.                              (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'system table index', count (*)
      from sysindexes
      where id    < 100
        and indid > 0
        and indid < 254

   insert into #objectagg (owner, type, qty)
      select ' ', 'user table index', count (i.indid)
      from sysindexes i,
           sysobjects o
      where (o.sysstat & 15) = 3
        and o.id             = i.id
        and i.indid          > 0
        and i.indid          < 254

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (class)),
                                            1),
                                   1,
                                   6)
                 from sysattributes
                 where class        = 3               -- buffer manager
                   and attribute    = 0               -- cache bindings
                   and object_type  = 'I'             -- index binding
                   and object       < 100)            -- system table
   where type = 'system table index'

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (class)),
                                            1),
                                   1,
                                   6)
                 from sysattributes
                 where class        = 3               -- buffer manager
                   and attribute    = 0               -- cache bindings
                   and object_type  = 'I'             -- index binding
                   and object       > 99)             -- not a system table
   where type = 'user table index'

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING LOGGED USER MESSAGES.                 (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'logged user message', count (error)
      from sysusermessages
      where (dlevel & 128) <> 128

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING REFERENTIAL CONSTRAINTS.              (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct ' ', 'referential constraint (internal)', count (constrid)
      from sysreferences
      where frgndbid = db_id ()
        and pmrydbid = db_id ()

   insert into #objectagg (owner, type, qty)
      select distinct ' ', 'referential constraint (external)', count (constrid)
      from sysreferences
      where frgndbid <> db_id ()
         or pmrydbid <> db_id ()

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING RULES.                                (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'rule', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING STORED PROCEDURES.                    (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)         = 4
        and ((o.sysstat2 / 16) & 3)  = 0
        and o.type                  <> 'XP'
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 64)         <> 64

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure (any)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)         = 4
        and ((o.sysstat2 / 16) & 3)  = 2
        and o.type                  <> 'XP'
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 64)         <> 64

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure (chained)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)         = 4
        and ((o.sysstat2 / 16) & 3)  = 1
        and o.type                  <> 'XP'
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 64)         <> 64

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure (recompile)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)        = 4
        and ((o.sysstat2 / 16) & 3) = 0
        and o.id                    = p.id
        and p.sequence              = 1
        and (p.status & 64)         = 64

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure (any, recompile)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)        = 4
        and ((o.sysstat2 / 16) & 3) = 2
        and o.id                    = p.id
        and p.sequence              = 1
        and (p.status & 64)         = 64

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure (chained, recompile)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)        = 4
        and ((o.sysstat2 / 16) & 3) = 1
        and o.id                    = p.id
        and p.sequence              = 1
        and (p.status & 64)         = 64

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING TRIGGERS.                             (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'trigger (delete)', count (t.id)
      from sysobjects t,
           sysobjects d
      where t.deltrig        <> 0
        and t.deltrig         = d.id
        and (d.sysstat & 15)  = 8

   insert into #objectagg (owner, type, qty)
      select ' ', 'trigger (insert)', count (t.id)
      from sysobjects t,
           sysobjects i
      where t.instrig        <> 0
        and t.instrig         = i.id
        and (i.sysstat & 15)  = 8

   insert into #objectagg (owner, type, qty)
      select ' ', 'trigger (update)', count (t.id)
      from sysobjects t,
           sysobjects u
      where t.updtrig        <> 0
        and t.updtrig         = u.id
        and (u.sysstat & 15)  = 8

   print '    COUNTING USERS.                                (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'user', count (u.suid) + 1                -- include "dbo"
      from sysusers u,
           master.dbo.syslogins l
      where u.suid > 1
        and l.suid = u.suid
        and l.name = u.name

   insert into #objectagg (owner, type, qty)
      select ' ', 'user (alias)', count (*)
      from sysalternates

   insert into #objectagg (owner, type, qty)
      select ' ', 'user (pseudonym)', count (u.suid) - 1    -- exclude "dbo"
      from sysusers u,
           master.dbo.syslogins l
      where l.suid  = u.suid
        and l.name <> u.name

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING USERTYPES.                            (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'usertype', count (*)
      from systypes
      where usertype > 99

   print ' '

   delete from #objectagg
   where qty = 0

   update #objectagg
   set prntqty = substring (convert (char (11), convert (money, qty), 1), 1, 8)

   if (exists (select *
               from #objectagg
               where cached IS NOT NULL
                 and cached <> '     0'))
      begin
         select 'OBJECT TYPE' = type,
                QUANTITY      = prntqty,
                CACHED        = isnull (cached, '     -')
         from #objectagg
         order by type
      end
   else
      begin
         select 'OBJECT TYPE' = type,
                QUANTITY      = prntqty
         from #objectagg
         order by type
      end
go
sp_procxmode sp_objectagggeneral, unchained
go

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

   declare @now  char (8),
           @uid  smallint

   if ((upper (@LOGIN) = 'BYUSER')  or  (@LOGIN = 'sa')  or  (@LOGIN IS NULL))
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    COUNTING CACHES.                               (%1!)', @now

         insert into #objectagg (owner, type, qty)
            select 'sa', 'cache', count (*)
            from master.dbo.sysconfigures
            where config         = 19
              and parent         = 19
              and (status & 64) <> 64
      end

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING DATABASES.                            (%1!)', @now

   if (@LOGIN IS NULL)
      begin
         insert into #objectagg (owner, type, qty)
            select ' ', 'database', count (*)
            from master.dbo.sysdatabases

         update #objectagg
         set cached = (select substring (convert (char (9),
                                                  convert (money,
                                                           count (class)),
                                                  1),
                                         1,
                                         6)
                       from master.dbo.sysattributes
                       where class        = 3               -- buffer manager
                         and attribute    = 0               -- cache bindings
                         and object_type  = 'D')            -- database binding
         where type = 'database'
      end
   else if (upper (@LOGIN) = 'BYUSER')
      begin
         insert into #objectagg (owner, type, qty)
            select suser_name (suid), 'database', count (*)
            from master.dbo.sysdatabases
            group by suid

         update #objectagg
         set cached = (select substring (convert (char (9),
                                                  convert (money,
                                                           count (a.class)),
                                                  1),
                                         1,
                                         6)
                       from master.dbo.sysattributes a,
                            master.dbo.sysdatabases d
                       where a.class        = 3             -- buffer manager
                         and a.attribute    = 0             -- cache bindings
                         and a.object_type  = 'D'           -- database binding
                         and a.object       = d.dbid
                         and d.suid         = suser_id (@LOGIN)
                         and oa.owner       = @LOGIN)
         from #objectagg oa
         where type = 'database'
      end
   else
      begin
         insert into #objectagg (owner, type, qty)
            select ' ', 'database', count (*)
            from master.dbo.sysdatabases
            where suid = suser_id (@LOGIN)

         update #objectagg
         set cached = (select substring (convert (char (9),
                                                  convert (money,
                                                           count (a.class)),
                                                  1),
                                         1,
                                         6)
                       from master.dbo.sysattributes a,
                            master.dbo.sysdatabases d
                       where a.class        = 3             -- buffer manager
                         and a.attribute    = 0             -- cache bindings
                         and a.object_type  = 'D'           -- database binding
                         and a.object       = d.dbid
                         and d.suid         = suser_id (@LOGIN))
         where type = 'database'
      end

   if ((upper (@LOGIN) = 'BYUSER')  or  (@LOGIN = 'sa')  or  (@LOGIN IS NULL))
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    COUNTING LOGINS.                               (%1!)', @now

         insert into #objectagg (owner, type, qty)
            select 'sa', 'login', count (*)
            from master.dbo.syslogins

         select @now = convert (char (8), getdate (), 8)

         print '    COUNTING REMOTE LOGINS.                        (%1!)', @now

         insert into #objectagg (owner, type, qty)
            select 'sa', 'remote login', count (*)
            from master.dbo.sysremotelogins

         select @now = convert (char (8), getdate (), 8)

         print '    COUNTING REMOTE SERVERS.                       (%1!)', @now

         insert into #objectagg (owner, type, qty)
            select 'sa', 'remote server', count (*)
            from master.dbo.sysservers
            where srvname <> @@servername
      end

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING EXTENDED STORED PROCEDURES.           (%1!)', @now

   --  Note that "recompile" does not apply to extended stored procedures.

   if (@LOGIN IS NULL)
      begin
         insert into #objectagg (owner, type, qty)
            select ' ', 'stored procedure (xp)', count (o.id)
            from sysobjects o,
                 sysprocedures p
            where (o.sysstat & 15)         = 4
              and ((o.sysstat2 / 16) & 3)  = 0
              and o.type                   = 'XP'
              and o.id                     = p.id
              and p.sequence               = 1
              and (p.status & 64)         <> 64

         insert into #objectagg (owner, type, qty)
            select ' ', 'stored procedure (any, xp)', count (o.id)
            from sysobjects o,
                 sysprocedures p
            where (o.sysstat & 15)         = 4
              and ((o.sysstat2 / 16) & 3)  = 2
              and o.type                   = 'XP'
              and o.id                     = p.id
              and p.sequence               = 1
              and (p.status & 64)         <> 64

         insert into #objectagg (owner, type, qty)
            select ' ', 'stored procedure (chained, xp)', count (o.id)
            from sysobjects o,
                 sysprocedures p
            where (o.sysstat & 15)         = 4
              and ((o.sysstat2 / 16) & 3)  = 1
              and o.type                   = 'XP'
              and o.id                     = p.id
              and p.sequence               = 1
              and (p.status & 64)         <> 64
      end
   else if (upper (@LOGIN) = 'BYUSER')
      begin
         insert into #objectagg (owner, type, qty)
            select distinct isnull (suser_name (u.suid), '?'),
                            'stored procedure (xp)',
                            count (o.id)
            from sysobjects o,
                 sysprocedures p,
                 sysusers u
            where (o.sysstat & 15)         = 4
              and ((o.sysstat2 / 16) & 3)  = 0
              and o.type                   = 'XP'
              and o.id                     = p.id
              and p.sequence               = 1
              and (p.status & 64)         <> 64
              and u.uid                    = o.uid
            group by o.uid, (o.sysstat & 15), (o.sysstat2 & 32)
            having (o.sysstat & 15)         = 4
               and ((o.sysstat2 / 16) & 3)  = 0
               and o.type                   = 'XP'
               and o.id                     = p.id
               and p.sequence               = 1
               and (p.status & 64)         <> 64
               and u.uid                    = o.uid

         insert into #objectagg (owner, type, qty)
            select distinct isnull (suser_name (u.suid), '?'),
                            'stored procedure (any, xp)',
                            count (o.id)
            from sysobjects o,
                 sysprocedures p,
                 sysusers u
            where (o.sysstat & 15)         = 4
              and ((o.sysstat2 / 16) & 3)  = 2
              and o.type                   = 'XP'
              and o.id                     = p.id
              and p.sequence               = 1
              and (p.status & 64)         <> 64
              and u.uid                    = o.uid
            group by o.uid, (o.sysstat & 15), (o.sysstat2 & 32)
            having (o.sysstat & 15)         = 4
               and ((o.sysstat2 / 16) & 3)  = 2
               and o.type                   = 'XP'
               and o.id                     = p.id
               and p.sequence               = 1
               and (p.status & 64)         <> 64
               and u.uid                    = o.uid

         insert into #objectagg (owner, type, qty)
            select distinct isnull (suser_name (u.suid), '?'),
                            'stored procedure (chained, xp)',
                            count (o.id)
            from sysobjects o,
                 sysprocedures p,
                 sysusers u
            where (o.sysstat & 15)         = 4
              and ((o.sysstat2 / 16) & 3)  = 1
              and o.type                   = 'XP'
              and o.id                     = p.id
              and p.sequence               = 1
              and (p.status & 64)         <> 64
              and u.uid                    = o.uid
            group by o.uid, (o.sysstat & 15), (o.sysstat2 & 32)
            having (o.sysstat & 15)         = 4
               and ((o.sysstat2 / 16) & 3)  = 1
               and o.type                   = 'XP'
               and o.id                     = p.id
               and p.sequence               = 1
               and (p.status & 64)         <> 64
               and u.uid                    = o.uid
      end
   else
      begin
         select @uid = uid
         from sysusers
         where suid = suser_id (@LOGIN)

         insert into #objectagg (owner, type, qty)
            select ' ', 'stored procedure (xp)', count (o.id)
            from sysobjects o,
                 sysprocedures p
            where (o.sysstat & 15)         = 4
              and ((o.sysstat2 / 16) & 3)  = 0
              and o.type                   = 'XP'
              and o.uid                    = @uid
              and o.id                     = p.id
              and p.sequence               = 1
              and (p.status & 64)         <> 64

         insert into #objectagg (owner, type, qty)
            select ' ', 'stored procedure (any, xp)', count (o.id)
            from sysobjects o,
                 sysprocedures p
            where (o.sysstat & 15)         = 4
              and ((o.sysstat2 / 16) & 3)  = 2
              and o.type                   = 'XP'
              and o.uid                    = @uid
              and o.id                     = p.id
              and p.sequence               = 1
              and (p.status & 64)         <> 64

         insert into #objectagg (owner, type, qty)
            select ' ', 'stored procedure (chained, xp)', count (o.id)
            from sysobjects o,
                 sysprocedures p
            where (o.sysstat & 15)         = 4
              and ((o.sysstat2 / 16) & 3)  = 1
              and o.type                   = 'XP'
              and o.uid                    = @uid
              and o.id                     = p.id
              and p.sequence               = 1
              and (p.status & 64)         <> 64
      end

   if ((upper (@LOGIN) = 'BYUSER')  or  (@LOGIN = 'sa')  or  (@LOGIN IS NULL))
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    COUNTING LOGGED NON-FATAL SYSTEM MESSAGES.     (%1!)', @now

         insert into #objectagg (owner, type, qty)
            select ' ', 'logged system message (non-fatal)', count (error)
            from master.dbo.sysmessages
            where severity          < 19
              and (dlevel & -32766) = -32766

         select @now = convert (char (8), getdate (), 8)

         print '    COUNTING ROLES.                                (%1!)', @now

         insert into #objectagg (owner, type, qty)
            select ' ', 'role (system)', count (srid)
            from master.dbo.syssrvroles
            where srid < 32

         insert into #objectagg (owner, type, qty)
            select ' ', 'role (user)', count (srid)
            from master.dbo.syssrvroles
            where srid > 31
      end
go
sp_procxmode sp_objectaggmaster, unchained
go

create procedure sp_objectagguser (@UID    smallint,
                                   @LOGIN  varchar (30))  as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @now  char (8)

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING TABLES AND VIEWS.                     (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct ' ', substring (vO.name, 1, 23), count (o.id)
      from sysobjects o,
           sybsystemprocs.dbo.usptvalues vO
      where vO.number  = convert (int, o.sysstat & 15)
        and vO.type    = 'O'
        and vO.number in (1, 2, 3)
        and o.uid      = @UID
      group by (o.sysstat & 15)
      having vO.number  = convert (int, o.sysstat & 15)
         and vO.type    = 'O'
         and vO.number in (1, 2, 3)
         and o.uid      = @UID

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (a.class)),
                                            1),
                                   1,
                                   6)
                 from sysattributes a,
                      sysobjects o
                 where a.class        = 3             -- buffer manager
                   and a.attribute    = 0             -- cache bindings
                   and a.object_type  = 'T'           -- table binding
                   and a.object       > 99            -- not a system table
                   and o.id           = a.object
                   and o.uid          = @UID)
   where type = 'user table'

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (a.class)),
                                            1),
                                   1,
                                   6)
                 from sysattributes a,
                      sysobjects o
                 where a.class        = 3             -- buffer manager
                   and a.attribute    = 0             -- cache bindings
                   and a.object_type  = 'T'           -- table binding
                   and a.object       < 100           -- system table
                   and o.id           = a.object
                   and o.uid          = @UID)
   where type = 'system table'

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING CHECK CONSTRAINTS.                    (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'check constraint', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)  = 7
        and o.uid             = @UID
        and o.id              = p.id
        and p.sequence        = 1
        and (p.status & 4096) = 4096

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING DEFAULTS.                             (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'default (user-named)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 6
        and o.uid              = @UID
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096

   insert into #objectagg (owner, type, qty)
      select ' ', 'default (constant/function)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)  = 6
        and o.uid             = @UID
        and o.id              = p.id
        and p.sequence        = 1
        and (p.status & 4096) = 4096

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING GRANTS AND REVOKES.                   (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'grant permission', count (id)
      from sysprotects
      where protecttype in (0, 1)
        and uid          = @UID

   insert into #objectagg (owner, type, qty)
      select ' ', 'revoke permission', count (id)
      from sysprotects
      where protecttype = 2
        and uid         = @UID

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING INDEXES.                              (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'system table index', count (*)
      from sysindexes
      where id    < 100
        and indid > 0
        and indid < 254
        and @UID  = 1

   insert into #objectagg (owner, type, qty)
      select ' ', 'user table index', count (i.indid)
      from sysindexes i,
           sysobjects o
      where (o.sysstat & 15) = 3
        and o.uid            = @UID
        and o.id             = i.id
        and i.indid          > 0
        and i.indid          < 254

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (class)),
                                            1),
                                   1,
                                   6)
                 from sysattributes
                 where class        = 3               -- buffer manager
                   and attribute    = 0               -- cache bindings
                   and object_type  = 'I'             -- index binding
                   and object       < 100             -- system table
                   and @UID         = 1)
   where type = 'system table index'

   update #objectagg
   set cached = (select substring (convert (char (9),
                                            convert (money,
                                                     count (a.class)),
                                            1),
                                   1,
                                   6)
                 from sysattributes a,
                      sysobjects o
                 where a.class        = 3             -- buffer manager
                   and a.attribute    = 0             -- cache bindings
                   and a.object_type  = 'I'           -- index binding
                   and a.object       > 99            -- not a system table
                   and o.id           = a.object
                   and o.uid          = @UID)
   where type = 'user table index'

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING LOGGED USER MESSAGES.                 (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'logged user message', count (error)
      from sysusermessages
      where uid             = @UID
        and (dlevel & 128) <> 128

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING REFERENTIAL CONSTRAINTS.              (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct ' ', 'referential constraint (internal)', count (r.constrid)
      from sysobjects o,
           sysreferences r
      where o.uid      = @UID
        and r.constrid = o.id
        and r.frgndbid = db_id ()
        and r.pmrydbid = db_id ()

   insert into #objectagg (owner, type, qty)
      select distinct ' ', 'referential constraint (external)', count (r.constrid)
      from sysobjects o,
           sysreferences r
      where o.uid      = @UID
        and r.constrid = o.id
        and ((r.frgndbid <> db_id ())  or  (r.pmrydbid <> db_id ()))

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING RULES.                                (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'rule', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.uid              = @UID
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING STORED PROCEDURES.                    (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)         = 4
        and ((o.sysstat2 / 16) & 3)  = 0
        and o.type                  <> 'XP'
        and o.uid                    = @UID
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 64)         <> 64

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure (any)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)         = 4
        and ((o.sysstat2 / 16) & 3)  = 2
        and o.type                  <> 'XP'
        and o.uid                    = @UID
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 64)         <> 64

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure (chained)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)         = 4
        and ((o.sysstat2 / 16) & 3)  = 1
        and o.type                  <> 'XP'
        and o.uid                    = @UID
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 64)         <> 64

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure (recompile)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)         = 4
        and ((o.sysstat2 / 16) & 3)  = 0
        and o.uid                    = @UID
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 64)          = 64

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure (any, recompile)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)        = 4
        and ((o.sysstat2 / 16) & 3) = 2
        and o.uid                   = @UID
        and o.id                    = p.id
        and p.sequence              = 1
        and (p.status & 64)         = 64

   insert into #objectagg (owner, type, qty)
      select ' ', 'stored procedure (chained, recompile)', count (o.id)
      from sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)        = 4
        and ((o.sysstat2 / 16) & 3) = 1
        and o.uid                   = @UID
        and o.id                    = p.id
        and p.sequence              = 1
        and (p.status & 64)         = 64

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING TRIGGERS.                             (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'trigger (delete)', count (t.id)
      from sysobjects t,
           sysobjects d
      where t.uid             = @UID
        and t.deltrig        <> 0
        and t.deltrig         = d.id
        and (d.sysstat & 15)  = 8

   insert into #objectagg (owner, type, qty)
      select ' ', 'trigger (insert)', count (t.id)
      from sysobjects t,
           sysobjects i
      where t.instrig        <> 0
        and t.instrig         = i.id
        and (i.sysstat & 15)  = 8
        and i.uid             = @UID

   insert into #objectagg (owner, type, qty)
      select ' ', 'trigger (update)', count (t.id)
      from sysobjects t,
           sysobjects u
      where t.updtrig        <> 0
        and t.updtrig         = u.id
        and (u.sysstat & 15)  = 8
        and u.uid             = @UID

   select @now = convert (char (8), getdate (), 8)

   print '    COUNTING USERTYPES.                            (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'usertype', count (*)
      from systypes
      where usertype > 99
        and uid      = @UID

   delete from #objectagg
   where qty = 0

   print ' '

   if (exists (select *
               from #objectagg))
      begin
         print '    COUNT TABLE OF OBJECT TYPES FOR LOGIN %1!.', @LOGIN

         print ' '

         update #objectagg
         set prntqty = substring (convert (char (11), convert (money, qty), 1), 1, 8)

         if (exists (select *
                     from #objectagg
                     where cached IS NOT NULL
                       and cached <> '     0'))
            begin
               select 'OBJECT TYPE' = type,
                      QUANTITY      = prntqty,
                      CACHED        = isnull (cached, '     -')
               from #objectagg
               order by type
            end
         else
            begin
               select 'OBJECT TYPE' = type,
                      QUANTITY      = prntqty
               from #objectagg
               order by type
            end
      end
   else
      begin
         print '    LOGIN %1! OWNS NO OBJECTS.', @LOGIN

         print ' '
      end
go
sp_procxmode sp_objectagguser, unchained
go

drop table #objectagg
go

create procedure sp_parameters (@PROCEDURE  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_parameters> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @dotpstn    tinyint,
           @id         int,
           @owner      varchar (30),
           @spcount    smallint,
           @spcstring  varchar (7),
           @uidcount   tinyint

   create table #parameters
      (spname     varchar (40)  not null,
       pname      varchar (30)  not null,
       colid      tinyint       not null,
       typname    char (16)     not null,
       utype      smallint      not null,
       stype      tinyint       not null,
       len        tinyint       not null,
       prec       tinyint       null,
       scale      tinyint       null,
       sortfield  varchar (50)  not null)

   if (@PROCEDURE IS NULL)
      begin
         if (exists (select *
                     from sysobjects
                     where (sysstat & 15) = 4))
            begin
               select @spcount = count (o.uid)
               from sysobjects o
               where (o.sysstat & 15) = 4
                 and not exists (select c.*
                                 from syscolumns c
                                 where c.id = o.id)

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

                     print '    THERE ARE %1! STORED PROCEDURES WITHOUT PARAMETERS.',
                           @spcstring

                     print ' '

                        select 'STORED PROCEDURE' = space (35),
                               'DATE'             = space (8)
                        where 1 = 2
                     union
                        select o.name,
                               convert (char (8), crdate, 11)
                        from sysobjects o
                        where o.uid            = 1
                          and (o.sysstat & 15) = 4
                          and 1                = (select count (o2.uid)
                                                  from sysobjects o2
                                                  where o2.name           = o.name
                                                    and (o2.sysstat & 15) = 4)
                          and not exists (select c.*
                                          from syscolumns c
                                          where c.id = o.id)
                     union
                        select substring (o.name + ' (' + user_name (o.uid) + ')', 1, 35),
                               convert (char (8), crdate, 11)
                        from sysobjects o
                        where o.uid            = 1
                          and (o.sysstat & 15) = 4
                          and 1                < (select count (o2.uid)
                                                  from sysobjects o2
                                                  where o2.name           = o.name
                                                    and (o2.sysstat & 15) = 4)
                          and not exists (select c.*
                                          from syscolumns c
                                          where c.id = o.id)
                     union
                        select substring (o.name + ' (' + user_name (o.uid) + ')', 1, 35),
                               convert (char (8), crdate, 11)
                        from sysobjects o
                        where o.uid            <> 1
                          and (o.sysstat & 15)  = 4
                          and not exists (select c.*
                                          from syscolumns c
                                          where c.id = o.id)
                     order by 'STORED PROCEDURE', 'DATE'
                  end
               else
                  begin
                     print '    THERE ARE NO PROCEDURES WITHOUT PARAMETERS.'
                  end

               print ' '

               select @spcount = count (o.uid)
               from sysobjects o
               where (o.sysstat & 15) = 4
                 and exists (select c.*
                             from syscolumns c
                             where c.id = o.id)

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

                     print '    THERE ARE %1! STORED PROCEDURES WITH PARAMETERS.',
                           @spcstring

                     print ' '

                     insert into #parameters
                        select o.name,
                               substring (c.name, 2, 29),
                               c.colid,
                               ' ',
                               c.usertype,
                               c.type,
                               c.length,
                               c.prec,
                               c.scale,
                               substring (o.name + ' ' + user_name (o.uid), 1, 50)
                        from syscolumns c,
                             sysobjects o
                        where o.uid            = 1
                          and (o.sysstat & 15) = 4
                          and o.id             = c.id
                          and 1                = (select count (o2.uid)
                                                  from sysobjects o2
                                                  where o2.name           = o.name
                                                    and (o2.sysstat & 15) = 4)

                     insert into #parameters
                        select substring (o.name + ' (' + user_name (o.uid) + ')', 1, 40),
                               substring (c.name, 2, 29),
                               c.colid,
                               ' ',
                               c.usertype,
                               c.type,
                               c.length,
                               c.prec,
                               c.scale,
                               substring (o.name + ' ' + user_name (o.uid), 1, 50)
                        from syscolumns c,
                             sysobjects o
                        where o.uid            = 1
                          and (o.sysstat & 15) = 4
                          and o.id             = c.id
                          and 1                < (select count (o2.uid)
                                                  from sysobjects o2
                                                  where o2.name           = o.name
                                                    and (o2.sysstat & 15) = 4)

                     insert into #parameters
                        select substring (o.name + ' (' + user_name (o.uid) + ')', 1, 40),
                               substring (c.name, 2, 29),
                               c.colid,
                               ' ',
                               c.usertype,
                               c.type,
                               c.length,
                               c.prec,
                               c.scale,
                               substring (o.name + ' ' + user_name (o.uid), 1, 50)
                        from syscolumns c,
                             sysobjects o
                        where o.uid            <> 1
                          and (o.sysstat & 15)  = 4
                          and o.id              = c.id

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

                     update #parameters
                     set typname = t.name
                     from systypes t
                     where #parameters.utype in (5, 6, 7, 11, 12, 21, 22)
                       and #parameters.utype  = t.usertype

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

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

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

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

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

                     update #parameters
                     set typname = 'double precision'
                     where utype  = 8
                       and len    = 8
                       and prec  IS NULL

                     update #parameters
                     set spname = ' '
                     where colid > 1

                     select 'STORED PROCEDURE' = substring (spname, 1, 35),
                            'PARAMETER'        = substring (pname, 1, 20),
                            'DATATYPE'         = typname
                     from #parameters
                     order by sortfield, colid
                  end
               else
                  begin
                     print '    THERE ARE NO STORED PROCEDURES WITH PARAMETERS.'

                     print ' '
                  end
            end
         else
            begin
               print '    THERE ARE NO STORED PROCEDURES.'

               print ' '
            end
      end
   else
      begin
         select @dotpstn = charindex ('.', @PROCEDURE)

         select @id  = id
         from sysobjects
         where id             = object_id (@PROCEDURE)
           and (sysstat & 15) = 4

         if ((@dotpstn > 0)  and
             (charindex ('.', substring (@PROCEDURE, 1 + @dotpstn, 63)) > 0))
            begin
               print '    A DATABASE CANNOT BE SPECIFIED FOR THE PROCEDURE ARGUMENT.'

               print ' '
            end
         else if (@id IS NOT NULL)
            begin
               if (exists (select *
                           from syscolumns
                           where id = @id))
                  begin
                     insert into #parameters
                        select ' ', substring (c.name, 2, 29), c.colid, t.name,
                               c.usertype, c.type, c.length, c.prec, c.scale, ' '
                        from syscolumns c,
                             systypes t
                        where c.id       = @id
                          and t.usertype = c.usertype
                  end
               else
                  begin
                     print '    PROCEDURE <%1!> HAS NO PARAMETERS.', @PROCEDURE

                     print ' '
                  end
            end
         else if ((@dotpstn = 0)  and
                  (exists (select *
                           from sysobjects
                           where name           = @PROCEDURE
                             and (sysstat & 15) = 4)))
            begin
               select @uidcount = count (id)
               from sysobjects
               where name           = @PROCEDURE
                 and (sysstat & 15) = 4

               if (@uidcount = 1)
                  begin
                     select @id    = id,
                            @owner = user_name (uid)
                     from sysobjects
                     where name           = @PROCEDURE
                       and (sysstat & 15) = 4

                     print '    PROCEDURE <%1!> IS OWNED BY %2!.',
                           @PROCEDURE, @owner

                     print ' '

                     if (exists (select *
                                 from syscolumns
                                 where id = @id))
                        begin
                           insert into #parameters
                              select ' ', substring (c.name, 2, 29), c.colid,
                                     t.name, c.usertype, c.type, c.length,
                                     c.prec, c.scale, ' '
                              from syscolumns c,
                                   systypes t
                              where c.id       = @id
                                and t.usertype = c.usertype
                        end
                     else
                        begin
                           print '    PROCEDURE <%1!> HAS NO PARAMETERS.', @PROCEDURE

                           print ' '
                        end
                  end
               else
                  begin
                     print '    STORED PROCEDURE <%1!> IS NOT UNIQUE.  QUALIFY WITH THE OWNER NAME.',
                           @PROCEDURE

                     print ' '

                     select OWNERS = user_name (uid)
                     from sysobjects
                     where name           = @PROCEDURE
                       and (sysstat & 15) = 4
                  end
            end
         else if ((@PROCEDURE like 'sp[_]%')  and  (db_name () <> 'sybsystemprocs'))
            begin
               print '    STORED PROCEDURE <%1!> IS BEING CHECKED IN sybsystemprocs.',
                     @PROCEDURE

               print ' '

               select @id = id
               from sybsystemprocs.dbo.sysobjects
               where name           = @PROCEDURE
                 and uid            = 1
                 and (sysstat & 15) = 4

               if (@id IS NULL)
                  begin
                     print '    STORED PROCEDURE <%1!> DOES NOT EXIST IN EITHER DATABASE.',
                           @PROCEDURE

                     print ' '
                  end
               else
                  begin
                     if (exists (select *
                                 from sybsystemprocs.dbo.syscolumns c
                                 where id = @id))
                        begin
                           insert into #parameters
                              select ' ', substring (c.name, 2, 29), c.colid,
                                     t.name, c.usertype, c.type, c.length,
                                     c.prec, c.scale, ' '
                              from sybsystemprocs.dbo.syscolumns c,
                                   sybsystemprocs.dbo.systypes t
                              where c.id       = @id
                                and t.usertype = c.usertype
                        end
                     else
                        begin
                           print '    STORED PROCEDURE <%1!> HAS NO PARAMETERS.', @PROCEDURE

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

               print ' '
            end

         if (exists (select *
                     from #parameters))
            begin
               update #parameters
               set typname = t.name
               from systypes t
               where #parameters.utype in (5, 6, 7, 11, 12, 21, 22)
                 and #parameters.utype  = t.usertype

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

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

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

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

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

               update #parameters
               set typname = 'double precision'
               where utype  = 8
                 and len    = 8
                 and prec  IS NULL

               select 'STORED PROCEDURE' = substring (@PROCEDURE, 1, 25),
                      'PARAMETER'        = substring (pname, 1, 20),
                      'DATATYPE'         = typname
               from #parameters
               order by colid
            end
      end
go
sp_addcomment sp_parameters, 14, 'Lists parameters for PROCEDURE or (default) all procedures'
go
sp_procxmode sp_parameters, anymode
go

------------ Temporary table needed for creation of sp_procs_fillprintname ---

create table #procs
  (id          int           not null,
   uid         int           not null,
   createdt    datetime      not null,
   mode        tinyint       not null,
   recompile   tinyint       not null,
   xp          tinyint       not null,
   procname    varchar (32)  not null,
   printname   varchar (82)  not null,
   parameters  varchar (51)  not null)
go

create procedure sp_procs_fillprintname as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   --  'any', 'chained', and 'unchained' are mutually exclusive.
   --  'recompile' and 'xp' are mutually exclusive.
   --  'unchained' is the default interpretation hence is not filled in.

   update #procs
   set printname = substring (printname + ' [any]', 1, 82)
   where mode      = 2
     and recompile = 0
     and xp        = 0

   update #procs
   set printname = substring (printname + ' [any, recompile]', 1, 82)
   where mode      = 2
     and recompile = 1

   update #procs
   set printname = substring (printname + ' [any, xp]', 1, 82)
   where mode = 2
     and xp   = 1

   update #procs
   set printname = substring (printname + ' [chained]', 1, 82)
   where mode      = 1
     and recompile = 0
     and xp        = 0

   update #procs
   set printname = substring (printname + ' [chained, recompile]', 1, 82)
   where mode      = 1
     and recompile = 1

   update #procs
   set printname = substring (printname + ' [chained, xp]', 1, 82)
   where mode = 1
     and xp   = 1

   update #procs
   set printname = substring (printname + ' [recompile]', 1, 82)
   where mode      = 0
     and recompile = 1

   update #procs
   set printname = substring (printname + ' [xp]', 1, 82)
   where mode = 0
     and xp   = 1
go
sp_procxmode sp_procs_fillprintname, unchained
go

drop table #procs
go

create procedure sp_procs (@PREFIX varchar (30) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   --   Note that if "any", "chained", etc. are specified then that text
   --   should NOT be in the "[...]" suffix of the procedure name.

   print ' '

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

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

         print ' '

         return 1
      end

   declare @continue    smallint,
           @format_lks  varchar (11),
           @format_pgs  varchar (11),
           @i           tinyint,
           @likename    varchar (31),
           @maxproclen  tinyint,
           @procpgs     int,
           @syslocks    int

   select @continue = 0,
          @i        = 1,
          @procpgs  = used_pgs (id, doampg, ioampg)
   from sysindexes
   where id = object_id ('sysprocedures')

   select @syslocks = value
   from master.dbo.syscurconfigs
   where config = 106

   create table #procs
     (id          int           not null,
      uid         int           not null,
      createdt    datetime      not null,
      mode        tinyint       not null,
      recompile   tinyint       not null,
      xp          tinyint       not null,
      procname    varchar (32)  not null,
      printname   varchar (82)  not null,
      parameters  varchar (51)  not null)

   if ((@@isolation > 0)  and  ((10 * @syslocks) < (12 * @procpgs)))
      begin
         select @format_lks = ltrim (substring (convert (char (14), convert (money, @syslocks), 1), 1, 11)),
                @format_pgs = ltrim (substring (convert (char (14), convert (money, @procpgs), 1), 1, 11))

         print '    THERE ARE %1! SYSTEM LOCKS AND %2! SYSPROCEDURES PAGES.',
               @format_lks, @format_pgs

         print ' '

         print '    RERUN AFTER FIRST SETTING TRANSACTION ISOLATION LEVEL TO 0.'

         print ' '

         return
      end
   else if (@PREFIX IS NULL)
      begin
         select @likename = '%'
      end
   else if (upper (@PREFIX) in ('ANY', 'CHAINED', 'DATE', 'RECOMPILE',
                                'UNCHAINED', 'XP'))
      begin
         select @likename = '%'
      end
   else
      begin
         select @likename = @PREFIX + '%'
      end

   if (exists (select *
               from sysobjects
               where (sysstat & 15) = 4))
      begin
         insert into #procs
            select o.id, o.uid, o.crdate, 0, 0, 0, o.name, o.name,
                   substring (substring (cl1.name, 2, 29)
                              + ', ' + substring (cl2.name, 2, 29)
                              + ', ' + substring (cl3.name, 2, 29)
                              + ', ' + substring (cl4.name, 2, 29)
                              + ', ' + substring (cl5.name, 2, 29)
                              + ', ' + substring (cl6.name, 2, 29),
                              1,
                              51)
            from syscolumns cl1,
                 syscolumns cl2,
                 syscolumns cl3,
                 syscolumns cl4,
                 syscolumns cl5,
                 syscolumns cl6,
                 sysobjects o
            where (o.sysstat & 15)    = 4
              and o.name           like @likename
              and o.id               *= cl1.id
              and cl1.colid           = 1
              and o.id               *= cl2.id
              and cl2.colid           = 2
              and o.id               *= cl3.id
              and cl3.colid           = 3
              and o.id               *= cl4.id
              and cl4.colid           = 4
              and o.id               *= cl5.id
              and cl5.colid           = 5
              and o.id               *= cl6.id
              and cl6.colid           = 6

         update #procs
         set parameters = ' '
         where parameters like ',%'

         update #procs
         set parameters = substring (parameters + ', ...', 1, 51)
         from syscolumns c,
              #procs p
         where c.id    = p.id
           and c.colid = 6

         while (@i < 6)
            begin
               update #procs
               set parameters = rtrim (substring (parameters, 1, char_length (parameters) - 1))
               where right (parameters, 1) = ','

               select @i = @i + 1
            end

         update #procs
         set printname = p.printname + ' (' + user_name (p.uid) + ')'
         from #procs p
         where p.uid <> 1
            or 1      < (select count (p2.uid)
                         from #procs p2
                         where p2.procname = p.procname)

         update #procs
         set mode = 1
         from sysobjects o,
              #procs p
         where p.id              = o.id
           and (o.sysstat2 & 16) = 16

         update #procs
         set mode = 2
         from sysobjects o,
              #procs p
         where p.id              = o.id
           and (o.sysstat2 & 32) = 32

         update #procs
         set recompile = 1
         from #procs p,
              sysprocedures s
         where s.id            = p.id
           and s.sequence      = 1
           and (s.status & 64) = 64

         update #procs
         set xp = 1
         from #procs p,
              sysobjects o
         where o.id   = p.id
           and o.type = 'XP'

         delete from #procs
         where upper (@PREFIX)  = 'ANY'
           and mode            <> 2

         delete from #procs
         where upper (@PREFIX)  = 'CHAINED'
           and mode            <> 1

         delete from #procs
         where upper (@PREFIX)  = 'UNCHAINED'
           and mode            <> 0

         delete from #procs
         where upper (@PREFIX)  = 'RECOMPILE'
           and recompile       <> 1

         delete from #procs
         where upper (@PREFIX)  = 'XP'
           and xp              <> 1

         select @continue = count (*)
         from #procs

         if (upper (@PREFIX) in ('ANY', 'CHAINED', 'UNCHAINED'))
            begin
               if (@continue > 0)
                  begin
                     update #procs
                     set printname = substring (printname + ' [recompile]', 1, 82)
                     where recompile = 1
                       and xp        = 0

                     update #procs
                     set printname = substring (printname + ' [xp]', 1, 82)
                     where recompile = 0
                       and xp        = 1
                  end
               else if (upper (@PREFIX) = 'ANY')
                  begin
                     print '    THERE ARE NO STORED PROCEDURES THAT USE ANY MODE.'

                     print ' '
                  end
               else if (upper (@PREFIX) = 'CHAINED')
                  begin
                     print '    THERE ARE NO STORED PROCEDURES THAT USE CHAINED MODE.'

                     print ' '
                  end
               else
                  begin
                     print '    THERE ARE NO STORED PROCEDURES THAT USE UNCHAINED MODE.'

                     print ' '
                  end
            end
         else if (upper (@PREFIX) = 'DATE')
            begin
               if (@continue > 0)
                  begin
                     execute sp_procs_fillprintname

                     select @maxproclen = max (char_length (printname))
                     from #procs

                     if (@maxproclen < 25)
                        begin
                           select 'DATE/TIME'  = convert (char (8), createdt, 11)
                                                 + ' ' + substring (convert (char (8), createdt, 8), 1, 5),
                                  'PROCEDURE'  = substring (printname, 1, 24),
                                  'PARAMETERS' = substring (parameters, 1, 37)
                           from #procs p
                           order by 'DATE/TIME', 'PROCEDURE'
                        end
                     else
                        begin
                           select 'DATE/TIME'  = convert (char (8), createdt, 11)
                                                 + ' ' + substring (convert (char (8), createdt, 8), 1, 5),
                                  'PROCEDURE'  = substring (printname, 1, 32),
                                  'PARAMETERS' = substring (parameters, 1, 29)
                           from #procs p
                           order by 'DATE/TIME', 'PROCEDURE'
                        end

                     select @continue = 0
                  end
               else
                  begin
                     print '    THERE ARE NO STORED PROCEDURES.'

                     print ' '
                  end
            end
         else if (upper (@PREFIX) in ('RECOMPILE', 'XP'))
            begin
               if (@continue > 0)
                  begin
                     update #procs
                     set printname = substring (printname + ' [any]', 1, 82)
                     where mode = 2

                     update #procs
                     set printname = substring (printname + ' [chained]', 1, 82)
                     where mode = 1
                  end
               else if (upper (@PREFIX) = 'RECOMPILE')
                  begin
                     print '    THERE ARE NO STORED PROCEDURES WITH THE RECOMPILE OPTION.'

                     print ' '
                  end
               else
                  begin
                     print '    THERE ARE NO EXTENDED STORED PROCEDURES.'

                     print ' '
                  end
            end
         else
            begin
               if (@continue > 0)
                  begin
                     execute sp_procs_fillprintname
                  end
               else
                  begin
                     print '    THERE ARE NO STORED PROCEDURES THAT BEGIN WITH <%1!>.',
                           @PREFIX

                     print ' '
                  end
            end

         if (@continue > 0)
            begin
               select @maxproclen = max (char_length (printname))
               from #procs

               if (@maxproclen < 17)
                  begin
                     select 'DATE'       = convert (char (8), createdt, 11),
                            'PROCEDURE'  = substring (printname, 1, 16),
                            'PARAMETERS' = parameters
                     from #procs
                     order by printname
                  end
               else if (@maxproclen < 25)
                  begin
                     select 'DATE'       = convert (char (8), createdt, 11),
                            'PROCEDURE'  = substring (printname, 1, 24),
                            'PARAMETERS' = substring (parameters, 1, 43)
                     from #procs
                     order by printname
                  end
               else
                  begin
                     select 'DATE'       = convert (char (8), createdt, 11),
                            'PROCEDURE'  = substring (printname, 1, 32),
                            'PARAMETERS' = substring (parameters, 1, 35)
                     from #procs
                     order by printname
                  end
            end
      end
   else
      begin
         print '    THERE ARE NO STORED PROCEDURES.'

         print ' '
      end
go
sp_addcomment sp_procs, 14, 'Lists stored procedures with various scope/sort options:'
go
sp_addcomment sp_procs, 14, '   (NULL):      all stored procedures in name order (default)'
go
sp_addcomment sp_procs, 14, '   "ANY":       only any mode stored procedures in name order'
go
sp_addcomment sp_procs, 14, '   "CHAINED":   only chained mode stored procedures in name order'
go
sp_addcomment sp_procs, 14, '   "DATE":      all stored procedures in date order'
go
sp_addcomment sp_procs, 14, '   PREFIX:      only stored procedures like PREFIX% in name order'
go
sp_addcomment sp_procs, 14, '   "RECOMPILE": only stored procedures with recompile option in name order'
go
sp_addcomment sp_procs, 14, '   "UNCHAINED": only unchained mode stored procedures in name order'
go
sp_addcomment sp_procs, 14, '   "XP":        only extended stored procedures in name order'
go
sp_procxmode sp_procs, anymode
go

create procedure sp_rules_all (@DATE_FLAG  char (1)) as
   set ansinull                      on
   set string_rtruncation            on

   declare @CR         char (1),
           @HT         char (1),
           @LF         char (1),
           @atsymbol   char (1),
           @maxtablen  tinyint,
           @seq        tinyint,
           @txtwidth   tinyint

   select @CR       = char (13),                      -- Carriage Return
          @HT       = char (9),                       -- Horizontal Tab
          @LF       = char (10),                      -- Line Feed
          @atsymbol = char (64),                      -- '@'
          @seq      = 1

   create table #rules
      (rname     varchar (62)   not null,
       uid       int            not null,
       crdate    datetime       not null,
       dateprnt  char (14)      not null,
       colid     tinyint        not null,
       seq       tinyint        not null,
       ruletxt   varchar (255)  not null,
       sort      varchar (255)  not null)

   insert into #rules
      select o.name, o.uid, o.crdate, ' ', c.colid, 1, c.text, ' '
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and o.id               = c.id
        and c.texttype         = 0

   update #rules
   set rname = substring (r.rname + ' (' + user_name (r.uid) + ')', 1, 62)
   from #rules r
   where r.uid <> 1
      or 1      < (select count (o.uid)
                   from sysobjects o,
                        sysprocedures p
                   where o.name             = r.rname
                     and (o.sysstat & 15)   = 7
                     and o.id               = p.id
                     and p.sequence         = 1
                     and (p.status & 4096) <> 4096)

   update #rules
   set ruletxt = substring (ruletxt, charindex ('(@', ruletxt), 255)
   where colid                     = 1
     and charindex ('(@', ruletxt) > 0

   update #rules
   set ruletxt = substring (ruletxt, charindex (@atsymbol, ruletxt), 255)
   where charindex ('(@', ruletxt) = 0
     and charindex (@atsymbol, ruletxt)  > 0

   while (exists (select *
                  from #rules
                  where charindex (@CR, ruletxt) > 0))
      begin
         update #rules
         set ruletxt = stuff (ruletxt, charindex (@CR, ruletxt), 1, ' ')
         where charindex (@CR, ruletxt) > 0
      end

   while (exists (select *
                  from #rules
                  where charindex (@HT, ruletxt) > 0))
      begin
         update #rules
         set ruletxt = stuff (ruletxt, charindex (@HT, ruletxt), 1, ' ')
         where charindex (@HT, ruletxt) > 0
      end

   while (exists (select *
                  from #rules
                  where charindex (@LF, ruletxt) > 0))
      begin
         update #rules
         set ruletxt = stuff (ruletxt, charindex (@LF, ruletxt), 1, ' ')
         where charindex (@LF, ruletxt) > 0
      end

   update #rules
   set ruletxt = ltrim (ruletxt)

   select @maxtablen = max (char_length (rname))
   from #rules

   if (@DATE_FLAG IS NULL)
      begin
         if (@maxtablen > 28)
            begin
               select @txtwidth = 38
            end
         else if (@maxtablen > 18)
            begin
               select @txtwidth = 48
            end
         else
            begin
               select @txtwidth = 58
            end
      end
   else
      begin
         if (@maxtablen > 28)
            begin
               select @txtwidth = 23
            end
         else if (@maxtablen > 18)
            begin
               select @txtwidth = 33
            end
         else
            begin
               select @txtwidth = 43
            end
      end

   while (@seq <= (255 / @txtwidth))
      begin
         insert into #rules
            select rname,
                   uid,
                   crdate,
                   dateprnt,
                   colid,
                   (@seq + 1),
                   ltrim (substring (ruletxt, (@txtwidth + 1), 255)),
                   sort
            from #rules
            where seq                   = @seq
              and char_length (ruletxt) > @txtwidth

         select @seq = @seq + 1
      end

   if (@DATE_FLAG IS NULL)
      begin
         update #rules
         set sort = rname
                    +  ' '
                    +  convert (varchar (3), colid)
                    +  ' '
                    +  convert (varchar (3), seq)

         update #rules
         set rname = ' '
         where colid <> 1
            or seq   <> 1

         if (@maxtablen > 28)
            begin
               select 'RULE'      = substring (rname, 1, 38),
                      DESCRIPTION = substring (ruletxt, 1, 38)
               from #rules
               order by sort
            end
         else if (@maxtablen > 18)
            begin
               select 'RULE'      = substring (rname, 1, 28),
                      DESCRIPTION = substring (ruletxt, 1, 48)
               from #rules
               order by sort
            end
         else
            begin
               select 'RULE'      = substring (rname, 1, 18),
                      DESCRIPTION = substring (ruletxt, 1, 58)
               from #rules
               order by sort
            end
      end
   else
      begin
         update #rules
         set dateprnt = convert (char (8), crdate, 11)
                        +  ' '
                        +  convert (char (5), crdate, 8),
             sort     = convert (char (10), crdate, 102)
                        +  ' '
                        +  convert (char (8), crdate, 108)
                        +  ' '
                        +  rname
                        +  ' '
                        +  convert (varchar (3), colid)
                        +  ' '
                        +  convert (varchar (3), seq)

         update #rules
         set rname    = ' ',
             dateprnt = ' '
         where colid <> 1
            or seq   <> 1

         if (@maxtablen > 28)
            begin
               select CREATED     = dateprnt,
                      'RULE'      = substring (rname, 1, 38),
                      DESCRIPTION = substring (ruletxt, 1, 23)
               from #rules
               order by sort
            end
         else if (@maxtablen > 18)
            begin
               select CREATED     = dateprnt,
                      'RULE'      = substring (rname, 1, 28),
                      DESCRIPTION = substring (ruletxt, 1, 33)
               from #rules
               order by sort
            end
         else
            begin
               select CREATED     = dateprnt,
                      'RULE'      = substring (rname, 1, 18),
                      DESCRIPTION = substring (ruletxt, 1, 43)
               from #rules
               order by sort
            end
      end
go

create procedure sp_rules (@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_rules> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @dbname     varchar (30),
           @tableid    int

   if ((@TABLE IS NULL)  or  (upper (@TABLE) = 'DATE'))
      begin
         if (exists (select o.*
                     from sysobjects o,
                          sysprocedures p
                     where (o.sysstat & 15)   = 7
                       and o.id               = p.id
                       and p.sequence         = 1
                       and (p.status & 4096) <> 4096))
            begin
               if (@TABLE IS NULL)
                  begin
                     execute sp_rules_all NULL
                  end
               else
                  begin
                     execute sp_rules_all 'X'
                  end
            end
         else
            begin
               select @dbname = ltrim (db_name ())

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

               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 if (exists (select c.*
                          from syscolumns c,
                               sysprocedures p
                          where c.id               = @tableid
                            and c.domain           = p.id
                            and p.sequence         = 1
                            and (p.status & 4096) <> 4096))
            begin
               select 'COLUMN' = col_name (@tableid, c.colid),
                      'RULE'   = object_name (c.domain)
               from syscolumns c,
                    sysprocedures p
               where c.id               = @tableid
                 and c.domain           = p.id
                 and p.sequence         = 1
                 and (p.status & 4096) <> 4096
               order by c.colid
            end
         else
            begin
               print '    THERE ARE NO RULES ON TABLE <%1!>.', @TABLE

               print ' '
            end
      end
go
sp_addcomment sp_rules, 14, 'Lists rules with various scope/sort options:'
go
sp_addcomment sp_rules, 14, '   (NULL): all rules in name order (default)'
go
sp_addcomment sp_rules, 14, '   "DATE": all rules in date order'
go
sp_addcomment sp_rules, 14, '   TABLE:  only rules for columns in TABLE'
go
sp_procxmode sp_rules, anymode
go

create procedure sp_showtable_detail (@OBJECTID int) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @CR          char ( 1),
           @HT          char ( 1),
           @LF          char ( 1),
           @dfltmssg    char ( 6),
           @maxcolname  tinyint,
           @maxcrtext   tinyint,
           @maxdflt     tinyint,
           @maxtypetext tinyint,
           @nullflag    tinyint,
           @objectname  varchar (30)

   select @CR          = char (13),                   -- Carriage Return
          @HT          = char (9),                    -- Horizontal Tab
          @LF          = char (10),                   -- Line Feed
          @maxcolname  = 0,
          @maxcrtext   = 0,
          @maxdflt     = 0,
          @maxtypetext = 0,
          @nullflag    = 0,
          @objectname  = object_name (@OBJECTID)

   if (exists (select *
               from master.dbo.sysdatabases
               where dbid = db_id ()
                 and 8192 = (status & 8192)))
      begin
         select @dfltmssg = '(Null)'                  -- ANSI db assumed
      end
   else
      begin
         select @dfltmssg = '-'
      end

   create table #showtbldetail
     (column    varchar (30)  not null,
      ident     tinyint       not null,
      usertype  smallint      not null,
      stype     tinyint       not null,
      len       tinyint       not null,
      prec      tinyint       null,
      scale     tinyint       null,
      typetext  varchar (18)  not null,
      cdflt     int           not null,
      dflt      varchar (60)  null,
      nulltype  int           not null,
      cr        int           not null,
      crtype    char (1)      not null,
      crtext    varchar (255) null,
      seq       tinyint       not null)

   insert into #showtbldetail
      select c.name,
             convert (tinyint, convert (bit, (c.status & 128))),
             c.usertype,
             c.type,
             c.length,
             c.prec,
             c.scale,
             t.name,
             c.cdefault,
             @dfltmssg,
             convert (int, convert (bit, (c.status & 8))),
             c.domain,
             'C',
             NULL,
             c.colid
      from syscolumns c,
           systypes t
      where c.id       = @OBJECTID
        and c.usertype = t.usertype

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

   update #showtbldetail
   set typetext = typetext
                  + ' ('
                  + convert (varchar (3), len)
                  + ')'
   where usertype in (1, 2, 3, 4, 24, 25)

   update #showtbldetail
   set typetext = typetext
                  + ' ('
                  + convert (varchar (3), prec)
                  + ', '
                  + convert (varchar (3), scale)
                  + ')'
   where usertype in (10, 26)

   update #showtbldetail
   set typetext = 'float (' + convert (varchar (3), prec) + ')'
   where usertype in (8, 23)
     and prec     IS NOT NULL

   update #showtbldetail
   set typetext = 'real'
   where ((usertype = 23)  or  ((usertype = 8) and (len = 4)))
     and prec IS NULL

   update #showtbldetail
   set typetext = 'double precision'
   where usertype  = 8
     and len       = 8
     and prec     IS NULL

   update #showtbldetail
   set typetext = 'tinyint'
   where usertype in (5, 13)
     and len       = 1

   update #showtbldetail
   set typetext = 'smallint'
   where usertype in (6, 13)
     and len       = 2

   update #showtbldetail
   set typetext = 'int'
   where usertype in (7, 13)
     and len       = 4

   if (exists (select *
               from #showtbldetail
               where nulltype = 1))
      begin
         update #showtbldetail
         set typetext = typetext + ' *'
         where nulltype = 1

         select @nullflag = 1
      end

   update #showtbldetail
   set dflt = object_name (s.cdflt)
   from syscomments c,
        #showtbldetail s
   where s.cdflt    = c.id
     and c.colid    = 1
     and c.colid2   = 0
     and c.texttype = 0

   update #showtbldetail
   set dflt = ltrim (substring (c.text, 8, 60))
   from syscomments c,
        #showtbldetail s
   where s.cdflt    = c.id
     and c.colid    = 1
     and c.colid2   = 0
     and c.texttype = 0
     and object_name (s.cdflt) like
                      substring (@objectname, 1, 10)  +  '[_]'
                      +  substring (s.column, 1, 6)  +  '[_]%'

   while (exists (select *
                  from #showtbldetail
                  where charindex (@CR, dflt) > 0))
      begin
         update #showtbldetail
         set dflt = stuff (dflt, charindex (@CR, dflt), 1, ' ')
         where charindex (@CR, dflt) > 0
      end

   while (exists (select *
                  from #showtbldetail
                  where charindex (@HT, dflt) > 0))
      begin
         update #showtbldetail
         set dflt = stuff (dflt, charindex (@HT, dflt), 1, ' ')
         where charindex (@HT, dflt) > 0
      end

   while (exists (select *
                  from #showtbldetail
                  where charindex (@LF, dflt) > 0))
      begin
         update #showtbldetail
         set dflt = stuff (dflt, charindex (@LF, dflt), 1, ' ')
         where charindex (@LF, dflt) > 0
      end

   update #showtbldetail
   set dflt = '-'
   where dflt      = @dfltmssg
     and nulltype  = 0

   update #showtbldetail
   set dflt = ltrim (dflt)

   update #showtbldetail
   set dflt = '! ' + s.dflt
   from systypes t,
        #showtbldetail s
   where s.usertype  > 99
     and s.usertype  = t.usertype
     and s.cdflt    <> t.tdefault

   update #showtbldetail
   set dflt = '! ' + dflt + ' (Identity)'
   where ident  = 1
     and cdflt <> 0
     and dflt not like '!%'

   update #showtbldetail
   set dflt = '!' + dflt + ' (Identity)'
   where ident   = 1
     and cdflt  <> 0
     and dflt like '!%'

   update #showtbldetail
   set dflt = '(Identity)'
   where ident = 1
     and cdflt = 0

   if (exists (select *
               from #showtbldetail
               where ident  = 1
                 and cdflt <> 0))
      begin
         print '    WARNING: IDENTITY COLUMN HAS AN UNUSEABLE DEFAULT.'

         print ' '
      end

   if (exists (select *
               from #showtbldetail
               where ident = 0
                 and dflt  like '!%'))
      begin
         print '    WARNING: COLUMN AND USERTYPE DEFAULTS DIFFER.'

         print ' '
      end

   update #showtbldetail
   set crtype = 'R'
   from sysprocedures p,
        #showtbldetail t
   where p.id               = t.cr
     and p.sequence         = 1
     and (p.status & 4096) <> 4096

   update #showtbldetail
   set crtext = c.text
   from syscomments c,
        #showtbldetail t
   where t.crtype   = 'C'
     and c.id       = t.cr
     and c.colid    = 1
     and c.colid2   = 0
     and c.texttype = 0

   update #showtbldetail
   set crtext = substring (crtext, 1, char_length (crtext) - 1)
   where crtype    = 'C'
     and crtext like '%CHECK%)'

   update #showtbldetail
   set crtext = substring (c.text, charindex ('@', c.text) - 1, 255)
   from syscomments c,
        #showtbldetail t
   where t.crtype                = 'R'
     and c.id                    = t.cr
     and c.texttype              = 0
     and charindex ('@', c.text) > 0

   update #showtbldetail
   set crtext = substring (crtext, 2, 255)
   where crtype                    = 'R'
     and substring (crtext, 1, 1) <> '('

   -- BEGINING TEXT VARIES DEPENDING UPON create VERSUS alter.  (Bug # 123888.)

   update #showtbldetail
   set crtext = substring (crtext, charindex ('(', upper (crtext)) + 1, 255)
   where upper (crtext) like '%CHECK%'

   -- ELIMINATE THE COLUMN NAME ONLY IF THE COLUMN IS PROPERLY REFERENCED.

   update #showtbldetail
   set crtext = ltrim (substring (crtext, char_length (column) + 1, 255))
   where upper (crtext) like (upper (column)  +  ' %')

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

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

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

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

   select @maxcolname  = max (char_length (column)),
          @maxtypetext = max (char_length (typetext)),
          @maxdflt     = max (char_length (dflt)),
          @maxcrtext   = max (char_length (crtext))
   from #showtbldetail

   if (@maxcolname < 14)
      begin
         if (@nullflag = 0)
            begin
               if (@maxtypetext < 14)
                  begin
                     if (@maxdflt < 13)
                        begin
                           select COLUMN       = substring (column, 1, 13),
                                  TYPE         = substring (typetext, 1, 13),
                                  'DEFAULT'    = substring (dflt, 1, 12),
                                  'CHECK/RULE' = substring (crtext, 1, 36)
                           from #showtbldetail
                           order by seq
                        end
                     else
                        begin
                           select COLUMN       = substring (column, 1, 13),
                                  TYPE         = substring (typetext, 1, 13),
                                  'DEFAULT'    = substring (dflt, 1, 18),
                                  'CHECK/RULE' = substring (crtext, 1, 30)
                           from #showtbldetail
                           order by seq
                        end
                  end
               else
                  begin
                     if (@maxdflt < 13)
                        begin
                           select COLUMN       = substring (column, 1, 13),
                                  TYPE         = substring (typetext, 1, 18),
                                  'DEFAULT'    = substring (dflt, 1, 12),
                                  'CHECK/RULE' = substring (crtext, 1, 31)
                           from #showtbldetail
                           order by seq
                        end
                     else
                        begin
                           select COLUMN       = substring (column, 1, 13),
                                  TYPE         = substring (typetext, 1, 18),
                                  'DEFAULT'    = substring (dflt, 1, 18),
                                  'CHECK/RULE' = substring (crtext, 1, 25)
                           from #showtbldetail
                           order by seq
                        end
                  end
            end
         else
            begin
               if (@maxdflt < 13)
                  begin
                     select COLUMN              = substring (column, 1, 13),
                            'TYPE (*=NULLABLE)' = typetext,
                            'DEFAULT'           = substring (dflt, 1, 12),
                            'CHECK/RULE'        = substring (crtext, 1, 31)
                     from #showtbldetail
                     order by seq
                  end
               else
                  begin
                     select COLUMN              = substring (column, 1, 13),
                            'TYPE (*=NULLABLE)' = typetext,
                            'DEFAULT'           = substring (dflt, 1, 18),
                            'CHECK/RULE'        = substring (crtext, 1, 25)
                     from #showtbldetail
                     order by seq
                  end
            end
      end
   else if (@maxcolname < 19)
      begin
         if (@maxdflt < 13)
            begin
               select COLUMN              = substring (column, 1, 18),
                      'TYPE (*=NULLABLE)' = typetext,
                      'DEFAULT'           = substring (dflt, 1, 12),
                      'CHECK/RULE'        = substring (crtext, 1, 26)
               from #showtbldetail
               order by seq
            end
         else
            begin
               select COLUMN              = substring (column, 1, 18),
                      'TYPE (*=NULLABLE)' = typetext,
                      'DEFAULT'           = substring (dflt, 1, 18),
                      'CHECK/RULE'        = substring (crtext, 1, 20)
               from #showtbldetail
               order by seq
            end
      end
   else
      begin
         if (@maxdflt < 13)
            begin
               select COLUMN              = column,
                      'TYPE (*=NULLABLE)' = typetext,
                      'DEFAULT'           = substring (dflt, 1, 12),
                      'CHECK/RULE'        = substring (crtext, 1, 14)
               from #showtbldetail
               order by seq
            end
         else
            begin
               select COLUMN              = column,
                      'TYPE (*=NULLABLE)' = typetext,
                      'DEFAULT'           = substring (dflt, 1, 18),
                      'CHK/RULE'          = substring (crtext, 1, 8)
               from #showtbldetail
               order by seq
            end
      end
go
sp_procxmode sp_showtable_detail, unchained
go

create procedure sp_tablesx (@PREFIX     varchar (30) = NULL,
                             @SORT_FLAG  varchar (1)  = 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_tablesx> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @exclude1  varchar (7),
           @exclude2  varchar (7),
           @exclude3  varchar (7),
           @exclude4  varchar (7)

   create table #tablesx
      (id         int            not null,
       uid        smallint       not null,
       crdate     datetime       not null,
       tablename  varchar ( 33)  not null,
       datap      char ( 11)     not null,
       indexp     char ( 11)     not null,
       tuples     char ( 13)     not null,
       maxlen     char (  7)     not null,
       ownername  varchar ( 30)  not null,
       indexes    smallint       not null,
       printname  varchar (31)   not null)

   select @exclude1 = 'sys%',
          @exclude2 = 'pb%',
          @exclude3 = 'sr[_]%'

   if exists (select *
              from sysobjects
              where ((@PREFIX IS NULL)  or
                     (upper (@PREFIX) in ('DATE', 'OWNER', 'PAGES', 'ROWS', 'WIDTH')))
                and (sysstat & 15) = 3)
      begin
         insert into #tablesx
            select distinct o.id,
                            o.uid,
                            o.crdate,
                            o.name,
                            substring (convert (char (14),
                                                convert (money,
                                                         sum (data_pgs (i.id, i.doampg))),
                                                1),
                                       1,
                                       11),
                            substring (convert (char (14),
                                                convert (money,
                                                         sum (data_pgs (i.id, i.ioampg))),
                                                1),
                                       1,
                                       11),
                            substring (convert (char (16),
                                                convert (money,
                                                         rowcnt (i0.doampg)),
                                                1),
                                       1,
                                       13),
                            substring (convert (char (10),
                                                convert (money,
                                                         i0.maxlen),
                                                1),
                                       1,
                                       7),
                            substring (user_name (o.uid), 1, 25),
                            0,
                            ' '
            from sysindexes i,
                 sysindexes i0,
                 sysobjects o
            where (o.sysstat & 15) = 3
              and o.id             = i.id
              and o.id             = i0.id
              and i0.indid         < 2
            group by o.id
            having (o.sysstat & 15) = 3
               and o.id             = i.id
               and o.id             = i0.id
               and i0.indid         < 2

         delete from #tablesx
         where tablename like @exclude1
            or tablename like @exclude2
            or tablename like @exclude3
            or tablename like @exclude4

         execute sp_excld_prnt  'TABLES', @exclude1, @exclude2, @exclude3, @exclude4
      end
   else if (exists (select *
                    from sysobjects
                    where @PREFIX          IS NOT NULL
                      and name           like @PREFIX + '%'
                      and (sysstat & 15)   in (1, 3)))
      begin
         insert into #tablesx
            select distinct o.id,
                            o.uid,
                            o.crdate,
                            o.name,
                            substring (convert (char (14),
                                                convert (money,
                                                         sum (data_pgs (i.id, i.doampg))),
                                                1),
                                       1,
                                       11),
                            substring (convert (char (14),
                                                convert (money,
                                                         sum (data_pgs (i.id, i.ioampg))),
                                                1),
                                       1,
                                       11),
                            substring (convert (char (16),
                                                convert (money,
                                                         rowcnt (i0.doampg)),
                                                1),
                                       1,
                                       13),
                            substring (convert (char (10),
                                                convert (money,
                                                         i0.maxlen),
                                                1),
                                       1,
                                       7),
                            substring (user_name (o.uid), 1, 25),
                            0,
                            ' '
            from sysindexes i,
                 sysindexes i0,
                 sysobjects o
            where (o.sysstat & 15)   in (1, 3)
              and o.name           like @PREFIX + '%'
              and o.id                = i.id
              and o.id                = i0.id
              and i0.indid            < 2
            group by o.id
            having (o.sysstat & 15)   in (1, 3)
               and o.name           like @PREFIX + '%'
               and o.id                = i.id
               and o.id                = i0.id
               and i0.indid            < 2
      end
   else if (@PREFIX IS NULL)
      begin
         print '    THERE ARE NO USER TABLES.'

         print ' '
      end
   else
      begin
         print '    THERE ARE NO USER TABLES THAT BEGIN WITH <%1!>.', @PREFIX

         print ' '
      end

   if (exists (select *
               from #tablesx))
      begin
         update #tablesx
         set tablename = substring (t.tablename + ' (' + ownername + ')', 1, 33)
         from #tablesx t
         where uid <> 1
            or 1    < (select count (t2.uid)
                       from #tablesx t2
                       where t.tablename = t2.tablename)

         update #tablesx
         set tablename = substring (t.tablename + ' [' + a.char_value + ']', 1, 33)
         from #tablesx t,
              sysattributes a
         where a.class        = 3             -- buffer manager
           and a.attribute    = 0             -- cache bindings
           and a.object_type  = 'T'           -- table binding
           and t.id           = a.object

         update #tablesx
         set indexes = (select count (i0.id) - 1
                        from sysindexes i0
                        where i0.id    = t.id
                          and i0.indid < 254
                          and (exists (select ii0.*
                                       from sysindexes ii0
                                       where ii0.id    = t.id
                                         and ii0.indid = 0)))
         from #tablesx t

         update #tablesx
         set indexes = (select count (i1.id)
                        from sysindexes i1
                        where i1.id    = t.id
                          and i1.indid < 254
                          and (exists (select ii1.*
                                       from sysindexes ii1
                                       where ii1.id    = t.id
                                         and ii1.indid = 1)))
         from #tablesx t
         where (exists (select ii11.*
                        from sysindexes ii11
                        where ii11.id    = t.id
                          and ii11.indid = 1))

         if (@PREFIX IS NULL)
            begin
               if (@SORT_FLAG IS NULL)
                  begin
                     select 'TABLE'         = substring (tablename, 1, 23),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen,
                            INDEXES         = indexes
                     from #tablesx x
                     order by tablename
                  end
               else
                  begin
                     select 'TABLE'         = substring (tablename, 1, 23),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen,
                            INDEXES         = indexes
                     from #tablesx x
                     order by tablename desc
                  end
            end
         else if (upper (@PREFIX) = 'DATE')
            begin
               if (@SORT_FLAG IS NULL)
                  begin
                     select 'DATE'          = convert (char (8), crdate, 11),
                            'TABLE'         = substring (tablename, 1, 22),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen
                     from #tablesx x
                     order by crdate, tablename
                  end
               else
                  begin
                     select 'DATE'          = convert (char (8), crdate, 11),
                            'TABLE'         = substring (tablename, 1, 22),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen
                     from #tablesx x
                     order by crdate desc, tablename
                  end
            end
         else if (upper (@PREFIX) = 'OWNER')
            begin
               update #tablesx
               set tablename = substring (tablename, 1, charindex ('(', tablename) - 1)
               where charindex ('(', tablename) > 0
                 and charindex ('[', tablename) = 0

               update #tablesx
               set tablename = substring (tablename, 1, charindex ('(', tablename) - 1)
                               + substring (tablename, charindex ('[', tablename) - 1, 23)
               where charindex ('(', tablename) > 0
                 and charindex ('[', tablename) > 0

               update #tablesx
               set printname = substring (ownername + '.' + tablename, 1, 31)

               insert into #tablesx
                  select 0, 0, getdate (), ' ', ' ', ' ', ' ', ' ',
                         ownername, 0, ' '
                  from #tablesx
                  group by ownername

               delete from #tablesx
               where printname = ' '
                 and ownername = (select min (ownername)
                                  from #tablesx)

               if (@SORT_FLAG IS NULL)
                  begin
                     select 'TABLE'         = printname,
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen
                     from #tablesx x
                     order by ownername, tablename
                  end
               else
                  begin
                     select 'TABLE'         = printname,
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen
                     from #tablesx x
                     order by ownername desc, tablename
                  end
            end
         else if (upper (@PREFIX) = 'PAGES')
            begin
               if (@SORT_FLAG IS NULL)
                  begin
                     select 'TABLE'         = substring (tablename, 1, 23),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen,
                            INDEXES         = indexes
                     from #tablesx x
                     order by datap, indexp, tuples, tablename
                  end
               else
                  begin
                     select 'TABLE'         = substring (tablename, 1, 23),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen,
                            INDEXES         = indexes
                     from #tablesx x
                     order by datap desc, indexp desc, tuples desc, tablename
                  end
            end
         else if (upper (@PREFIX) = 'ROWS')
            begin
               if (@SORT_FLAG IS NULL)
                  begin
                     select 'TABLE'         = substring (tablename, 1, 23),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen,
                            INDEXES         = indexes
                     from #tablesx x
                     order by tuples, tablename
                  end
               else
                  begin
                     select 'TABLE'         = substring (tablename, 1, 23),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen,
                            INDEXES         = indexes
                     from #tablesx x
                     order by tuples desc, tablename
                  end
            end
         else if (upper (@PREFIX) = 'WIDTH')
            begin
               if (@SORT_FLAG IS NULL)
                  begin
                     select 'TABLE'         = substring (tablename, 1, 23),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen,
                            INDEXES         = indexes
                     from #tablesx
                     order by maxlen, tablename
                  end
               else
                  begin
                     select 'TABLE'         = substring (tablename, 1, 23),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen,
                            INDEXES         = indexes
                     from #tablesx
                     order by maxlen desc, tablename
                  end
            end
         else
            begin
               update #tablesx
               set indexp = '          0',
                   tuples = '          N/A'
               where id = 8

               if (@SORT_FLAG IS NULL)
                  begin
                     select 'TABLE'         = substring (tablename, 1, 23),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen,
                            INDEXES         = indexes
                     from #tablesx x
                     order by tablename
                  end
               else
                  begin
                     select 'TABLE'         = substring (tablename, 1, 23),
                            ' DATA PAGES'   = datap,
                            'INDEX PAGES'   = indexp,
                            '     APP ROWS' = tuples,
                            'MAX LEN'       = maxlen,
                            INDEXES         = indexes
                     from #tablesx x
                     order by tablename desc
                  end
            end
      end
go
sp_addcomment sp_tablesx, 14, 'Lists user tables with space data with several options:'
go
sp_addcomment sp_tablesx, 14, '   (NULL):      all user tables in table name order (default)'
go
sp_addcomment sp_tablesx, 14, '   "DATE":      all user tables in creation date order'
go
sp_addcomment sp_tablesx, 14, '   "OWNER":     all user tables in user / table name order'
go
sp_addcomment sp_tablesx, 14, '   "PAGES":     all user tables in number of pages order'
go
sp_addcomment sp_tablesx, 14, '   PREFIX:      only user tables like PREFIX% in table name order'
go
sp_addcomment sp_tablesx, 14, '   "ROWS":      all user tables in number of rows order'
go
sp_addcomment sp_tablesx, 14, '   "WIDTH":     all user tables in table width order'
go
sp_addcomment sp_tablesx, 14, '   "SORT FLAG": secondary option for descending sorts'
go
sp_procxmode sp_tablesx, anymode
go

create procedure sp_triggers (@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_triggers> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @allflag     tinyint,
           @existflag   tinyint,
           @maxtabname  tinyint,
           @tableid     int

   create table #triggers
      (tabuid    smallint      not null,
       tabname   varchar (63)  not null,
       deltrig   int           not null,
       delname   varchar (30)  not null,
       deldt     datetime      null,
       deldate   char (14)     not null,
       instrig   int           not null,
       insname   varchar (30)  not null,
       insdt     datetime      null,
       insdate   char (14)     not null,
--     seltrig   int           not null,
--     selname   varchar (30)  not null,
--     seldt     datetime      null,
--     seldate   char (14)     not null,
       updtrig   int           not null,
       updname   varchar (30)  not null,
       upddt     datetime      null,
       upddate   char (14)     not null)

   select @allflag   = 0,
          @existflag = 0

   if ((@TABLE IS NULL)  or  (upper (@TABLE) = 'DATE'))
      begin
         select @allflag = 1
      end
   else
      begin
         select @tableid = id
         from sysobjects
         where id              = object_id (@TABLE)
           and (sysstat & 15) in (1, 3)
      end

   insert into #triggers
      select o.uid, o.name,
             o.deltrig, isnull (object_name (o.deltrig), '-'), NULL, ' ',
             o.instrig, isnull (object_name (o.instrig), '-'), NULL, ' ',
--           o.seltrig, isnull (object_name (o.seltrig), '-'), NULL, ' ',
             o.updtrig, isnull (object_name (o.updtrig), '-'), NULL, ' '
      from sysobjects o
      where ((@allflag = 1)  or  (o.id = @tableid))
        and (o.sysstat & 15) in (1, 3)
        and (o.deltrig > 0  or  o.instrig > 0  or  o.updtrig > 0)

   if (exists (select *
               from #triggers))
      begin
         select @existflag = 1

         update #triggers
         set tabname = substring (t.tabname + ' (' + user_name (t.tabuid) + ')', 1, 63)
         from #triggers t
         where t.tabuid <> 1
            or 1         < (select count (o.uid)
                            from sysobjects o
                            where o.name            = t.tabname
                              and (o.sysstat & 15) in (1, 3))

         update #triggers
         set delname = substring (t.delname + ' (' + user_name (o.uid) + ')', 1, 30)
         from sysobjects o,
              #triggers t
         where o.id   = t.deltrig
           and o.uid <> t.tabuid

         update #triggers
         set insname = substring (t.insname + ' (' + user_name (o.uid) + ')', 1, 30)
         from sysobjects o,
              #triggers t
         where o.id   = t.instrig
           and o.uid <> t.tabuid

--       update #triggers
--       set selname = substring (t.selname + ' (' + user_name (o.uid) + ')', 1, 30)
--       from sysobjects o,
--            #triggers t
--       where o.id   = t.seltrig
--         and o.uid <> t.tabuid

         update #triggers
         set updname = substring (t.updname + ' (' + user_name (o.uid) + ')', 1, 30)
         from sysobjects o,
              #triggers t
         where o.id   = t.updtrig
           and o.uid <> t.tabuid

         update #triggers
         set deldt   = o.crdate,
             deldate = convert (char (8), o.crdate, 11)
                       + ' '
                       + substring (convert (char (8), o.crdate, 8), 1, 5)
         from sysobjects o
         where #triggers.deltrig > 0
           and #triggers.deltrig = o.id

         update #triggers
         set insdt   = o.crdate,
             insdate = convert (char (8), o.crdate, 11)
                       + ' '
                       + substring (convert (char (8), o.crdate, 8), 1, 5)
         from sysobjects o
         where #triggers.instrig > 0
           and #triggers.instrig = o.id

--       update #triggers
--       set seldt   = o.crdate,
--           seldate = convert (char (8), o.crdate, 11)
--                     + ' '
--                     + substring (convert (char (8), o.crdate, 8), 1, 5)
--       from sysobjects o
--       where #triggers.seltrig > 0
--         and #triggers.seltrig = o.id

         update #triggers
         set upddt   = o.crdate,
             upddate = convert (char (8), o.crdate, 11)
                       + ' '
                       + substring (convert (char (8), o.crdate, 8), 1, 5)
         from sysobjects o
         where #triggers.updtrig > 0
           and #triggers.updtrig = o.id
      end

   if ((@TABLE IS NULL)  and (@existflag = 1))
      begin
         select @maxtabname = max (char_length (tabname))
         from #triggers

         if (@maxtabname < 12)
            begin
               select 'TABLE'          = substring (tabname, 1, 11),
                      'DELETE TRIGGER' = substring (delname, 1, 21),
                      'INSERT TRIGGER' = substring (insname, 1, 21),
                      'UPDATE TRIGGER' = substring (updname, 1, 21)
               from #triggers
               order by tabname
            end
         else if (@maxtabname < 18)
            begin
               select 'TABLE'          = substring (tabname, 1, 17),
                      'DELETE TRIGGER' = substring (delname, 1, 19),
                      'INSERT TRIGGER' = substring (insname, 1, 19),
                      'UPDATE TRIGGER' = substring (updname, 1, 19)
               from #triggers
               order by tabname
            end
         else
            begin
               select 'TABLE'          = substring (tabname, 1, 23),
                      'DELETE TRIGGER' = substring (delname, 1, 17),
                      'INSERT TRIGGER' = substring (insname, 1, 17),
                      'UPDATE TRIGGER' = substring (updname, 1, 17)
               from #triggers
               order by tabname
            end
      end
   else if ((upper (@TABLE) = 'DATE')  and (@existflag = 1))
      begin
            select 'TRIGGER DATE' = space (14),
                   'TRIGGER NAME' = space (27),
                   'TABLE'        = space (27),
                   TYPE           = space (6)
            where 1 = 2
         union all
            select deldate,
                   substring (delname, 1, 27),
                   substring (tabname, 1, 27),
                   'DELETE'
            from #triggers
            where deltrig > 0
         union all
            select insdate,
                   substring (insname, 1, 27),
                   substring (tabname, 1, 27),
                   'INSERT'
            from #triggers
            where instrig > 0
--       union all
--          select seldate,
--                 substring (selname, 1, 27),
--                 substring (tabname, 1, 27),
--                 'SELECT'
--          from #triggers
--          where seltrig > 0
         union all
            select upddate,
                   substring (updname, 1, 27),
                   substring (tabname, 1, 27),
                   'UPDATE'
            from #triggers
            where updtrig > 0
         order by 'TRIGGER DATE', 'TRIGGER NAME', 'TABLE', TYPE
      end
   else if ((@allflag = 1)  and  (@existflag = 0))
      begin
         print '    THERE ARE NO TRIGGERS ON USER TABLES.'

         print ' '
      end
   else if ((@tableid IS NOT NULL)  and  (@existflag = 1))
      begin
            select TYPE           = space (6),
                   'TRIGGER NAME' = space (30),
                   'TRIGGER DATE' = space (14)
            where 1 = 2
         union all
            select 'DELETE', delname, deldate
            from #triggers
            where deltrig > 0
         union all
            select 'INSERT', insname, insdate
            from #triggers
            where instrig > 0
--      union all
--          select 'SELECT', selname, seldate
--          from #triggers
--          where seltrig > 0
         union all
            select 'UPDATE', updname, upddate
            from #triggers
            where updtrig > 0
         order by TYPE
      end
   else if ((@tableid IS NOT NULL)  and  (@existflag = 0))
      begin
         print '    THERE ARE NO TRIGGERS ON TABLE <%1!>.', @TABLE

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

         print ' '
      end
go
sp_addcomment sp_triggers, 14, 'Lists triggers with various scope/sort options:'
go
sp_addcomment sp_triggers, 14, '   (NULL): all triggers in table name order (default)'
go
sp_addcomment sp_triggers, 14, '   "DATE": all triggers in date order'
go
sp_addcomment sp_triggers, 14, '   TABLE:  only triggers for TABLE'
go
sp_procxmode sp_triggers, anymode
go

create procedure sp_usertypes 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_usertypes> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @btlen    tinyint,
           @dfltlen  tinyint,
           @rulelen  tinyint

   create table #usertypes
     (uttext    varchar (30)  not null,
      uid       smallint      not null,
      len       tinyint       not null,
      prec      tinyint       null,
      scale     tinyint       null,
      bttype    tinyint       not null,
      bttext    varchar (30)  not null,
      dflt      varchar (30)  not null,
      rul       varchar (30)  not null,
      variable  tinyint       not null,
      nulltype  tinyint       not null)

   insert into #usertypes
      select ut.name,
             ut.uid,
             ut.length,
             ut.prec,
             ut.scale,
             bt.type,
             bt.name,
             isnull (object_name (ut.tdefault), '-'),
             isnull (object_name (ut.domain), '-'),
             ut.variable,
             ut.allownulls
      from systypes ut,
           systypes bt
      where ut.usertype > 99
        and bt.usertype < 100
        and ut.type     = bt.type

   insert into #usertypes
      select ut.name,
             ut.uid,
             ut.length,
             ut.prec,
             ut.scale,
             bt.type,
             bt.name,
             isnull (object_name (ut.tdefault), '-'),
             isnull (object_name (ut.domain), '-'),
             ut.variable,
             ut.allownulls
      from systypes ut,
           systypes bt
      where ut.usertype = 80              -- timestamp
        and bt.usertype = 4               -- varbinary

   delete from #usertypes
   where bttext in ('nchar', 'nvarchar', 'timestamp')

   delete from #usertypes
   where bttext = 'sysname'
     and ((len <> 30)  or  (nulltype = 0))

   update #usertypes
   set uttext = substring (uttext + ' (' + user_name (uid) + ')', 1, 30)
   where uid <> 1

   update #usertypes
   set bttext = bttext + ' (' +  convert (varchar (3), len) + ')'
   where bttype in (37, 38, 39, 45, 47)

   update #usertypes
   set bttext = bttext + ' (' +  convert (varchar (3), prec)
                + ', ' +  convert (varchar (3), scale) + ')'
   where bttype in (55, 63, 106, 108)

   update #usertypes
   set bttext = 'float' + ' (' +  convert (varchar (3), prec) + ')'
   where bttype in (59, 62)
     and prec   IS NOT NULL

   update #usertypes
   set bttext = 'real'
   where bttype  = 59
     and prec   IS NULL

   update #usertypes
   set bttext = 'double precision'
   where bttype  = 62
     and prec   IS NULL

   update #usertypes
   set bttext = 'sysname'
   where bttype   = 39
     and len      = 30
     and variable = 0

   select @btlen   = max (char_length (bttext)),
          @dfltlen = max (char_length (dflt)),
          @rulelen = max (char_length (rul))
   from #usertypes

   if ((@dfltlen = 1)  and  (@rulelen = 1))
      begin
         select 'USER TYPE' = uttext,
                'BASE TYPE' = substring (bttext, 1, 16)
                              +  ' '
                              +  char (32 + (10 * nulltype))
         from #usertypes
         order by uttext
      end
   else if (@dfltlen = 1)
      begin
         select 'USER TYPE' = uttext,
                'BASE TYPE' = substring (bttext, 1, 16)
                              +  ' '
                              +  char (32 + (10 * nulltype)),
                'RULE'      = substring (rul, 1, 27)
         from #usertypes
         order by uttext
      end
   else if (@rulelen = 1)
      begin
         select 'USER TYPE' = uttext,
                'BASE TYPE' = substring (bttext, 1, 16)
                              +  ' '
                              +  char (32 + (10 * nulltype)),
                'DEFAULT'   = substring (dflt, 1, 27)
         from #usertypes
         order by uttext
      end
   else if (@btlen < 13)
      begin
         select 'USER TYPE' = substring (uttext, 1, 18),
                'BASE TYPE' = substring (bttext, 1, 12)
                              +  ' '
                              +  char (32 + (10 * nulltype)),
                'DEFAULT'   = substring (dflt, 1, 21),
                'RULE'      = substring (rul, 1, 21)
         from #usertypes
         order by uttext
      end
   else
      begin
         select 'USER TYPE' = substring (uttext, 1, 18),
                'BASE TYPE' = substring (bttext, 1, 16)
                              +  ' '
                              +  char (32 + (10 * nulltype)),
                'DEFAULT'   = substring (dflt, 1, 19),
                'RULE'      = substring (rul, 1, 19)
         from #usertypes
         order by uttext
      end

   print ' '

   if (1 < any (select count (bttype)
                from #usertypes
                group by bttype, variable, len, prec, scale, nulltype, dflt, rul))
      begin
         print '    THERE ARE MULTIPLE USER TYPES OF THE SAME UNIQUE BASE TYPE.'

         print ' '

         select 'USER TYPE' = t.uttext,
                'BASE TYPE' = substring (t.bttext, 1, 16)
                              +  ' '
                              +  char (32 + (10 * t.nulltype))
         from #usertypes t
         where 1 < (select count (t2.uttext)
                    from #usertypes t2
                    where t.bttype   = t2.bttype
                      and t.variable = t2.variable
                      and t.len      = t2.len
                      and t.nulltype = t2.nulltype
                      and t.dflt     = t2.dflt
                      and t.rul      = t2.rul)
         group by t.bttype, t.variable, t.len, t.prec, t.scale, t.nulltype, t.dflt, t.rul
         having 1 < (select count (t2.uttext)
                     from #usertypes t2
                     where t.bttype   = t2.bttype
                       and t.variable = t2.variable
                       and t.len      = t2.len
                       and t.nulltype = t2.nulltype
                       and t.dflt     = t2.dflt
                       and t.rul      = t2.rul)
         order by t.bttext, t.nulltype, t.uttext
      end
   else
      begin
         print '    ALL USER TYPES ARE OF UNIQUE BASE TYPE.'
      end
go
sp_addcomment sp_usertypes, 14, 'Lists all user defined types with their attributes'
go
sp_procxmode sp_usertypes, anymode
go

create procedure sp_views (@PARM 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_views> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @exclude1  varchar (7),
           @exclude2  varchar (7),
           @exclude3  varchar (7),
           @exclude4  varchar (7)

   create table #views
      (id        int           not null,
       uid       smallint      not null,
       name      varchar (62)  not null,
       crdate    datetime      not null,
       erows     int           not null,
       btables   tinyint       not null,
       bviews    tinyint       not null)

   select @exclude1 = 'sr[_]%'

   if exists (select *
              from sysobjects
              where (sysstat & 15) = 2)
      begin
         if (upper (@PARM) in ('DATE', 'SIZE'))
            begin
               insert into #views
                  select id, uid, name, crdate, 0, 0, 0
                  from sysobjects
                  where (sysstat & 15) = 2
            end
         else
            begin
               insert into #views
                  select id, uid, name, crdate, 0, 0, 0
                  from sysobjects
                  where name           like (@PARM + '%')
                    and (sysstat & 15)    = 2

               if (@PARM IS NULL)
                  begin
                     execute sp_excld_prnt  'VIEWS', @exclude1, @exclude2, @exclude3, @exclude4

                     delete from #views
                     where name like @exclude1
                        or name like @exclude2
                        or name like @exclude3
                        or name like @exclude4
                  end
            end

         update #views
         set name = substring (v.name  +  ' ('  +  user_name (v.uid)  +  ')', 1, 62)
         from #views v
         where v.uid <> 1
            or 1      < (select count (v2.uid)
                         from sysobjects v2
                         where v2.name = v.name)

         update #views
         set erows = (select count (p.id)
                      from sysprocedures p
                      where p.id = v.id)
         from #views v

         update #views
         set btables = (select count (d.depid)
                        from sysdepends d,
                             sysobjects o
                        where d.id              = v.id
                          and o.id              = d.depid
                          and (o.sysstat & 15) in (1, 3)),
             bviews  = (select count (d.depid)
                        from sysdepends d,
                             sysobjects o
                        where d.id             = v.id
                          and o.id             = d.depid
                          and (o.sysstat & 15) = 2)
         from #views v

         if (upper (@PARM) = 'DATE')
            begin
               print '                                                                  <-- BASE -->'

               select CREATED     = convert (char (8), crdate, 11)
                                    + ' '
                                    + substring (convert (char (8), crdate, 8),
                                                 1,
                                                 5),
                      'VIEW'      = substring (name, 1, 39),
                      'EXEC ROWS' = substring (convert (char (12),
                                                        convert (money,
                                                                 erows),
                                                        1),
                                               1,
                                               9),
                      'TABLES'    = btables,
                      'VIEWS'     = bviews
               from #views
               order by crdate, name, user_name (uid)
            end
         else if (upper (@PARM) = 'SIZE')
            begin
               print '                                                                  <-- BASE -->'

               select CREATED     = convert (char (8), crdate, 11)
                                    + ' '
                                    + substring (convert (char (8), crdate, 8),
                                                 1,
                                                 5),
                      'VIEW'      = substring (name, 1, 39),
                      'EXEC ROWS' = substring (convert (char (12),
                                                        convert (money,
                                                                 erows),
                                                        1),
                                               1,
                                               9),
                      'TABLES'    = btables,
                      'VIEWS'     = bviews
               from #views
               order by erows desc, name, user_name (uid)
            end
         else if (exists (select *
                          from #views))
            begin
               print '                                                                  <-- BASE -->'

               select CREATED     = convert (char (8), crdate, 11)
                                    + ' '
                                    + substring (convert (char (8), crdate, 8),
                                                 1,
                                                 5),
                      'VIEW'      = substring (name, 1, 39),
                      'EXEC ROWS' = substring (convert (char (12),
                                                        convert (money,
                                                                 erows),
                                                        1),
                                               1,
                                               9),
                      'TABLES'    = btables,
                      'VIEWS'     = bviews
               from #views
               order by name, user_name (uid)
            end
         else
            begin
               if (@PARM IS NULL)
                  begin
                     print '    THERE ARE NO VIEWS OTHER THAN POSSIBLY EXCLUDED VIEWS.'

                     print ' '
                  end
               else
                  begin
                     print '    THERE ARE NO VIEWS LIKE <%1!>.', @PARM

                     print ' '
                  end
            end
      end
   else
      begin
         print '    THERE ARE NO VIEWS.'

         print ' '
      end
go
sp_addcomment sp_views, 14, 'Lists all views with several options:'
go
sp_addcomment sp_views, 14, '   (NULL): all views in name order'
go
sp_addcomment sp_views, 14, '   "DATE": all views in date order'
go
sp_addcomment sp_views, 14, '   "SIZE": all views in size order'
go
sp_addcomment sp_views, 14, '   PREFIX: all views that begin with PREFIX'
go
sp_procxmode sp_views, anymode
go

dump transaction sybsystemprocs with truncate_only
go
