use sybsystemprocs
go

sp_dropprocedure sp_columnagg
go
sp_dropprocedure sp_grantsagg
go
sp_dropprocedure sp_indexagg
go
sp_dropprocedure sp_objectagg_statistics
go
sp_dropprocedure sp_objectaggbyuser
go
sp_dropprocedure sp_objectagggeneral
go
sp_dropprocedure sp_objectaggmaster
go
sp_dropprocedure sp_objectagguser
go
sp_dropprocedure sp_objectagg
go
sp_dropprocedure sp_tableagg
go
sp_dropprocedure sp_typeagg
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

   declare @cmdstring  varchar (255),
           @collen     tinyint,
           @typelen    tinyint

   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 (30)  not null,
      nulltype  int           not null)

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

   print ' '

   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

   if (exists (select *
               from #columnagg))
      begin
         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 @collen  = max (char_length (column)),
                @typelen = max (char_length (typetext))
         from #columnagg

         select @cmdstring = 'select distinct ''COLUMN NAME'' = substring (column, 1,'
                             +  convert (varchar (2), @collen)
                             +  '),'
                             +  '''DATATYPE'' = substring (typetext, 1,'
                             +  convert (varchar (2), @typelen)
                             +  '),'
                             +  '''COUNT'' = substring (convert (char (8), convert (money, count (*)), 1), 1, 5)'
                             +  ' from #columnagg'
                             +  ' group by column, typetext'
                             +  ' order by column, typetext'

         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO USER TABLES OR VIEWS.'

         print ' '
      end
go
sp_addcomment sp_columnagg, 'Displays user table/view column datatype aggregates'
go
sp_addcomment sp_columnagg, '   if NULL_FLAG is non-null also check by nullability'
go
sp_procxmode sp_columnagg, anymode
go

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

         print ' '

         return 1
      end

   declare @accesslen  tinyint,
           @cmdstring  varchar (255),
           @colcount   varchar ( 10),
           @objlen     tinyint,
           @ugrlen     tinyint

   create table #grntsagg
      (id           int            not null,
       ouid         smallint       not null,
       uid          smallint       not null,
       otype        varchar (16)   not null,
       objname      varchar (63)   not null,
       action       smallint       not null,
       protecttype  tinyint        not null,
       access       varchar (17)   not null,
       colid        smallint       not null,
       coltext      varchar (32)   not null,
       columns      varbinary (32) null,
       sort         varchar (75)   not null)

   if (exists (select *
               from sysprotects))
      begin
         if (exists (select *
                     from sysprotects
                     where action in (167, 198, 203, 207, 221, 222, 228, 233, 235, 236, 253)))
            begin
               print '    THERE ARE CREATE/DUMP/PROXY PROTECTIONS.'

               print ' '

               select distinct ACCESS = substring (vTP.name + ' ' + vTA.name, 1, 23),
                               'COUNT'= count (p.id)
               from sybsystemprocs.dbo.usptvalues vTA,
                    sybsystemprocs.dbo.usptvalues vTP,
                    sysprotects p
               where p.action   in (167, 198, 203, 207, 221, 222, 228, 233, 235, 236, 253)
                 and vTP.type    = 'TP'
                 and vTP.number  = p.protecttype
                 and vTA.type    = 'TA'
                 and vTA.number  = p.action
               group by p.action
               having p.action   in (167, 198, 203, 207, 221, 222, 228, 233, 235, 236, 253)
                  and vTP.type    = 'TP'
                  and vTP.number  = p.protecttype
                  and vTA.type    = 'TA'
                  and vTA.number  = p.action
               order by ACCESS
            end
         else
            begin
               print '    THERE ARE NO CREATE/DUMP/PROXY PROTECTIONS.'
            end

         print ' '

         --  COLUMN INDEPENDENT PERMISSIONS

         insert into #grntsagg
            select p.id,
                   o.uid,
                   p.uid,
                   substring (vO.name, 1, 16),
                   o.name,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 17),
                   0,
                   '-',
                   NULL,
                   ' '
            from sybsystemprocs.dbo.usptvalues vO,
                 sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where (o.sysstat & 15) in (1, 2, 3, 4)
              and p.id              = o.id
              and p.columns        IS NULL
              and vO.type           = 'O'
              and vO.number         = (o.sysstat & 15)
              and vTP.type          = 'TP'
              and vTP.number        = p.protecttype
              and vTA.type          = 'TA'
              and vTA.number        = p.action

         --  COLUMN LEVEL PERMISSIONS

         insert into #grntsagg
            select p.id,
                   o.uid,
                   p.uid,
                   substring (vO.name, 1, 16),
                   o.name,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 17),
                   vP.number,
                   substring (col_name (p.id, vP.number), 1, 16),
                   p.columns,
                   ' '
            from sybsystemprocs.dbo.usptvalues vO,
                 sybsystemprocs.dbo.usptvalues vP,
                 sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where (o.sysstat & 15)           in (1, 2, 3, 4)
              and p.id                        = o.id
              and vO.type                     = 'O'
              and vO.number                   = (o.sysstat & 15)
              and vTP.type                    = 'TP'
              and vTP.number                  = p.protecttype
              and vTA.type                    = 'TA'
              and vTA.number                  = p.action
              and vP.type                     = 'P'
              and col_name (p.id, vP.number) IS NOT NULL
              and (0x01 & convert (tinyint, substring (p.columns, 1, 1)))
                                              = 0
              and (vP.aux2 & convert (tinyint, substring (isnull (p.columns, 0x01), vP.aux1, 1)))
                                             <> 0

         --  COLUMN LEVEL PERMISSIONS FOR ALL COLUMNS OF AN OBJECT

         insert into #grntsagg
            select p.id,
                   o.uid,
                   p.uid,
                   substring (vO.name, 1, 16),
                   o.name,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 17),
                   vP.number,
                   substring (isnull (col_name (p.id, vP.number), '(all)'), 1, 16),
                   p.columns,
                   ' '
            from sybsystemprocs.dbo.usptvalues vO,
                 sybsystemprocs.dbo.usptvalues vP,
                 sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where (o.sysstat & 15)            in (1, 2, 3, 4)
              and p.id                         = o.id
              and vO.type                      = 'O'
              and vO.number                    = (o.sysstat & 15)
              and vTP.type                     = 'TP'
              and vTP.number                   = p.protecttype
              and vTA.type                     = 'TA'
              and vTA.number                   = p.action
              and vP.type                      = 'P'
              and substring (p.columns, 2, 1) IS NULL
              and (0xfe & convert (tinyint, substring (p.columns, 1, 1)))
                                               = 0
              and (vP.aux2 & convert (tinyint, substring (isnull (p.columns, 0xfe), vP.aux1, 1)))
                                              <> 0

         -- INSERT "All" FOR delete/insert/select/references/update
         -- 541 = select + update + references (ON COLUMNS)
         -- 932 = select + insert + delete + update + references (ON TABLES/VIEWS)

         insert into #grntsagg
            select distinct id,
                            ouid,
                            uid,
                            otype,
                            objname,
                            sum (action),
                            protecttype,
                            'Grant All',
                            0,
                            ' ',
                            columns,
                            ' '
            from #grntsagg
            group by id, uid, protecttype, columns

         delete from #grntsagg
         where action not in (541, 932)
           and access      = 'Grant All'

         delete #grntsagg
         from #grntsagg g
         where g.action <> 541
           and exists (select *
                       from #grntsagg g2
                       where g2.id          = g.id
                         and g2.uid         = g.uid
                         and g2.protecttype = g.protecttype
                         and g2.columns     = isnull (g.columns, 0x01)
                         and g2.action      = 541)

         delete #grntsagg
         from #grntsagg g
         where g.action <> 932
           and exists (select *
                       from #grntsagg g2
                       where g2.id          = g.id
                         and g2.uid         = g.uid
                         and g2.protecttype = g.protecttype
                         and g2.columns     = g.columns
                         and g2.action      = 932)

         update #grntsagg
         set access = 'Grant* All'
         where access      = 'Grant All'
           and protecttype = 0

         update #grntsagg
         set access = 'Revoke All'
         where access      = 'Grant All'
           and protecttype = 2

         update #grntsagg
         set sort = user_name (uid) + ' ' + otype + access

         if (exists (select *
                     from #grntsagg
                     where columns IS NULL
                        or columns  = 0x01))
            begin
               print '    THERE ARE PROTECTIONS ON PROCEDURE(S)/TABLE(S)/VIEW(S).'

               print ' '

               select distinct ugrname = substring (isnull (user_name (uid), '?'), 1, 28),
                               otype,
                               access,
                               C       = substring (convert (char (10), convert (money, count (id)), 1), 1, 7),
                               sort
               into #grntsagg2
               from #grntsagg
               where (columns IS NULL  or  columns  = 0x01)
               group by otype, uid, action, protecttype, sort
               having (columns IS NULL  or  columns  = 0x01)

               if (exists (select *
                           from #grntsagg2
                           where charindex ('*', access) > 0))
                  begin
                     print '    "*" INDICATES "WITH GRANT".'

                     print ' '
                  end

               update #grntsagg2
               set ugrname = ' ',
                   otype   = ' '
               from #grntsagg2 g
               where exists (select *
                             from #grntsagg2 g2
                             where g2.ugrname = g.ugrname
                               and g2.otype   = g.otype
                               and g2.access  = g.access
                               and g2.access  > (select min (g3.access)
                                                 from #grntsagg2 g3
                                                 where g3.ugrname = g2.ugrname
                                                   and g3.otype   = g2.otype))

               update #grntsagg2
               set ugrname = ' '
               from #grntsagg2 g
               where exists (select *
                             from #grntsagg2 g2
                             where g2.ugrname                 = g.ugrname
                               and g2.otype                   = g.otype
                               and g2.access                  = g.access
                               and g2.otype + ' ' + g2.access > (select min (g3.otype + ' ' + g3.access)
                                                                   from #grntsagg2 g3
                                                                   where g3.ugrname = g2.ugrname))

               select @ugrlen    = max (char_length (ugrname)),
                      @accesslen = max (char_length (access))
               from #grntsagg2

               select @cmdstring = 'select ''USER/GROUP/ROLE'' = substring (ugrname, 1,'
                                   +  convert (varchar (2), @ugrlen)
                                   +  '),'
                                   +  '''OBJECT TYPE'' = otype,'
                                   +  'ACCESS = substring (access, 1,'
                                   +  convert (varchar (2), @accesslen)
                                   +  '),'
                                   +  '''COUNT'' = C'
                                   +  ' from #grntsagg2'
                                   +  ' order by sort'

               execute  (@cmdstring)
            end
         else
            begin
               print '    THERE ARE NO PROTECTIONS ON PROCEDURES OR ENTIRE TABLE(S)/VIEW(S).'
            end

         print ' '

         select @colcount = substring (convert (char (8),
                                                convert (money,
                                                         count (distinct id)),
                                                1),
                                       1,
                                       5)
         from #grntsagg
         where columns IS NOT NULL
           and columns     <> 0x01

         select @colcount = ltrim (@colcount)

         if (@colcount <> '0')
            begin
               print ' '

               if (@colcount = '1')
                  begin
                     print '    THERE ARE COLUMN PROTECTIONS ON ONE OBJECT.'
                  end
               else
                  begin
                     print '    THERE ARE COLUMN PROTECTIONS ON %1! OBJECTS.', @colcount
                  end

               print ' '

               delete from #grntsagg
               where columns IS NULL
                  or columns  = 0x01

               update #grntsagg
               set objname = substring (g.objname + ' (' + user_name (g.ouid) + ')', 1, 63)
               from #grntsagg g
               where g.ouid <> 1
                  or 1       < (select count (o.uid)
                                from sysobjects o
                                where o.name = g.objname)

               select @objlen = max (char_length (objname))
               from #grntsagg

               select @cmdstring = 'select distinct ''TABLE/VIEW'' = substring (objname, 1,'
                                   +  convert (varchar (2), @objlen)
                                   +  ')'
                                   +  ' from #grntsagg'
                                   +  ' order by ''TABLE/VIEW'''

               execute  (@cmdstring)
            end
         else
            begin
               print '    THERE ARE NO PROTECTIONS ON ANY TABLE/VIEW COLUMNS.'
            end
      end
   else
      begin
         print '    THERE ARE NO PROTECTIONS.'
      end

   print ' '
