use sybsystemprocs
go
drop procedure sp_execsizes
go
drop procedure sp_size
go
drop procedure sp_sizes
go
drop procedure sp_spacebyuser
go

set quoted_identifier on
go

create procedure sp_execsizes (@SIZE smallint) 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_execsizes> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @format_lks  varchar (11),
           @format_mr   varchar (6),
           @format_pgs  varchar (11),
           @maxrows     int,
           @procid      int,
           @procpgs     int,
           @syslocks    int

   select @format_mr = convert (varchar (6), @SIZE)

   select @maxrows = 1 + max (sequence)
   from sysprocedures

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

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

   if (@SIZE > @maxrows)
      begin
         select @format_mr = ltrim (substring (convert (char (9), convert (money, @maxrows), 1), 1, 6))

         print '    THE LARGEST EXECUTEABLE OBJECT HAS %1! EXECUTION ROWS.',
               @format_mr

         print ' '
      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 '    RERUN AFTER FIRST SETTING TRANSACTION ISOLATION LEVEL TO 0.'

         print ' '
      end
   else
      begin
         create table #execsizes
            (id       int          not null,
             ostatus  tinyint      not null,
             pstatus  smallint     not null,
             name     char (30)    not null,
             owner    char (9)     not null,
             crdate   char (8)     not null,
             execrows int          not null,
             textrows int          not null,
             typetext char (7)     not null)

         insert into #execsizes
            select distinct o.id,
                            (o.sysstat & 15),
                            (p.status & 4096),
                            o.name,
                            substring (isnull (user_name (o.uid), '?'), 1, 9),
                            convert (char (8), o.crdate, 11),
                            count (p.sequence),
                            0,
                            ' '
            from sysobjects o,
                 sysprocedures p
            where (o.sysstat & 15) in (2, 4, 7, 8)
              and o.id              = p.id
            group by o.id
            having (o.sysstat & 15)   in (2, 4, 7, 8)
               and o.id                = p.id
               and count (p.sequence)  > @SIZE

         update #execsizes
         set textrows = (select count (c.id)
                         from syscomments c
                         where c.id       = ps.id
                           and c.texttype = 0)
         from #execsizes ps

         update #execsizes
         set typetext = '   View'
         where ostatus  = 2

         update #execsizes
         set typetext = '     SP'
         where ostatus  = 4

         update #execsizes
         set typetext = '  Check'
         where ostatus = 7
           and pstatus = 4096

         update #execsizes
         set typetext = '   Rule'
         where ostatus  = 7
           and pstatus <> 4096

         update #execsizes
         set typetext = 'Trigger'
         where ostatus = 8

         select OWNER       = owner,
                'OBJECT'    = name,
                CREATED     = crdate,
                'EXEC ROWS' = substring (convert (char (12), convert (money, execrows), 1), 1, 9),
                'TEXT ROWS' = substring (convert (char (12), convert (money, textrows), 1), 1, 9),
                'TYPE'      = typetext
         from #execsizes
         order by execrows desc, textrows desc, name asc
      end
go
sp_addcomment sp_execsizes, 14, 'Lists size data for executeable objects with #exec rows > SIZE'
go
sp_procxmode sp_execsizes, anymode
go