go
sp_addcomment sp_grantsagg, 'Displays protection aggregates'
go
sp_procxmode sp_grantsagg, anymode
go

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

         print ' '

         return 1
      end

   declare @cmdstring  varchar (255),
           @sequence   int,
           @typelen    tinyint

   if (exists (select i.*
               from sysindexes i,
                    sysobjects o
               where i.indid          > 0
                 and i.indid          < 254
                 and i.id             = o.id
                 and (o.sysstat & 15) = 3))
      begin
         create table #indexagg1
            (istatus smallint     not null,       -- sysindexes.status
             DOclust tinyint      not null,       -- DataOnly Clustered?
             AP      tinyint      not null,       -- AllPages?
             DP      tinyint      not null,       -- DataPages?
             DR      tinyint      not null,       -- DataRows?
             pages   int          not null)

         create table #indexagg2
            (istatus smallint     not null,       -- sysindexes.status
             DOclust tinyint      not null,       -- DataOnly Clustered?
             AP      tinyint      not null,       -- AllPages?
             DP      tinyint      not null,       -- DataPages?
             DR      tinyint      not null,       -- DataRows?
             C       int          not null,       -- Count
             pages   int          not null,       -- Index pages
             typetxt varchar (45) null)

         print '    COUNT TABLE OF INDEX TYPES FOR USER TABLES.'

         print ' '

         insert into #indexagg1
            select (i.status & 2135),
                   convert (tinyint, (i.status2 & 512) / 512),
                   convert (tinyint, (o.sysstat2 & 8192) / 8192),
                   convert (tinyint, (o.sysstat2 & 16384) / 16384),
                   convert (tinyint, (o.sysstat2 & 32768) / 32768),
                   data_pgs (i.id, i.ioampg)
            from sysindexes i,
                 sysobjects o
            where (o.sysstat & 15) = 3
              and i.id             = o.id
              and i.indid          > 0
              and i.indid          < 254

         --
         -- KLUDGE FIX FOR IMPLICIT ALLPAGES LOCKING.
         --

         update #indexagg1
         set AP = 1
         where AP = 0
           and DP = 0
           and DR = 0

         insert into #indexagg2
            select distinct istatus, DOclust, AP, DP, DR, count (istatus), sum (pages), NULL
            from #indexagg1
            group by istatus, AP, DP, DR

         update #indexagg2
         set typetxt = 'AllPages'
         where AP = 1

         update #indexagg2
         set typetxt = 'DataPages'
         where DP = 1

         update #indexagg2
         set typetxt = 'DataRows'
         where DR = 1

         select @sequence = 1

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

               select @sequence = @sequence + 1
            end

         -- ELIMINATE THE REDUNDANT TEXT FOR A PRIMARY KEY

         update #indexagg2
         set typetxt = stuff (typetxt, charindex ('Primary Unique', typetxt), 14, 'Primary')
         where charindex ('Primary Unique', typetxt) > 0

         -- CORRECT THE NONCLUSTERED TEXT FOR A DATAONLY TABLE.

         update #indexagg2
         set typetxt = stuff (typetxt, charindex ('Nonclustered', typetxt), 12, 'Clustered')
         where charindex ('Nonclustered', typetxt) > 0
           and DOclust                         = 1

         select @typelen = max (char_length (typetxt))
         from #indexagg2

         select @cmdstring = 'select distinct QUANTITY = convert (smallint, sum (C)),'
                             +  'TYPE = substring (typetxt, 1,'
                             +  convert (varchar (2), @typelen)
                             +  '),'
                             +  '''INDEX PAGES'' = substring (convert (char (14), convert (money, pages), 1), 1, 11)'
                             +  ' from #indexagg2'
                             +  ' group by typetxt'
                             +  ' order by typetxt'

         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO INDEXES ON USER TABLES.'

         print ' '
      end
go
sp_addcomment sp_indexagg, 'Displays index aggregates for all user tables'
go
sp_procxmode sp_indexagg, anymode
go

create procedure sp_objectagg_statistics as
   set flushmessage                  on

   declare @now  char (8)

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

   print '    UPDATING INDEX STATISTICS ON SYSTEM TABLES.    (%1!)', @now

   update index statistics sysalternates

   update index statistics sysattributes

   update index statistics syscolumns

   update index statistics sysindexes

   update index statistics sysobjects

   update index statistics sysprocedures

   update index statistics sysprotects

   update index statistics sysreferences

   update index statistics sysroles

   update index statistics systypes

   update index statistics sysusermessages

   update index statistics sysusers
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 @cmdstring  varchar (255),
           @msglog     int,
           @now        char (8),
           @typelen    tinyint,
           @userlen    tinyint

   select @msglog = hextoint ('0x80'),
          @now    = convert (char (8), getdate (), 8)

   print '    COUNTING LOGS, 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
        and o.id      <> 8
      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
         and o.id      <> 8

   insert into #objectagg (owner, type, qty)
      select isnull (suser_name (u.suid), '?'), 'log', 1
      from sysobjects o,
           sysusers u
      where u.uid = o.uid
        and o.id  = 8

   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      <> 8               -- not the log
                   and object       < 100)            -- system table
   from #objectagg
   where type = 'system 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       = 8)              -- log
   from #objectagg
   where type = 'log'

   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 & @msglog) = @msglog
        and u.uid                = m.uid
      group by m.uid
      having (m.dlevel & @msglog) = @msglog
         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 USER TABLE COLUMNS                    (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select distinct isnull (suser_name (u.suid), '?'),
                      'user table column',
                      count (c.id)
      from syscolumns c,
           sysobjects o,
           sysusers u
      where (o.sysstat & 15) = 3
        and c.id             = o.id
        and u.uid            = o.uid
      group by o.uid
      having (o.sysstat & 15) = 3
         and c.id             = o.id
         and u.uid            = o.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)

   update #objectagg
   set owner = ' '
   from #objectagg o1
   where exists (select *
                 from #objectagg o2
                 where o2.owner = o1.owner
                   and o2.type  = o1.type
                   and o2.type  > (select min (o3.type)
                                   from #objectagg o3
                                   where o3.owner = o2.owner))


   select @typelen = max (char_length (type)),
          @userlen = max (char_length (owner))
   from #objectagg

   if (@typelen < 11)
      begin
         select @typelen = 11
      end

   if ((@userlen + @typelen) > 60)
      begin
         select @userlen = 60 - @typelen
      end

   select @cmdstring = 'select ''USER'' = substring (owner, 1,'
                       +  convert (varchar (2), @userlen)
                       +  '),'
                       +  '''OBJECT TYPE'' = substring (type, 1,'
                       +  convert (varchar (2), @typelen)
                       +  '),'
                       +  'QUANTITY = prntqty'

   if (exists (select *
               from #objectagg
               where cached IS NOT NULL
                 and cached <> '      '
                 and cached <> '     0'))
      begin
         select @cmdstring = @cmdstring
                             +  ', CACHED = isnull (cached, ''     -'')'
      end

   select @cmdstring = @cmdstring
                       +  ' from #objectagg'
                       +  ' order by sortorder'

   execute  (@cmdstring)
go
sp_existsprocedure sp_objectaggbyuser
go

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

   declare @cmdstring  varchar (255),
           @msglog     int,
           @now        char (8),
           @typelen    tinyint,
           @userlen    tinyint

   select @msglog = hextoint ('0x80'),
          @now    = convert (char (8), getdate (), 8)

   print '    COUNTING LOGS, 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 o.id      <> 8
        and vO.number  = convert (int, o.sysstat & 15)
        and vO.type    = 'O'
        and vO.number in (1, 2, 3)
      group by (o.sysstat & 15)
      having o.id      <> 8
         and vO.number  = convert (int, o.sysstat & 15)
         and vO.type    = 'O'
         and vO.number in (1, 2, 3)

   insert into #objectagg (owner, type, qty)
      select 'dbo', 'log', 1
      from sysobjects
      where id = 8

   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      <> 8             -- not the log
                     and object       < 100)          -- system table
   where type = 'system 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       = 8)            -- log
   where type = 'log'

   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 & @msglog) = @msglog

   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

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

   print '    COUNTING USER TABLE COLUMNS                    (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'user table column', count (c.id)
      from syscolumns c,
           sysobjects o
      where (o.sysstat & 15) = 3
        and c.id             = o.id

   select @now = convert (char (8), getdate (), 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 (guest)', 1
      from sysusers
      where suid = -1
        and uid  = 2

   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)

   select @typelen = max (char_length (type))
   from #objectagg

   select @cmdstring = 'select ''OBJECT TYPE'' = substring (type, 1,'
                       +  convert (varchar (2), @typelen)
                       +  '),'
                       +  'QUANTITY = prntqty'

   if (exists (select *
               from #objectagg
               where cached IS NOT NULL
                 and cached <> '     0'))
      begin
         select @cmdstring = @cmdstring
                             +  ', CACHED = isnull (cached, ''     -'')'
      end

   select @cmdstring = @cmdstring
                       +  ' from #objectagg'
                       +  ' order by type'

   execute  (@cmdstring)
go
sp_existsprocedure sp_objectagggeneral
go

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

   declare @msglog  int,
           @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 @msglog = hextoint ('0x80'),
          @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 'sa', 'logged system message (non-fatal)', count (error)
            from master.dbo.sysmessages
            where severity           < 19
              and (dlevel & @msglog) = @msglog

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

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

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

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

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

   declare @cmdstring  varchar (255),
           @msglog     int,
           @now        char (8),
           @typelen    tinyint

   select @msglog = hextoint ('0x80'),
          @now    = convert (char (8), getdate (), 8)

   print '    COUNTING LOGS, 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
        and o.id      <> 8
      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
         and o.id      <> 8

   insert into #objectagg (owner, type, qty)
      select ' ', 'log', count (id)
      from sysobjects
      where id  = 8
        and 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      <> 8             -- log
                   and a.object       < 100           -- system table
                   and o.id           = a.object
                   and o.uid          = @UID)
   where type = 'system 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       = 8             -- log
                   and o.id           = a.object
                   and o.uid          = @UID)
   where type = 'log'

   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 & @msglog) = @msglog

   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 USER TABLE COLUMNS                    (%1!)', @now

   insert into #objectagg (owner, type, qty)
      select ' ', 'user table column', count (c.id)
      from syscolumns c,
           sysobjects o
      where (o.sysstat & 15) = 3
        and o.uid            = @UID
        and c.id             = o.id

   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)

         select @typelen = max (char_length (type))
         from #objectagg

         select @cmdstring = 'select ''OBJECT TYPE'' = substring (type, 1,'
                             +  convert (varchar (2), @typelen)
                             +  '),'
                             +  'QUANTITY = prntqty'

         if (exists (select *
                     from #objectagg
                     where cached IS NOT NULL
                       and cached <> '     0'))
            begin
               select @cmdstring = @cmdstring
                                   +  ', CACHED = isnull (cached, ''     -'')'
            end

         select @cmdstring = @cmdstring
                             +  ' from #objectagg'
                             +  ' order by type'

         execute  (@cmdstring)
      end
   else
      begin
         print '    LOGIN %1! OWNS NO OBJECTS.', @LOGIN

         print ' '
      end