create procedure sp_size (@OBJECT varchar (63)) as
   set ansinull                      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_size> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmnt0rows  char (19),
           @cmnt1rows  char (17),
           @execrows   char (12),
           @objectid   int,
           @pstatus    smallint,
           @sysstat    tinyint,
           @typetext   char (16)

   select @objectid = id,
          @sysstat  = (sysstat & 15)
   from sysobjects
   where id = object_id (@OBJECT)
   having id = object_id (@OBJECT)

   if (@sysstat in (1, 3))
      begin
         select OWNER            = substring (isnull (user_name (o.uid), '?'), 1, 16),
                'RESERVED PAGES' = substring (convert (char (17),
                                                       convert (money,
                                                                reserved_pgs (i.id, i.doampg)),
                                                       1),
                                              1,
                                              14),
                'DATA PAGES'     = substring (convert (char (13),
                                                       convert (money,
                                                                data_pgs (i.id, i.doampg)),
                                                       1),
                                              1,
                                              10),
                'APP TUPLES'     = substring (convert (char (14),
                                                       convert (money,
                                                                rowcnt (i.doampg)),
                                                       1),
                                              1,
                                              11),
                'MAX LEN'        = convert (smallint, i.maxlen)
         from sysindexes i,
              sysobjects o
         where o.id    = @objectid
           and o.id    = i.id
           and i.indid < 2

         if (exists (select *
                     from sysindexes
                     where id    = @objectid
                       and indid > 0))
            begin
               print ' '

               set nocount off

               select 'INDEX ID'       = i.indid,
                      'RESERVED PAGES' = substring (convert (char (17),
                                                             convert (money,
                                                                      reserved_pgs (i.id, i.ioampg)),
                                                             1),
                                                    1,
                                                    14),
                      'INDEX PAGES'    = substring (convert (char (14),
                                                             convert (money,
                                                                      data_pgs (i.id, i.ioampg)),
                                                             1),
                                                    1,
                                                    11),
                      'INDEX/DATA %'   = convert (smallint,
                                                  (100 * data_pgs (i.id, i.ioampg)) / data_pgs (d.id, d.doampg))
               from sysindexes d,
                    sysindexes i
               where d.id    = @objectid
                 and d.indid < 2
                 and i.id    = @objectid
                 and i.indid > 0
               order by i.indid
            end
      end
   else if (@sysstat in (2, 4, 7, 8))
      begin
         select @execrows = convert (char (12), convert (money, count (*)), 1),
                @pstatus  = (status & 4096)
         from sysprocedures
         where id = @objectid
         having id = @objectid

         if (@sysstat = 2)
            begin
               select @typetext = 'View'
            end
         else if (@sysstat = 4)
            begin
               select @typetext = 'Stored Procedure'
            end
         else if ((@sysstat = 7)  and  (@pstatus = 4096))
            begin
               select @typetext = 'Check'
            end
         else if (@sysstat = 7)
            begin
               select @typetext = 'Rule'
            end
         else
            begin
               select @typetext = 'Trigger'
            end

         select @cmnt0rows = substring (convert (char (19), convert (money, count (*)), 1), 1, 16)
         from syscomments
         where id       = @objectid
           and texttype = 0

         select @cmnt1rows = substring (convert (char (17), convert (money, count (*)), 1), 1, 14)
         from syscomments
         where id       = @objectid
           and texttype = 1

         select TYPE               = @typetext,
                'EXECUTE ROWS'     = substring (convert (char (15), convert (money, @execrows), 1), 1, 12),
                'SYSTEM TEXT ROWS' = substring (@cmnt0rows, 1, 16),
                'USER TEXT ROWS'   = substring (@cmnt1rows, 1, 14)
      end
   else if (exists (select *
                    from sysobjects
                    where name           like (@OBJECT + '%')
                      and (sysstat & 15)   in (1, 2, 3, 4, 7, 8)))
      begin
         print '    THERE IS NO OBJECT <%1!> BUT THERE ARE OBJECTS LIKE <%2!%%>.',
               @OBJECT, @OBJECT

         print ' '

         print '    USE sp_size WITH ONE OF THESE.'

         print ' '

         select OWNER          = substring (user_name (o.uid), 1, 26),
                OBJECT         = substring (o.name, 1, 26),
                TYPE           = substring (vO.name, 1, 23)
         from sysobjects o,
              sybsystemprocs.dbo.usptvalues vO
         where o.name    like (@OBJECT + '%')
           and vO.number    = convert (int, o.sysstat & 15)
           and vO.type      = 'O'
           and vO.number   in (1, 2, 3, 4, 7, 8)
         order by o.name, user_name (o.uid)
      end
   else
      begin
         print '    OBJECT <%1!> DOES NOT EXIST OR HAS NO SIZE.', @OBJECT

         print ' '
      end
go
sp_addcomment sp_size, 14, 'Displays size data of a TABLE or EXECUTEABLE'
go
sp_procxmode sp_size, anymode
go

create procedure sp_sizes (@SIZE int) 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_sizes> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @format_mp  varchar (15),
           @maxpages   int

   select @maxpages = max (sum (data_pgs (i.id, i.doampg)))
   from sysindexes i
   group by i.id

   if (@SIZE > @maxpages)
      begin
         select @format_mp = substring (convert (char (18), convert (money, @maxpages), 1), 1, 15)

         print '    THE LARGEST TABLE HAS %1! PAGES', @format_mp

         print ' '
      end
   else
      begin
         select distinct 'TABLE'      = substring (o.name, 1, 22),
                         'DATA PAGES' = substring (convert (char (14),
                                                            convert (money,
                                                                     sum (data_pgs (i.id, i.doampg))),
                                                            1),
                                                   1,
                                                   11),
                         'APP TUPLES' = substring (convert (char (14),
                                                            convert (money,
                                                                     rowcnt (i0.doampg)),
                                                            1),
                                                   1,
                                                   11),
                         'MAX LEN'    = substring (convert (char (10),
                                                            convert (money,
                                                                     i0.maxlen),
                                                            1),
                                                   1,
                                                   7),
                         OWNER        = substring (isnull (user_name (o.uid), '?'), 1, 9),
                         TYPE         = substring (vO.name, 1, 12)
         from sysindexes i,
              sysindexes i0,
              sysobjects o,
              sybsystemprocs.dbo.usptvalues vO
         where o.id      = i.id
           and o.id      = i0.id
           and i0.indid  < 2
           and vO.type   = 'O'
           and vO.number = convert (int, o.sysstat & 15)
         group by o.id
         having @SIZE     < sum (data_pgs (i.id, i.doampg))
            and o.id      = i.id
            and o.id      = i0.id
            and i0.indid  < 2
            and vO.type   = 'O'
            and vO.number = convert (int, o.sysstat & 15)
         order by 'DATA PAGES' desc, 'TABLE'
      end