go
sp_existsprocedure sp_objectagguser
go

drop table #objectagg
go

create procedure sp_objectagg (@LOGIN varchar (30) = NULL) as
   --
   --  THE LOGIC IS A LITTLE BIT TRICKY SINCE A LOGIN CAN OWN A DATATABASE
   --  BUT MAY NOT BE A USER IN THE master DATABASE.  IN THIS CASE WE'LL
   --  SIMPLY PRINT AN INFORMATORY MESSAGE.  OTHERWISE . . . WE'LL DO MORE.
   --
   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 ' '

         print '    sp_objectagg CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @dbowner_cnt  smallint,
           @format_lks   varchar (11),
           @format_pgs   varchar (11),
           @procpgs      int,
           @syslocks     int,
           @uid          smallint,
           @stats_flag   tinyint

   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)

   if ((user_id () = 1)  or  (charindex ('sa_role', show_role ()) > 0))
      begin
         select @stats_flag = 1
      end
   else
      begin
         select @stats_flag = 0
      end

   select @uid = uid
   from sysusers
   where suid = suser_id (@LOGIN)

   select @dbowner_cnt = -1,
          @procpgs     = used_pgs (id, doampg, ioampg)
   from sysindexes
   where id = object_id ('sysprocedures')

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

   if (exists (select *
               from master.dbo.sysdatabases
               where dbid            = db_id ()
                 and (status & 1024) = 1024))
      begin
         select @stats_flag = 0
      end
   else 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 '    INDEX STATISTICS ON SYSTEM TABLES WILL NOT BE UPDATED.'

         print ' '

         print '    TRANSACTION ISOLATION LEVEL 0 WILL BE USED.'

         print ' '

         set transaction isolation level 0

         select @stats_flag = 0
      end

   if (db_id () = 1)
      begin
         select @dbowner_cnt = count (*)
         from master.dbo.sysdatabases
         where suid = suser_id (@LOGIN)

         if ((@uid IS NULL)  and  (@dbowner_cnt = 1))
            begin
               print '    LOGIN %1! OWNS 1 DATABASE AND OWNS NOTHING ELSE.',
                     @LOGIN

               print ' '
            end
         else if ((@uid IS NULL)  and  (@dbowner_cnt > 1))
            begin
               print '    LOGIN %1! OWNS %2! DATABASES AND OWNS NOTHING ELSE.',
                     @LOGIN, @dbowner_cnt

               print ' '
            end
         else
            begin
               execute  sp_objectaggmaster  @LOGIN=@LOGIN
            end
      end

   if (upper (@LOGIN) = 'BYUSER')
      begin
         if (@stats_flag = 1)
            begin
               execute  sp_objectagg_statistics
            end

         execute  sp_objectaggbyuser
      end
   else if (@LOGIN IS NULL)
      begin
         if (@stats_flag = 1)
            begin
               execute  sp_objectagg_statistics
            end

         execute  sp_objectagggeneral
      end
   else if (@uid IS NULL)
      begin
         print '    %1! IS NOT A VALID LOGIN.', @LOGIN

         print ' '
      end
   else if ((@uid IS NOT NULL)  or  (@dbowner_cnt = 0))
      begin
         if (@stats_flag = 1)
            begin
               execute  sp_objectagg_statistics
            end

         execute  sp_objectagguser  @UID=@uid, @LOGIN=@LOGIN
      end
go
sp_addcomment sp_objectagg, 'Displays object type aggregates (options: LOGIN or "BYUSER")'
go
sp_procxmode sp_objectagg, anymode
go

create procedure sp_tableagg  (@PREFIX varchar (30) = 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_tableagg CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @bitoff      bit,
           @biton       bit,
           @cmdstring   varchar (255),
           @hdr_string  varchar ( 78),
           @likestring  varchar ( 30),
           @retval      int,
           @tablen      tinyint

   create table #tableagg
      (tabid       int           not null,
       uid         smallint      not null,
       tabname     varchar (63)  not null,
       indexes     tinyint       not null,
       pkeys       tinyint       not null,
       pkeys_prnt  char (5)      not null,
       fkeys       tinyint       not null,
       fkeys_prnt  char (5)      not null,
       self        tinyint       not null,
       self_prnt   char (5)      not null,
       rcount      char (5)      not null,
       wcount      char (5)      not null,
       dpages      char (7)      not null)

   select @bitoff = convert (bit, 0),
          @retval = 0

   select @biton    = ~(@bitoff)

   if (@PREFIX IS NULL)
      begin
         select @likestring = '%'
      end
   else if (upper (@PREFIX) = 'READONLY')
      begin
         print '    ONLY READ-ONLY TABLES WILL BE LISTED.'

         print ' '

         select @likestring = '%'
      end
   else if (upper (@PREFIX) = 'SYS')
      begin
         print '    ONLY SYSTEM TABLES WILL BE LISTED.'

         print ' '

         select @likestring = '%'
      end
   else
      begin
         execute  sp_like_string  @INPUT_STRING=@PREFIX,
                                  @LIKE_STRING=@likestring  output

         print '    ONLY USER TABLES LIKE %1! WILL BE LISTED.', @likestring

         print ' '
      end

   if (upper (@PREFIX) = 'SYS')
      begin
         insert into #tableagg
            select id, uid, name, 0, 0, '0', 0, '0', 0, '0', '0', '0', '0'
            from sysobjects
            where (sysstat & 15) = 1
      end
   else
      begin
         if (exists (select *
                     from sysdepends
                     where object_name (depid) IS NULL))
            begin
               print '    DEPENDENCY COUNTS ARE NOT GUARANTEED.  EXECUTE sp_usesright.'

               print ' '

               select @retval = 1
            end

         insert into #tableagg
            select id, uid, name, 0, 0, '0', 0, '0', 0, '0', '0', '0', '0'
            from sysobjects
            where (sysstat & 15)    = 3
              and name           like @likestring

         update #tableagg
         set tabname = r.tabname + ' (' + user_name (r.uid) + ')'
         from #tableagg r
         where r.uid <> 1
            or 1      < (select count (o.uid)
                         from sysobjects o
                         where (o.sysstat & 15) = 3
                           and o.name           = r.tabname)
      end

   update #tableagg
   set indexes = (select count (i.indid)
                  from sysindexes i
                  where i.id    = ro.tabid
                    and i.indid > 0
                    and i.indid < 254),
       pkeys   = (select count (r.reftabid)
                  from sysreferences r
                  where r.pmrydbname IS NULL
                    and r.reftabid    = ro.tabid),
       fkeys   = (select count (r.tableid)
                  from sysreferences r
                  where r.frgndbname IS NULL
                    and r.tableid     = ro.tabid),
       self    = (select count (r.tableid)
                  from sysreferences r
                  where r.frgndbname IS NULL
                    and r.pmrydbname IS NULL
                    and r.tableid     = r.reftabid
                    and r.tableid     = ro.tabid),
       rcount  = (select substring (convert (char (8), convert (money, count (d.id)), 1), 1, 5)
                  from sysdepends d
                  where d.depid     = ro.tabid
                    and d.resultobj = @bitoff),
       wcount  = (select substring (convert (char (8), convert (money, count (d.id)), 1), 1, 5)
                  from sysdepends d
                  where d.depid     = ro.tabid
                    and d.resultobj = @biton),
       dpages  = (select substring (convert (char (10), convert (money, data_pgs (i.id, i.doampg)), 1), 1, 7)
                  from sysindexes i
                  where i.id    = ro.tabid
                    and i.indid < 2)
   from #tableagg ro

   if (upper (@PREFIX) = 'READONLY')
      begin
         delete from #tableagg
         where wcount <> '    0'
      end

   if (exists (select *
               from #tableagg))
      begin
         select @tablen = max (char_length (tabname))
         from #tableagg

         select @cmdstring = 'select''TABLE'' = substring (tabname, 1,'
                             +  convert (varchar (2), @tablen)
                             +  '),'
                             +  '''READ'' = rcount,'
                             +  'WRITE = wcount,'

         if (exists (select *
                     from #tableagg
                     where fkeys > 0))
            begin
               update #tableagg
               set pkeys = pkeys - self,
                   fkeys = fkeys - self

               update #tableagg
               set pkeys_prnt = substring (convert (char (8), convert (money, pkeys), 1), 1, 5),
                   fkeys_prnt = substring (convert (char (8), convert (money, fkeys), 1), 1, 5),
                   self_prnt  = substring (convert (char (8), convert (money, self), 1), 1, 5)

               if (@tablen > 31)
                  begin
                     select @tablen = 31
                  end

               select @hdr_string = space (@tablen + 2)
                                    +  'DEPENDENTS  <-- KEY USAGE -->          DATA'

               select @cmdstring = @cmdstring
                                   +  'PK = pkeys_prnt,'
                                   +  'FK = fkeys_prnt,'
                                   +  'SELF = self_prnt,'
            end
         else
            begin
               if (@tablen > 58)
                  begin
                     select @tablen = 58
                  end

               select @hdr_string = space (@tablen + 2)
                                    +  'DEPENDENTS           DATA'
            end

         select @cmdstring = @cmdstring
                             +  'INDEXES = indexes,'
                             +  'PAGES = dpages'
                             +  ' from #tableagg'
                             +  ' order by tabname'

         print '%1!', @hdr_string

         execute  (@cmdstring)

      end
   else if (@PREFIX IS NULL)
      begin
         print '    THERE ARE NO USER TABLES.'

         print ' '
      end
   else if (upper (@PREFIX) = 'READONLY')
      begin
         print '    THERE ARE NO READ-ONLY USER TABLES.'

         print ' '
      end
   else
      begin
         print '    THERE ARE NO USER TABLES LIKE %1!.', @likestring

         print ' '
      end

   return @retval
go
sp_addcomment sp_tableagg, 'Lists dependency, primary/foreign key, and index aggregate data'
go
sp_addcomment sp_tableagg, 'with options:'
go
sp_addcomment sp_tableagg, '   (NULL):     all user tables (default)'
go
sp_addcomment sp_tableagg, '   PREFIX:     only user tables like PREFIX%'
go
sp_addcomment sp_tableagg, '   "READONLY": only read-only user tables'
go
sp_addcomment sp_tableagg, '   "SYS":      all system tables only'
go
sp_procxmode sp_tableagg, anymode
go

create procedure sp_typeagg as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @cmdstring  varchar (255),
           @typelen    tinyint

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   if (exists (select *
               from #typeagg))
      begin
         update #typeagg
         set type = type  +  ' '  +  char (32 + (10 * nul))

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

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

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

         select @typelen = max (char_length (type))
         from #typeagg

         select @cmdstring = 'select TYPE = substring (type, 1,'
                             +  convert (varchar (2), @typelen)
                             +  '),'
                             +  'COLUMNS = substring (convert (char (10), convert (money, c)), 1, 7)'

         if (exists (select *
                     from #typeagg
                     where comment IS NOT NULL))
            begin
               select @cmdstring = @cmdstring
                                   +  ',COMMENT = isnull (comment, '' '')'
            end

         select @cmdstring = @cmdstring
                             +  ' from #typeagg'
                             +  ' order by substring (type, 1, 6), size1, size2, nul'

         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO USER TABLES.'

         print ' '
      end
go
sp_addcomment sp_typeagg, 'Displays column type aggregates for user tables'
go
sp_procxmode sp_typeagg, anymode
go