go
sp_addcomment sp_sizes, 14, 'Lists size data for tables with #pages > SIZE'
go
sp_procxmode sp_sizes, anymode
go

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

         print ' '

         return 1
      end

   create table #spacebyuser
      (uid     smallint      not null,
       owner   varchar (30)  not null,
       adata   int           not null,
       udata   int           not null,
       aindex  int           not null,
       uindex  int           not null,
       txt     int           not null)

   insert into #spacebyuser
      select distinct o.uid,
                      user_name (o.uid),
                      sum (reserved_pgs (i.id, i.doampg)),
                      sum (data_pgs (i.id, i.doampg)),
                      0,
                      0,
                      0
      from sysindexes i,
           sysobjects o
      where o.id    > 99
        and i.id    = o.id
        and i.indid < 2
      group by o.uid
      having o.id    > 99
         and i.id    = o.id
         and i.indid < 2

   insert into #spacebyuser
      select -1,
             '(system tables)',
             sum (reserved_pgs (id, doampg)),
             sum (data_pgs (id, doampg)),
             0,
             0,
             0
      from sysindexes
      where id     < 100
        and id    <> 8
        and indid  < 2

   update #spacebyuser
   set aindex = (select sum (reserved_pgs (i.id, i.ioampg))
                 from sysindexes i
                 where i.id     < 100
                   and i.id    <> 8
                   and i.indid  > 0
                   and i.indid  < 254),
       uindex = (select sum (data_pgs (i.id, i.ioampg))
                 from sysindexes i
                 where i.id     < 100
                   and i.id    <> 8
                   and i.indid  > 0
                   and i.indid  < 254),
       txt    = (select sum (data_pgs (i.id, i.ioampg))
                 from sysindexes i
                 where i.id     < 100
                   and i.id    <> 8
                   and i.indid  > 253)
   from #spacebyuser s
   where s.uid = -1

   update #spacebyuser
   set aindex = (select isnull (sum (reserved_pgs (i.id, i.ioampg)), 0)
                 from sysindexes i,
                      sysobjects o
                 where o.uid   = s.uid
                   and o.id    = i.id
                   and i.id    > 99
                   and i.indid > 0
                   and i.indid < 254),
       uindex = (select isnull (sum (data_pgs (i.id, i.ioampg)), 0)
                 from sysindexes i,
                      sysobjects o
                 where o.uid   = s.uid
                   and o.id    = i.id
                   and i.id    > 99
                   and i.indid > 0
                   and i.indid < 254),
       txt    = (select isnull (sum (data_pgs (i.id, i.ioampg)), 0)
                 from sysindexes i,
                      sysobjects o
                 where o.uid    = s.uid
                   and o.id     = i.id
                   and i.id     > 99
                   and i.indid  > 253)
     from #spacebyuser s
     where s.uid > -1

   if (exists (select *
               from master.dbo.sysusages
               where dbid   = db_id ()
                 and segmap = 4))
      begin
         insert into #spacebyuser
            select distinct -2,
                            '(transaction log)',
                            sum (u.size),
                            data_pgs (8, i.doampg),
                            0,
                            0,
                            0
            from sysindexes i,
                 master.dbo.sysusages u
            where i.id     = 8
              and u.dbid   = db_id ()
              and u.segmap = 4
            group by u.dbid
            having i.id     = 8
               and u.dbid   = db_id ()
               and u.segmap = 4
      end
   else
      begin
         print '    SPACE USAGE FOR THE NON-DEDICATED TRANSACTION LOG IS UNAVAILABLE.'

         print ' '
      end

   select 'USER'         = substring (owner, 1, 15),
          'ALLOC DPAGES' = substring (convert (char (15), convert (money, adata), 1), 1, 12),
          'USED DPAGES'  = substring (convert (char (14), convert (money, udata), 1), 1, 11),
          'ALLOC IPAGES' = substring (convert (char (15), convert (money, aindex), 1), 1, 12),
          'USED IPAGES'  = substring (convert (char (14), convert (money, uindex), 1), 1, 11),
          'TEXT PAGES'   = substring (convert (char (14), convert (money, txt), 1), 1, 11)
   from #spacebyuser
   order by owner
go
sp_addcomment sp_spacebyuser, 14, 'Lists aggregate table space by user'
go
sp_procxmode sp_spacebyuser, anymode
go

dump transaction sybsystemprocs with truncate_only
go
