use sybsystemprocs
go

drop procedure sp_dumplog
go
drop procedure sp_freespace
go
drop procedure sp_lastxact
go
drop procedure sp_lockconfig
go
--drop procedure sp_loggedmsgs          Bug # 138622 makes this pointless.
--go
drop procedure sp_new
go
drop procedure sp_segmentdetail
go
drop procedure sp_segments
go
drop procedure sp_thaprint
go
drop procedure sp_thatruncate
go
drop procedure sp_unused_logins
go
drop procedure sp_unused
go
drop procedure sp_xactagg
go

set quoted_identifier on
go

create procedure sp_dumplog (@TYPE_FLAG  char (1) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @allocpgs   int,
           @dbname     varchar (30),
           @duration   varchar (14),
           @error      int,
           @ownerflag  tinyint,
           @roleflag   tinyint,
           @timeend    datetime,
           @timestart  datetime

   print ' '

   if ((@@tranchained = 0)  and
       (@@trancount = 0)    and
       (exists (select *
                from master.dbo.sysdatabases
                where dbid             = db_id ()
                  and (status & 1024) <> 1024)))
      begin
         select @dbname   = name,
                @roleflag = charindex ('oper_role', show_role ()) + charindex ('sa_role', show_role ())
         from master.dbo.sysdatabases
         where dbid = db_id ()

         select @ownerflag = 1
         from master.dbo.sysdatabases
         where dbid = db_id ()
           and suid = suser_id ()

         if ((@ownerflag = 1)  or  (@roleflag > 0))
            begin
               select @allocpgs  = data_pgs (id, doampg),
                      @timestart = getdate ()
               from sysindexes
               where id = 8

               if (@TYPE_FLAG IS NULL)
                  begin
                     print '    DUMPING TRANSACTION LOG WITH TRUNCATE_ONLY.'

                     dump transaction @dbname with truncate_only

                     select @error = @@error
                  end
               else
                  begin
                     print '    DUMPING TRANSACTION LOG WITH NO_LOG.'

                     dump transaction @dbname with no_log

                     select @error = @@error
                  end

               print ' '

               if (@error > 0)
                  begin
                     print ' '

                     print '    DUMP FAILED WITH MESSAGE %1!.', @error
                  end
               else
                  begin
                     print '    DUMP WAS SUCCESSFUL.'
                  end

            print ' '

            select @allocpgs = @allocpgs - data_pgs (id, doampg)
            from sysindexes
            where id = 8

            select @timeend = getdate ()

            execute sp_duration @timestart, @timeend, @duration output

            select DURATION             = @duration,
                   'PAGES FREED'        = substring (convert (char (14),
                                                              convert (money,
                                                                      @allocpgs),
                                                              1),
                                                     1,
                                                     11)

               print ' '
            end
         else
            begin
               print '    YOU DO NOT HAVE DUMP TRANSACTION PRIVILEGES.'
            end
      end
   else
      begin
         print '    Sp_dumplog CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         print '    @@TRANCHAINED = <%1!>, @@TRANCOUNT = <%2!>.',
               @@tranchained, @@trancount
      end

   print ' '
go
sp_addcomment sp_dumplog, 11, 'Dumps log with truncate_only (default) or no_log'
go

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

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

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

         print ' '

         return 1
      end

   declare @allocpgs   int,
           @datacnt    varchar (3),
           @dataflag   tinyint,
           @datapgs    int,
           @dbname     varchar (30),
           @freepgs    int,
           @indpgs     int,
           @logcnt     varchar (3),
           @logflag    tinyint,
           @logpgs     int,
           @mixedcnt   varchar (3),
           @mixedflag  tinyint,
           @pctused    tinyint,
           @textpgs    int,
           @totpgs     int

   execute sp_date

   print ' '

   select @dataflag  = 0,
          @logflag   = 0,
          @mixedflag = 0

   select @dataflag = count (u.dbid)                 -- dedicated data areas?
   from master.dbo.sysdatabases d,
        master.dbo.sysusages u
   where d.dbid = db_id ()
     and u.dbid = d.dbid
     and (u.segmap & 7) in (0, 1, 2, 3, 5, 6)

   select @logflag = count (u.dbid)                  -- dedicated log areas?
   from master.dbo.sysdatabases d,
        master.dbo.sysusages u
   where d.dbid         = db_id ()
     and u.dbid         = d.dbid
     and (u.segmap & 7) = 4

   select @mixedflag = count (u.dbid)                -- mixed data/log areas?
   from master.dbo.sysdatabases d,
        master.dbo.sysusages u
   where d.dbid         = db_id ()
     and u.dbid         = d.dbid
     and (u.segmap & 7) = 7

   if (@mixedflag = 0)
      begin
         select @totpgs = sum (size)
         from master.dbo.sysusages
         where dbid    = db_id ()
           and segmap <> 4

         select @allocpgs = sum (reserved_pgs (id, doampg) + reserved_pgs (id, ioampg))
         from sysindexes
         where id <> 8

         select @datapgs = sum (data_pgs (id, doampg)),
                @indpgs  = sum (data_pgs (id, ioampg))
         from sysindexes
         where id    <> 8
           and indid <> 254
           and indid <> 255

         select @textpgs = sum (data_pgs (id, ioampg))
         from sysindexes
         where id    <> 8
           and indid in (254, 255)

         select @freepgs = @totpgs - @allocpgs,
                @pctused = (100 * @allocpgs) / @totpgs

         select ' %DB USED'   = @pctused,
                'TOTAL PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @totpgs),
                                                    1),
                                           1,
                                           11),
                ' FREE PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @freepgs),
                                                    1),
                                           1,
                                           11),
                'ALLOC PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @allocpgs),
                                                    1),
                                           1,
                                           11),
                '  DATA PGS'  = substring (convert (char (13),
                                                    convert (money,
                                                             @datapgs),
                                                    1),
                                           1,
                                           10),
                ' INDEX PGS'  = substring (convert (char (13),
                                                    convert (money,
                                                             @indpgs),
                                                    1),
                                           1,
                                           10),
                ' TEXT PGS'   = substring (convert (char (12),
                                                    convert (money,
                                                             @textpgs),
                                                    1),
                                           1,
                                           9)

         print ' '

         select @totpgs = sum (u.size),
                @logpgs = data_pgs (i.id, i.doampg)
         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

         select @freepgs = @totpgs - @logpgs,
                @pctused = (100 * @logpgs) / @totpgs

         select '%LOG USED'   = @pctused,
                'TOTAL PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @totpgs),
                                                    1),
                                           1,
                                           11),
                ' FREE PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @freepgs),
                                                    1),
                                           1,
                                           11),
                'ALLOC PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @logpgs),
                                                    1),
                                           1,
                                           11)
      end
   else
      begin
        select @datacnt   = substring (convert (char (6), convert (money, @dataflag), 1), 1, 3),
               @logcnt    = substring (convert (char (6), convert (money, @logflag), 1), 1, 3),
               @mixedcnt  = substring (convert (char (6), convert (money, @mixedflag), 1), 1, 3)

        select @datacnt  = ltrim (@datacnt),
               @dbname   = db_name (),
               @logcnt   = ltrim (@logcnt),
               @mixedcnt = ltrim (@mixedcnt)

        if (@dataflag = 0)
           begin
              if (@logflag = 0)
                 begin
                    print '    DATABASE %1! HAS %2! DATA/LOG AREA(S).',
                          @dbname,  @mixedcnt

                    print ' '
                 end
              else
                 begin
                    print '    DATABASE %1! HAS %2! DATA/LOG AND %3! LOG AREA(S).',
                          @dbname, @mixedcnt, @logcnt

                    print ' '

                    print '    THE FOLLOWING DATA DOES NOT INDICATE SEPARATE DATA/LOG LIMITATIONS.'

                    print ' '
                 end
           end
        else
           begin
              if (@logflag = 0)
                 begin
                    print '    DATABASE %1! HAS %2! DATA/LOG AND %3! DATA AREA(S).',
                          @dbname, @mixedcnt, @datacnt

                    print ' '
                 end
              else
                 begin
                    print '    DATABASE %1! HAS %2! DATA/LOG, %3! DATA, AND %4! LOG AREA(S).',
                          @dbname, @mixedcnt, @datacnt, @logcnt

                    print ' '
                 end

              print '    THE FOLLOWING DATA DOES NOT INDICATE SEPARATE DATA/LOG LIMITATIONS.'

              print ' '
           end

        select @totpgs = sum (size)
        from master.dbo.sysusages
        where dbid = db_id ()

        select @allocpgs = sum (reserved_pgs (id, doampg) + reserved_pgs (id, ioampg))
        from sysindexes
        where id <> 8

         select @datapgs = sum (data_pgs (id, doampg)),
                @indpgs  = sum (data_pgs (id, ioampg))
         from sysindexes
         where id    <> 8
           and indid <> 254
           and indid <> 255

         select @textpgs = sum (data_pgs (id, ioampg))
         from sysindexes
         where id    <> 8
           and indid in (254, 255)

        select @logpgs = data_pgs (id, doampg)
        from sysindexes
        where id = 8

        select @allocpgs = @allocpgs + @logpgs

        select @freepgs = @totpgs - @allocpgs,
               @pctused = (100 * @allocpgs) / @totpgs

        select '%USED'       = @pctused,
               'TOTAL PAGES' = substring (convert (char (14),
                                                   convert (money,
                                                            @totpgs),
                                                   1),
                                          1,
                                          11),
               ' FREE PAGES' = substring (convert (char (14),
                                                   convert (money,
                                                            @freepgs),
                                                   1),
                                          1,
                                          11),
               'ALLOC PAGES' = substring (convert (char (14),
                                                   convert (money,
                                                            @allocpgs),
                                                   1),
                                          1,
                                          11),
               ' DATA PAGES' = substring (convert (char (14),
                                                   convert (money,
                                                            @datapgs),
                                                   1),
                                          1,
                                          11),
               'INDEX PAGES' = substring (convert (char (14),
                                                   convert (money,
                                                            @indpgs),
                                                   1),
                                          1,
                                          11),
                ' TEXT PAGES' = substring (convert (char (14),
                                                    convert (money,
                                                             @textpgs),
                                                    1),
                                           1,
                                           11)
      end
go
sp_addcomment sp_freespace, 11, 'Lists total/free/log space'
go
sp_procxmode sp_freespace, anymode
go

create procedure sp_lastxact (@XACTS  tinyint = 1)  as
   print ' '

   if ((@@tranchained = 0)  and
       (@@trancount = 0)    and
       (exists (select *
                from master.dbo.sysdatabases
                where dbid             = db_id ()
                  and (status & 1024) <> 1024)))
      begin
         set ansinull                      on
         set chained                       off              -- Non-ANSI
         set flushmessage                  on
         set string_rtruncation            on
         set transaction isolation level   1                -- Non-ANSI

         declare @lastxact   varbinary (6),
                 @xactcount  tinyint

         select @lastxact  = xactid,
                @xactcount = 1
         from syslogs

         while (@xactcount < @XACTS)
            begin
               select @lastxact = xactid
               from syslogs
               where xactid < @lastxact

               select @xactcount = @xactcount + 1
            end

         select 'TRANSACTION' = l.xactid,
                OPERATION     = isnull (substring (vOP.name, 1, 62),
                                        'op = ' + convert (varchar (10), l.op))
         from syslogs l,
              sybsystemprocs.dbo.usptvalues vOP
         where l.xactid >= @lastxact
           and l.op     *= vOP.number
           and vOP.type  = 'OP'
      end
   else
      begin
         print '    sp_lastxact CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         print '    @@TRANCHAINED = %1!, @@TRANCOUNT = %2!.', @@tranchained, @@trancount
      end

   print ' '
go
sp_addcomment sp_lastxact, 11, 'Lists composition of last transactions in syslogs (default XACTS is 1)'
go
sp_procxmode sp_lastxact, anymode
go

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

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

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

         print ' '

         return 1
      end

   print ' '

   declare @maxnamelen tinyint

   create table #lockcfg
     (sort      tinyint       not null,
      type      char (8)      not null,
      name      varchar (30)  not null,
      dpages    char (10)     not null,
      txt       varchar (33)  not null)

   insert into #lockcfg
      select 1,
             'Table',
             object_name (a.object),
             substring (convert (char (13), convert (money, data_pgs (i.id, i.doampg)), 1), 1, 10),
             substring (a.char_value, 1, 33)
      from sysattributes a,
           sysindexes i
      where a.object_type = 'T'
        and a.class       = 5
        and i.id          = a.object
        and i.indid       < 2

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

   insert into #lockcfg
      select 3,
             'Server',
             '-',
             '         -',
             'PCT = '
             + convert (varchar (11), pc.value)
             + ', LWM = '
             + convert (varchar (11), lc.value)
             + ', HWM = '
             + convert (varchar (11), hc.value)
      from master.dbo.sysconfigures pf,
           master.dbo.sysconfigures lf,
           master.dbo.sysconfigures hf,
           master.dbo.syscurconfigs pc,
           master.dbo.syscurconfigs lc,
           master.dbo.syscurconfigs hc
      where pf.config  = pc.config
        and pf.name    = 'lock promotion PCT'
        and pf.parent <> 19
        and pf.config <> 19
        and lf.config  = lc.config
        and lf.name    = 'lock promotion LWM'
        and lf.parent <> 19
        and lf.config <> 19
        and hf.config  = hc.config
        and hf.name    = 'lock promotion HWM'
        and hf.parent <> 19
        and hf.config <> 19

   select @maxnamelen = max (char_length (name))
   from #lockcfg

   if (@maxnamelen < 14)
       begin
          select TYPE                  = type,
                 OBJECT                = substring (name, 1, 13),
                 'LOCK PROMOTION DATA' = txt,
                 'DATA PAGES'          = dpages
          from #lockcfg
          order by sort, name
       end
   else if (@maxnamelen < 24)
       begin
          select TYPE                  = type,
                 OBJECT                = substring (name, 1, 23),
                 'LOCK PROMOTION DATA' = txt,
                 'DATA PAGES'          = dpages
          from #lockcfg
          order by sort, name
       end
   else
       begin
          select TYPE                  = type,
                 OBJECT                = name,
                 'LOCK PROMOTION DATA' = txt
          from #lockcfg
          order by sort, name
       end
go
sp_addcomment sp_lockconfig, 11, 'Lists lock promotion values for local tables/database and server'
go
sp_procxmode sp_lockconfig, anymode
go

--create procedure sp_loggedmsgs 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_loggedmsgs> CANNOT BE RUN FROM WITHIN A TRANSACTION.'
--
--         print ' '
--
--         return 1
--      end
--
--   declare @maxerror  int
--
--   select @maxerror = max (error)
--   from sysusermessages
--   where (dlevel & 128) <> 128
--
--   if (db_id () = 1)
--      begin
--         if (@maxerror IS NULL)
--            begin
--               select @maxerror = max (error)
--               from master.dbo.sysmessages
--               where severity          < 19
--                 and (dlevel & -32766) = -32766
--            end
--
--         if (@maxerror IS NULL)
--            begin
--               print '    ALL NON-FATAL SYSTEM MESSAGES AND USER MESSAGES ARE UNLOGGED.'
--
--               print ' '
--            end
--         else
--            begin
--               print '    THERE ARE LOGGED NON-FATAL SYSTEM MESSAGES AND/OR USER MESSAGES.'
--
--               print ' '
--
--              if (@maxerror > 32766)
--                 begin
--                        select 'ERROR #'   = 0,
--                               'SVR'       = convert (tinyint, 0),
--                               DESCRIPTION = space (61)
--                        where 1 = 2
--                     union
--                        select error,
--                               convert (tinyint, severity),
--                               substring (description, 1, 61)
--                        from master.dbo.sysmessages
--                        where severity          < 19
--                          and (dlevel & -32766) = -32766
--                     union
--                        select error,
--                               convert (tinyint, 16),
--                               substring (description, 1, 61)
--                        from sysusermessages
--                        where (dlevel & 128) <> 128
--                     order by 'ERROR #'
--                 end
--              else
--                 begin
--                       select 'ERROR #'   = convert (smallint, 0),
--                              'SVR'       = convert (tinyint, 0),
--                              DESCRIPTION = space (65)
--                       where 1 = 2
--                    union
--                       select convert (smallint, error),
--                              convert (tinyint, severity),
--                              substring (description, 1, 65)
--                       from master.dbo.sysmessages
--                       where severity          < 19
--                         and (dlevel & -32766) = -32766
--                    union
--                       select convert (smallint, error),
--                              convert (tinyint, 16),
--                              substring (description, 1, 65)
--                       from sysusermessages
--                       where (dlevel & 128) <> 128
--                    order by 'ERROR #'
--                 end
--            end
--      end
--   else
--      begin
--         if (@maxerror IS NULL)
--            begin
--               print '    ALL USER MESSAGES ARE UNLOGGED.'
--
--               print ' '
--            end
--         else
--            begin
--               print '    THERE ARE LOGGED USER MESSAGES.'
--
--               print ' '
--
--              if (@maxerror > 32766)
--                 begin
--                    select 'ERROR #'   = error,
--                           DESCRIPTION = substring (description, 1, 65)
--                    from sysusermessages
--                    where (dlevel & 128) <> 128
--                    order by error
--                 end
--              else
--                 begin
--                    select 'ERROR #'   = convert (smallint, error),
--                           DESCRIPTION = substring (description, 1, 69)
--                    from sysusermessages
--                    where (dlevel & 128) <> 128
--                    order by error
--                 end
--            end
--      end
--go
--sp_addcomment sp_loggedmsgs, 11, 'Lists logged user messages / non-fatal system messages'
--go
--sp_procxmode sp_loggedmsgs, anymode
--go

create procedure sp_new (@DAYS tinyint = 4) as
   set ansinull                      on
   set nocount                       on
   set quoted_identifier             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_new> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   execute sp_date

   set nocount off

   print ' '

   create table #new
     (id        int           not null,
      uid       smallint      not null,
      prntname  varchar (38)  not null,
      otype     varchar (23)  not null,
      crdate    datetime      not null)

   insert into #new
      select o.id, o.uid, o.name, substring (vO.name, 1, 23), o.crdate
      from sysobjects o,
           sybsystemprocs.dbo.usptvalues vO
      where datediff (dd, o.crdate, getdate ()) < @DAYS
        and vO.type                             = 'O'
        and vO.number                           = (o.sysstat & 15)

   update #new
   set prntname = substring (prntname  +  ' ('  +  user_name (uid)  +  ')', 1, 38)
   from #new n
   where (n.uid <> 1)
      or (1 < (select count (o.id)
               from sysobjects o
               where o.name = n.prntname))

   if (db_id () = 1)
      begin
         insert into #new
            select dbid,
                   suid,
                   name  +  ' ('  +  suser_name (suid)  +  ')',
                   'database',
                   crdate
            from master.dbo.sysdatabases
            where datediff (dd, crdate, getdate ()) < @DAYS

         insert into #new
            select 0,
                   0,
                   'engine ('  +  convert (varchar (3), engine)  +  ')',
                   'server engine',
                   starttime
            from master.dbo.sysengines
            where datediff (dd, starttime, getdate ()) < @DAYS

         insert into #new
            select 0,
                   suid,
                   suser_name (suid),
                   'login',
                   accdate
            from master.dbo.syslogins
            where datediff (dd, accdate, getdate ()) < @DAYS
      end

   if (exists (select *
               from #new))
      begin
         select OBJECT        = prntname,
                'OBJECT TYPE' = otype,
                CREATED       = convert (char (8), crdate, 11)
                                +  ' '
                                +  substring (convert (char (8), crdate, 8), 1, 5)
         from #new
         order by crdate, prntname
      end
   else
      begin
         print '    ALL CHECKED OBJECTS ARE OLDER THAN %1! DAYS.', @DAYS

         print ' '
      end
go
sp_addcomment sp_new, 11, 'Lists objects newer than DAYS (default is 4) days'
go
sp_procxmode sp_new, anymode
go

create procedure sp_segmentdetail (@SEGMENT varchar (30) = 'logsegment') as
   set ansinull                      on
   set string_rtruncation            on

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

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

         print ' '

         return 1
      end

   declare @segnum  smallint

   print ' '

   select @segnum = segment
   from syssegments
   where name = @SEGMENT

   if (@segnum IS NULL)
      begin
         print '    SEGMENT %1! DOES NOT EXIST.', @SEGMENT

         print ' '
      end
   else
      begin
         print '    DETAILS ARE FOR SEGMENT %1!.', @SEGMENT

         print ' '

         if (exists (select *
                     from systhresholds
                     where segment = @segnum))
            begin
               select THRESHOLD          = substring (convert (char (12),
                                                               convert (money,
                                                                        t.free_space),
                                                               1),
                                                      1,
                                                      9),
                      'LCT?'             = substring (vB.name, 1, 4),
                      'STORED PROCEDURE' = substring (t.proc_name, 1, 30),
                      OWNER              = substring (suser_name (t.suid), 1, 18)
               from systhresholds         t,
                    sybsystemprocs.dbo.usptvalues vB
               where t.segment = @segnum
                 and t.status  = vB.number
                 and vB.type   = 'B'
               order by free_space
            end
         else
            begin
               print '    SEGMENT %1! HAS NO THRESHOLDS.', @SEGMENT
            end

         print ' '

            select DEVICE        = space (14),
                   'DEDICATED?'  = space (9),
                   'TOTAL PAGES' = space (11),
                   'FREE PAGES'  = space (11)
            where 1 = 2
         union all
            select substring (d.name, 1, 14),
                   'Exclusive',
                   substring (convert (char (14), convert (money, u.size), 1),
                              1,
                              11),
                   substring (convert (char (14),
                                       convert (money,
                                                curunreservedpgs (db_id (),
                                                                  u.lstart,
                                                                  u.unreservedpgs)),
                                       1),
                              1,
                              11)
            from syssegments s,
                 master.dbo.sysdevices d,
                 master.dbo.sysusages u
            where s.name                = @SEGMENT
              and d.cntrltype           = 0
              and power (2, s.segment)  = (u.segmap & power (2, s.segment))
              and u.dbid                = db_id ()
              and d.low                <= u.vstart + u.size
              and d.high               >= u.vstart + u.size - 1
              and 1                     = (select count (s2.segment)
                                           from syssegments s2,
                                                master.dbo.sysusages u2
                                           where u.vstart              = u2.vstart
                                             and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))
         union all
            select substring (d.name, 1, 14),
                   'Shared',
                   substring (convert (char (14), convert (money, u.size), 1),
                              1,
                              11),
                   substring (convert (char (14),
                                       convert (money,
                                                curunreservedpgs (db_id (),
                                                                  u.lstart,
                                                                  u.unreservedpgs)),
                                       1),
                              1,
                              11)
            from syssegments s,
                 master.dbo.sysdevices d,
                 master.dbo.sysusages u
            where s.name                = @SEGMENT
              and d.cntrltype           = 0
              and power (2, s.segment)  = (u.segmap & power (2, s.segment))
              and u.dbid                = db_id ()
              and d.cntrltype           = 0
              and d.low                <= u.vstart + u.size
              and d.high               >= u.vstart + u.size - 1
              and 1                     < (select count (s2.segment)
                                           from syssegments s2,
                                                master.dbo.sysusages u2
                                           where u.vstart              = u2.vstart
                                             and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))
         order by 'TOTAL PAGES'

         print ' '

         if (exists (select i.*
                     from sysindexes i,
                          syssegments s
                     where i.segment = s.segment
                       and s.name    = @SEGMENT))
            begin
                  select OWNER    = space (12),
                         TYPE     = space (17),
                         'OBJECT' = space (44)
                  where 1 = 2
               union all
                  select substring (user_name (o.uid), 1, 12),
                         substring (vO.name, 1, 17),
                         o.name
                  from sysindexes i,
                       sysobjects o,
                       syssegments s,
                       sybsystemprocs.dbo.usptvalues vO
                  where s.name    = @SEGMENT
                    and i.segment = s.segment
                    and i.id      = o.id
                    and i.indid   = 0
                    and vO.number = convert (int, o.sysstat & 15)
                    and vO.type   = 'O'
               union all
                  select substring (user_name (o.uid), 1, 12),
                         'clustered index',
                         substring (object_name (i.id)  +  '.'  +  i.name,
                                    1,
                                    44)
                  from sysindexes i,
                       sysobjects o,
                       syssegments s
                  where s.name    = @SEGMENT
                    and i.segment = s.segment
                    and i.id      = o.id
                    and i.indid   = 1
               union all
                  select substring (user_name (o.uid), 1, 12),
                         'nonclustered index ',
                         substring (object_name (i.id)  +  '.'  +  i.name,
                                    1,
                                    44)
                  from sysindexes i,
                       sysobjects o,
                       syssegments s
                  where s.name    = @SEGMENT
                    and i.segment = s.segment
                    and i.id      = o.id
                    and i.indid   > 1
               order by 'OBJECT', OWNER
            end
         else
            begin
               print '    SEGMENT %1! IS UNUSED.', @SEGMENT

               print ' '
            end
      end
go
sp_addcomment sp_segmentdetail, 11, 'Lists threshold, device, and object info for SEGMENT'
go
sp_addcomment sp_segmentdetail, 11, '   default SEGMENT is "logsegment"'
go
sp_procxmode sp_segmentdetail, anymode
go

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

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

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

         print ' '

         return 1
      end

   declare @dbname  varchar (32),
           @thcount tinyint

   print ' '

   select @dbname = db_name ()

   create table #segments1
      (segnum     smallint      not null,
       segname    varchar (34)  not null,
       status     smallint      not null,
       objcount   varchar (20)  not null,
       fs_size    char (7)      not null,
       lct        char (3)      not null,
       sp         varchar (30)  null)

   create table #segments2
      (phyname    varchar (30)  not null,
       segname    varchar (30)  not null,
       devname    varchar (30)  not null,
       dedicated  char (9)      not null,
       fpage      int           not null,
       size       int           not null,
       free       int           not null)

   if (exists (select *
               from systhresholds))
       begin
          if (exists (select *
                      from master.dbo.sysdatabases
                      where dbid          = db_id ()
                        and (status2 & 2) = 2))
             begin
               print '    THERE IS NO FREE-SPACE ACCOUNTING FOR DATABASE%1!.',
                     @dbname

               print ' '
             end

          if (exists (select t.*
                      from systhresholds t
                      where (not exists (select o.*
                                         from sysobjects o
                                         where o.name           = t.proc_name
                                           and (o.sysstat & 15) = 4))
                        and (not exists (select so.*
                                         from sybsystemprocs.dbo.sysobjects so
                                         where so.name           = t.proc_name
                                           and (so.sysstat & 15) = 4))))
             begin
               print '    THERE ARE NON-EXISTENT THRESHOLD PROCEDURES.'

               print ' '

               select SEGMENT               = substring (s.name, 1, 18),
                      'TH SIZE'             = substring (convert (char (10), convert (money, t.free_space), 1), 1, 7),
                      'TH STORED PROCEDURE' = substring (t.proc_name, 1, 30),
                      OWNER                 = substring (suser_name (t.suid), 1, 18)
               from syssegments   s,
                    systhresholds t
               where s.segment = t.segment
                 and (not exists (select o.*
                                  from sysobjects o
                                  where o.name           = t.proc_name
                                    and (o.sysstat & 15) = 2))
                 and (not exists (select so.*
                                  from sybsystemprocs.dbo.sysobjects so
                                  where so.name           = t.proc_name
                                    and (so.sysstat & 15) = 2))
               order by s.name, t.free_space
             end
       end

   insert into #segments1                                -- unused segment
      select segment,
             name,
             status,
             '           0 objects',
             ' ',
             'no',
             NULL
      from syssegments s
      where (not exists (select i.*
                         from sysindexes i
                         where i.segment = s.segment))

   insert into #segments1                                -- 1 object on segment
      select s.segment,
             s.name,
             s.status,
             substring (space (20 - char_length (i.name)) + i.name, 1, 20),
             ' ',
             'no',
             NULL
      from sysindexes i,
           syssegments s
      where s.segment = i.segment
        and i.indid   = 0
        and 1         = (select count (ii.indid)
                         from sysindexes ii
                         where ii.segment = s.segment)

   insert into #segments1                                -- 1 index on segment
      select s.segment,
             s.name,
             s.status,
             substring (space (20 - char_length (object_name (i.id) + '.' + i.name))
                        + object_name (i.id) + '.' + i.name, 1, 20),
             ' ',
             'no',
             NULL
      from sysindexes i,
           syssegments s
      where s.segment = i.segment
        and i.indid   = 1
        and 1         = (select count (ii.indid)
                         from sysindexes ii
                         where ii.segment = s.segment)

   insert into #segments1                                -- N objects on segment
      select distinct s.segment,
                      s.name,
                      s.status,
                      substring (convert (char (15),
                                          convert (money,
                                                   count (i.indid)),
                                          1),
                                 1,
                                 12)  +  ' objects',
             ' ',
             'no',
             NULL
      from sysindexes i,
           syssegments s
      where s.segment = i.segment
        and 1         < (select count (ii.indid)
                         from sysindexes ii
                         where ii.segment = s.segment)
      group by s.segment

   update #segments1
   set segname = segname + ' ##'
   where status = 1

   update #segments1
   set lct = vB.name
   from systhresholds         t,
        sybsystemprocs.dbo.usptvalues vB
   where t.segment = #segments1.segnum
     and t.status  = vB.number
     and vB.type   = 'B'

   select @thcount = max (count(*))
   from systhresholds
   group by segment

   if (@thcount IS NULL)
      begin
         print '    THERE ARE NO THRESHOLD STORED PROCEDURES.'

         print ' '

         select NUMBER      = segnum,
                SEGMENT     = substring (segname, 1, 16),
                'OBJECT(S)' = objcount
         from #segments1
         order by segname
      end
   else if (@thcount = 1)
      begin
         update #segments1
         set fs_size = substring (convert (char (10), convert (money, t.free_space), 1), 1, 7),
             sp      = t.proc_name
         from systhresholds t
         where t.segment = #segments1.segnum

         select NUMBER                = segnum,
                SEGMENT               = substring (segname, 1, 16),
                'OBJECT(S)'           = objcount,
                'LCT?'                = lct,
                'TH SIZE'             = fs_size,
                'TH STORED PROCEDURE' = isnull (substring (sp, 1, 19), '-')
         from #segments1
         order by segname
      end
   else
      begin
         update #segments1
         set fs_size = (select substring (convert (char (10), convert (money, count (t.segment)), 1), 1, 7)
                        from systhresholds t
                        where t.segment = #segments1.segnum)

         select NUMBER      = segnum,
                SEGMENT     = substring (segname, 1, 16),
                'OBJECT(S)' = objcount,
                'TH COUNT'  = fs_size,
                'LCT?'      = lct
         from #segments1
         order by segname
      end

   print ' '

   insert into #segments2
      select isnull (substring (d.phyname, 11, 8), ' '),
             s.name,
             d.name,
             'Shared',
             u.vstart,
             u.size,
             curunreservedpgs (db_id (), u.lstart, u.unreservedpgs)
      from syssegments s,
           master.dbo.sysdevices d,
           master.dbo.sysusages u
      where d.cntrltype           = 0
        and power (2, s.segment)  = (u.segmap & power (2, s.segment))
        and s.status              = 0
        and u.dbid                = db_id ()
        and d.low                <= u.vstart + u.size
        and d.high               >= u.vstart + u.size - 1

   insert into #segments2
      select isnull (substring (d.phyname, 11, 8), ' '),
             s.name  +  ' ##',
             d.name,
             'Shared',
             u.vstart,
             u.size,
             curunreservedpgs (db_id (), u.lstart, u.unreservedpgs)
      from syssegments s,
           master.dbo.sysdevices d,
           master.dbo.sysusages u
      where d.cntrltype           = 0
        and power (2, s.segment)  = (u.segmap & power (2, s.segment))
        and s.status              = 1
        and u.dbid                = db_id ()
        and d.low                <= u.vstart + u.size
        and d.high               >= u.vstart + u.size - 1

   update #segments2
   set dedicated = 'Exclusive'
   from #segments2 s
   where 1 = (select count (s2.segment)
              from syssegments s2, master.dbo.sysusages u2
              where s.fpage               = u2.vstart
                and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))

   select SEGMENT       = substring (segname, 1, 18),
          DEVICE        = substring (devname, 1, 16),
          'DEDICATED?'  = dedicated,
          'TOTAL PAGES' = substring (convert (char (14),
                                              convert (money,
                                                       size),
                                              1),
                                     1,
                                     11),
          'FREE PAGES'  = substring (convert (char (14),
                                              convert (money,
                                                       free),
                                              1),
                                     1,
                                     11)
   from #segments2
   order by segname, phyname, fpage
go
sp_addcomment sp_segments, 11, 'Lists all segments with aggregate and device info'
go
sp_procxmode sp_segments, anymode
go

create procedure sp_thaprint (@DBNAME       varchar (30),
                              @SEGMENTNAME  varchar (30),
                              @FREE_SPACE   int,
                              @STATUS       int)  as

   -- If this stored procedure is user executed the print messages are sent to
   -- the client.  If system invoked the print messages go to the error log.

   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @fsprint  varchar (11)

   print '-----------------------------------------------'

   print 'Sp_thaprint invoked.'

   print '   Database  = %1!,', @DBNAME

   print '   Segment   = %1!,', @SEGMENTNAME

   select @fsprint = ltrim (substring (convert (char (14),
                                                convert (money,
                                                         @FREE_SPACE),
                                                1),
                                       1,
                                       11))

   print '   Threshold = %1! free pages,', @fsprint

   print '   Status    = %1!.', @STATUS

   print '-----------------------------------------------'
go
sp_addcomment sp_thaprint, 11, 'ThresHold Action Stored Procedure (print only)'
go

create procedure sp_thatruncate (@DBNAME       varchar (30),
                                 @SEGMENTNAME  varchar (30),
                                 @FREE_SPACE   int,
                                 @STATUS       int)  as

   -- If this stored procedure is user executed the print messages are sent to
   -- the client.  If system invoked the print messages go to the error log.

   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @error    int,
           @fsprint  varchar (11),                    -- Free Space (print)
           @lsa      varchar (11),                    -- Log Space After
           @lsb      varchar (11)                     -- Log Space Before

   print '-----------------------------------------------'

   print 'sp_thatruncate invoked.'

   print '   Database  = %1!,', @DBNAME

   print '   Segment   = %1!,', @SEGMENTNAME

   select @fsprint = ltrim (substring (convert (char (14),
                                                convert (money,
                                                         @FREE_SPACE),
                                                1),
                                       1,
                                       11))

   print '   Threshold = %1! free pages,', @fsprint

   print '   Status    = %1!.', @STATUS

   if (exists (select name
               from syssegments
               where name    = @SEGMENTNAME
                 and segment = 2))
      begin
         select @lsb = ltrim (substring (convert (char (14),
                                                  convert (money,
                                                           reserved_pgs (id,
                                                                         doampg)),
                                                  1),
                                         1,
                                         11))
         from sysindexes
         where id = 8

         dump transaction @DBNAME with truncate_only

         select @error = @@error

         if (@error = 0)
            begin
               print 'Dump Transaction with truncate_only succeeded.'

               select @lsa = ltrim (substring (convert (char (14),
                                                        convert (money,
                                                                 reserved_pgs (id,
                                                                               doampg)),
                                                        1),
                                               1,
                                               11))
               from sysindexes
               where id = 8

               print '   Log Pages Used changed from %1! to %2!.', @lsb, @lsa
            end
         else
            begin
               print 'Dump Transaction with truncate_only failed.'

               print '   Error = %1!.', @error
            end
      end

   print '-----------------------------------------------'
go
sp_addcomment sp_thatruncate, 11, 'ThresHold Action Stored Procedure (dump with truncate_only)'
go

------------ Temporary table needed for creation of sp_unused_logins ---------

create table #unusedlogins
   (suid       smallint       not null,
    name       varchar (30)   not null,
    accdate    smalldatetime  not null)
go

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

   delete #unusedlogins
   from #unusedlogins ul,
        sysusers u
   where ul.suid = u.suid
go
sp_procxmode sp_unused_logins, unchained
go

drop table #unusedlogins
go


create procedure sp_unused (@OFF_STRING varchar (25) = 'PB,SR,SYS') as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

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

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

         print ' '

         return 1
      end

   declare @CR           char (1),
           @LF           char (1),
           @LFas         char (4),
           @LFasLF       char (4),
           @as           char (4),
           @asLF         char (4),
           @bitoff       bit,
           @biton        bit,
           @cmdstring    varchar (30),
           @now          char (8),
           @pbflag       tinyint,
           @srflag       tinyint,
           @sysflag      tinyint,
           @usertblflag  tinyint,
           @viewflag     tinyint

   create table #systable
     (name        varchar ( 32)  not null,
      type        char (  2)     not null,
      text        varchar (255)  not null)

   create table #unused
     (object      varchar ( 30)  not null,
      type        char ( 13)     not null,
      description varchar ( 33)  not null,
      owner       varchar ( 30)  not null,
      cdate       char (  8)     not null)

   print ' '

   select @CR          = char (13),                      -- Carriage Return
          @LF          = char (10),                      -- Line Feed
          @bitoff      = convert (bit, 0),
          @pbflag      = charindex ('PB', upper (@OFF_STRING)),
          @srflag      = charindex ('SR', upper (@OFF_STRING)),
          @sysflag     = charindex ('SYS', upper (@OFF_STRING)),
          @usertblflag = charindex ('USERTBL', upper (@OFF_STRING)),
          @viewflag    = charindex ('VIEW', upper (@OFF_STRING))

   select @as     = ' AS ',
          @asLF   = ' AS' + @LF,
          @biton  = ~(@bitoff),
          @LFas   = @LF + 'AS ',
          @LFasLF = @LF + 'AS' + @LF

   if (@sysflag = 0)
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    LOADING WORK TABLE FROM sybsystemprocs.        (%1!)', @now

         insert into #systable
            select distinct o.name, 'S', c.text
            from sybsystemprocs.dbo.syscomments c,
                 sysobjects o
            where (o.sysstat & 15) = 1
              and c.text like '%' + o.name + '%'

         delete from #systable
         where text like '%sp[_]' + name + '%'
      end

   if (db_name () = 'master')
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    LOADING WORK TABLE FROM master/sybsystemprocs. (%1!)', @now

         insert into #systable
            select distinct o.name, 'U', c.text
            from master.dbo.sysobjects o,
                 sybsystemprocs.dbo.syscomments c
            where (o.sysstat & 15) = 3
              and o.name like 'spt[_]%'
              and c.text like '%' + o.name + '%'

         delete from #systable
         where text like '%sp[_]' + name + '%'
           and type = 'U'

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

         print '    CHECKING FOR UNUSED BUFFER POOLS.              (%1!)', @now

         insert into #unused                        -- buffer pools
            select substring (cf.name + ': 4K I/O', 1, 30),
                   'buffer pool',
                   '-',
                   'dbo',
                   '-'
            from master.dbo.sysconfigures cf,
                 master.dbo.syscurconfigs cc
            where cf.comment = '4K I/O Buffer Pool'
              and cf.parent  = 19
              and cc.config  = cf.config
              and cc.comment = cf.name
              and exists (select cf2.*
                          from master.dbo.sysconfigures cf2
                          where cf2.parent   = 19
                            and cf2.name     = cf.name
                            and cf2.comment in ('8K I/O Buffer Pool', '16K I/O Buffer Pool'))

         insert into #unused
            select substring (cf.name + ': 8K I/O', 1, 30),
                   'buffer pool',
                   '-',
                   'dbo',
                   '-'
            from master.dbo.sysconfigures cf,
                 master.dbo.syscurconfigs cc
            where cf.comment = '8K I/O Buffer Pool'
              and cf.parent  = 19
              and cc.config  = cf.config
              and cc.comment = cf.name
              and exists (select cf2.*
                          from master.dbo.sysconfigures cf2
                          where cf2.parent   = 19
                            and cf2.name     = cf.name
                            and cf2.comment  = '16K I/O Buffer Pool')
      end

   create clustered index C on #systable (name)

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

   print '    CHECKING FOR UNUSED DEFAULTS.                  (%1!)', @now

   insert into #unused                              -- defaults (user-named)
      select o.name,
             'default',
             substring (substring (c.text,
                                   4 + charindex (@as, upper (c.text)),
                                   datalength (c.text) - 3 - charindex (@as, upper (c.text))),
                        1,
                        26),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)                = 6
        and o.id                            = p.id
        and p.sequence                      = 1
        and (p.status & 4096)              <> 4096
        and o.id                            = c.id
        and c.colid                         = 1
        and c.texttype                      = 0
        and charindex (@as, upper (c.text)) > 0
        and (not exists (select cl.*
                         from syscolumns cl
                         where cl.cdefault = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.tdefault = o.id))

   insert into #unused
      select o.name,
             'default',
             substring (substring (c.text,
                                   4 + charindex (@asLF, upper (c.text)),
                                   datalength (c.text) - 3 - charindex (@asLF, upper (c.text))),
                        1,
                        26),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)                  = 6
        and o.id                              = p.id
        and p.sequence                        = 1
        and (p.status & 4096)                <> 4096
        and o.id                              = c.id
        and c.colid                           = 1
        and c.texttype                        = 0
        and charindex (@asLF, upper (c.text)) > 0
        and (not exists (select cl.*
                         from syscolumns cl
                         where cl.cdefault = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.tdefault = o.id))

   insert into #unused
      select o.name,
             'default',
             substring (substring (c.text,
                                   4 + charindex (@LFas, upper (c.text)),
                                   datalength (c.text) - 3 - charindex (@LFas, upper (c.text))),
                        1,
                        26),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)                  = 6
        and o.id                              = p.id
        and p.sequence                        = 1
        and (p.status & 4096)                <> 4096
        and o.id                              = c.id
        and c.colid                           = 1
        and c.texttype                        = 0
        and charindex (@LFas, upper (c.text)) > 0
        and (not exists (select cl.*
                         from syscolumns cl
                         where cl.cdefault = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.tdefault = o.id))

   insert into #unused
      select o.name,
             'default',
             substring (substring (c.text,
                                   4 + charindex (@LFasLF, upper (c.text)),
                                   datalength (c.text) - 3 - charindex (@LFasLF, upper (c.text))),
                        1,
                        26),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)                    = 6
        and o.id                                = p.id
        and p.sequence                          = 1
        and (p.status & 4096)                  <> 4096
        and o.id                                = c.id
        and c.colid                             = 1
        and c.texttype                          = 0
        and charindex (@LFasLF, upper (c.text)) > 0
        and (not exists (select cl.*
                         from syscolumns cl
                         where cl.cdefault = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.tdefault = o.id))

   insert into #unused                              -- defaults (constant/function)
      select o.name,
             'default',
             substring (c.text, 9, 26),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments c,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)         = 6
        and o.id                     = p.id
        and p.sequence               = 1
        and (p.status & 4096)        = 4096
        and o.id                     = c.id
        and c.colid                  = 1
        and c.texttype               = 0
        and substring (c.text, 1, 8) = 'DEFAULT '
        and (not exists (select cl.*
                         from syscolumns cl
                         where cl.cdefault = o.id))

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

   print '    CHECKING FOR UNUSED GROUPS.                    (%1!)', @now

   insert into #unused                              -- groups w/o members
      select ug.name,
             'group',
             'no members',
             'dbo',
             '-'
      from sysusers ug
      where ug.uid = ug.gid
        and (not exists (select r.*
                         from sysroles r
                         where r.lrid = ug.uid))
        and (not exists (select uu.*
                         from sysusers uu
                         where ug.uid  = uu.gid
                           and uu.uid  > 0
                           and uu.uid <> uu.gid))

   insert into #unused                              -- groups w/o permissions
      select ug.name,
             'group',
             'no permissions',
             'dbo',
             '-'
      from sysusers ug
      where ug.uid = ug.gid
        and (not exists (select r.*
                         from sysroles r
                         where r.lrid = ug.uid))
        and (not exists (select p.*
                         from sysprotects p
                         where p.uid = ug.uid))

   insert into #unused                              -- groups w/o both
      select object, type, 'no members/permissions', owner, cdate
      from #unused u
      where u.type = 'group'
        and 1 < (select count (u2.object)
                 from #unused u2
                 where u.object = u2.object
                   and u.type   = u2.type)

   delete #unused
   where type         = 'group'
     and description in ('no members', 'no permissions')
     and 1 < (select count (u2.object)
              from #unused u2
              where #unused.object = u2.object
                and #unused.type   = u2.type)

   if (db_name () = 'master')
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING FOR UNUSED LOGINS.                    (%1!)', @now

         insert into #unused                        -- logins
            select name,
                   'login',
                   'no activity',
                   'dbo',
                   convert (char (8), accdate, 11)
            from master.dbo.syslogins
            where suid   > 2
              and totcpu = 0
              and totio  = 0

         select suid, name, accdate
         into #unusedlogins
         from master.dbo.syslogins
         where suid > 1

         delete #unusedlogins
         from #unusedlogins u,
              master.dbo.sysloginroles lr
         where lr.srid < 32
           and u.suid  = lr.suid

         select @cmdstring = 'sp_unused_logins'

         execute sp_globalexec  @cmdstring

         insert into #unused
            select name,
                   'login',
                   'neither user nor system role',
                   'dbo',
                   convert (char (8), accdate, 11)
            from #unusedlogins

         drop table #unusedlogins
      end

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

   print '    CHECKING FOR UNUSED ROLES.                     (%1!)', @now

   insert into #unused
      select sr.name,
             'role',
             'no users',
             'dbo',
             '-'
      from sysroles r,
           master.dbo.syslogins l,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr
      where sr.srid = r.id                          -- only local roles
        and sr.srid > 31                            -- only user roles
        and lr.srid = sr.srid                       -- login roles with role
        and l.suid  = lr.suid                       -- logins with login role
        and not exists (select u.*                  -- no user in login pool
                        from sysusers u
                        where u.suid = l.suid)

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

   print '    CHECKING FOR UNUSED RULES.                     (%1!)', @now

   insert into #unused                              -- rules
      select o.name,
             'rule',
             substring (ltrim (substring (cm.text,
                                          4 + charindex (@as, upper (cm.text)),
                                          datalength (cm.text) - 3 - charindex (@as, upper (cm.text)))),
                        1,
                        33),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments cm,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and o.id               = cm.id
        and cm.colid           = 1
        and cm.texttype        = 0
        and charindex (@as, upper (cm.text)) > 0
        and (not exists (select c.*
                         from syscolumns c
                         where c.domain = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.domain = o.id))

   insert into #unused
      select o.name,
             'rule',
             substring (ltrim (substring (cm.text,
                                          4 + charindex (@asLF, upper (cm.text)),
                                          datalength (cm.text) - 3 - charindex (@asLF, upper (cm.text)))),
                        1,
                        33),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments cm,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and o.id               = cm.id
        and cm.colid           = 1
        and cm.texttype        = 0
        and charindex (@asLF, upper (cm.text)) > 0
        and (not exists (select c.*
                         from syscolumns c
                         where c.domain = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.domain = o.id))

   insert into #unused
      select o.name,
             'rule',
             substring (ltrim (substring (cm.text,
                                          4 + charindex (@LFas, upper (cm.text)),
                                          datalength (cm.text) - 3 - charindex (@LFas, upper (cm.text)))),
                        1,
                        33),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments cm,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and o.id               = cm.id
        and cm.colid           = 1
        and cm.texttype        = 0
        and charindex (@LFas, upper (cm.text)) > 0
        and (not exists (select c.*
                         from syscolumns c
                         where c.domain = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.domain = o.id))

   insert into #unused
      select o.name,
             'rule',
             substring (ltrim (substring (cm.text,
                                          4 + charindex (@LFasLF, upper (cm.text)),
                                          datalength (cm.text) - 3 - charindex (@LFasLF, upper (cm.text)))),
                        1,
                        33),
             isnull (user_name (o.uid), '?'),
             convert (char (8), o.crdate, 11)
      from syscomments cm,
           sysobjects o,
           sysprocedures p
      where (o.sysstat & 15)   = 7
        and o.id               = p.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and o.id               = cm.id
        and cm.colid           = 1
        and cm.texttype        = 0
        and charindex (@LFasLF, upper (cm.text)) > 0
        and (not exists (select c.*
                         from syscolumns c
                         where c.domain = o.id))
        and (not exists (select t.*
                         from systypes t
                         where t.domain = o.id))

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

   print '    CHECKING FOR UNUSED SEGMENTS.                  (%1!)', @now

   insert into #unused                              -- exclusive segments
      select s.name,
             'segment',
             'exclusive',
             'dbo',
             '-'
      from syssegments s,
           master.dbo.sysusages u
      where power (2, s.segment) = (u.segmap & power (2, s.segment))
        and u.dbid               = db_id ()
        and 1                    = (select count (s2.segment)
                                    from syssegments s2,
                                         master.dbo.sysusages u2
                                    where u.vstart              = u2.vstart
                                      and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))
        and (not exists (select i.*
                         from sysindexes i
                         where i.segment = s.segment))

   insert into #unused                              -- shared segments
      select s.name,
             'segment',
             'shared',
             'dbo',
             '-'
      from syssegments s,
           master.dbo.sysusages u
      where power (2, s.segment) = (u.segmap & power (2, s.segment))
        and u.dbid               = db_id ()
        and 1                    < (select count (s2.segment)
                                    from syssegments s2,
                                         master.dbo.sysusages u2
                                    where u.vstart              = u2.vstart
                                      and power (2, s2.segment) = (u2.segmap & power (2, s2.segment)))
        and (not exists (select i.*
                         from sysindexes i
                         where i.segment = s.segment))

   if (@sysflag = 0)
      begin
         select @now = convert (char (8), getdate (), 8)

         print '    CHECKING FOR UNUSED SYSTEM TABLES.             (%1!)', @now

         insert into #unused                              -- system tables
            select o.name,
                   substring (vO.name, 1, 13),
                   '-',
                   isnull (user_name (o.uid), '?'),
                   convert (char (8), o.crdate, 11)
            from sysobjects o,
                 sybsystemprocs.dbo.usptvalues vO
            where (o.sysstat & 15) = 1
              and vO.number        = convert (int, o.sysstat & 15)
              and vO.type          = 'O'
              and (not exists (select s.*
                               from #systable s
                               where s.name           = o.name
                                 and (o.sysstat & 15) = 1))
      end

   if (@usertblflag = 0)
      begin
         select @now = convert (char (8), getdate (), 8)

         if ((@pbflag = 0)  and  (@srflag = 0))
            begin
               print '    CHECKING FOR UNUSED USER TABLES.               (%1!)', @now

               insert into #unused                              -- user tables
                  select o.name,
                         'user table',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 3
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
                    and (not exists (select r.*
                                     from sysreferences r
                                     where r.pmrydbid = db_id ()
                                       and r.reftabid = o.id))
            end
         else if (@pbflag = 0)
            begin
               print '    CHECKING FOR UNUSED NON-SUBSCRIPTION TABLES.   (%1!)', @now

               insert into #unused                              -- user tables
                  select o.name,
                         'user table',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 3
                    and o.name not like 'sr[_]%'
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
                    and (not exists (select r.*
                                     from sysreferences r
                                     where r.pmrydbid = db_id ()
                                       and r.reftabid = o.id))
            end
         else if (@srflag = 0)
            begin
               print '    CHECKING FOR UNUSED NON-POWERBUILDER TABLES.   (%1!)', @now

               insert into #unused                              -- user tables
                  select o.name,
                         'user table',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 3
                    and o.name not like 'pb%'
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
                    and (not exists (select r.*
                                     from sysreferences r
                                     where r.pmrydbid = db_id ()
                                       and r.reftabid = o.id))
            end
         else
            begin
               print '    CHECKING FOR UNUSED NON-PB/SR TABLES.          (%1!)', @now

               insert into #unused                              -- user tables
                  select o.name,
                         'user table',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 3
                    and o.name not like 'pb%'
                    and o.name not like 'sr[_]%'
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
                    and (not exists (select r.*
                                     from sysreferences r
                                     where r.pmrydbid = db_id ()
                                       and r.reftabid = o.id))
            end
      end

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

   print '    CHECKING FOR UNUSED USERTYPES.                 (%1!)', @now

   insert into #unused                              -- usertypes (1)
      select t.name,
             'usertype',
             substring (tb.name, 1, 26),
             isnull (user_name (t.uid), '?'),
             '-'
      from systypes t,
           systypes tb
      where t.usertype   > 100
        and tb.type      = t.type
        and tb.usertype in (5, 6, 7, 11, 12, 15, 16, 17, 19, 20, 21, 22, 23)
        and (not exists (select c.*
                         from syscolumns c
                         where c.usertype = t.usertype))

   insert into #unused                              -- usertypes (2)
      select t.name,
             'usertype',
             substring (tb.name  +  ' ('  +  convert (varchar (3), t.length)  +  ')',
                        1, 26),
             isnull (user_name (t.uid), '?'),
             '-'
      from systypes t,
           systypes tb
      where t.usertype   > 100
        and tb.type      = t.type
        and tb.usertype in (1, 2, 3, 4, 8, 13, 14)
        and (not exists (select c.*
                         from syscolumns c
                         where c.usertype = t.usertype))

   insert into #unused                              -- usertypes (3)
      select t.name,
             'usertype',
             substring (tb.name  +  ' ('  +  convert (varchar (3), t.prec)
                        + ', '  +  convert (varchar (3), t.scale) + ')',
                        1, 26),
             isnull (user_name (t.uid), '?'),
             '-'
      from systypes t,
           systypes tb
      where t.usertype   > 100
        and tb.type      = t.type
        and tb.usertype in (10, 26, 29, 28)
        and (not exists (select c.*
                         from syscolumns c
                         where c.usertype = t.usertype))

   update #unused
   set description = substring (description, 1, 5)
   where description = 'image (16)'
      or description = 'text (16)'

   update #unused
   set description = substring (description + ', identity', 1, 33)
   from systypes t
   where #unused.type    = 'usertype'
     and #unused.object  = t.name
     and t.ident         = 1

   update #unused
   set description = substring (description + ', not null', 1, 33)
   from systypes t
   where #unused.type   = 'usertype'
     and #unused.object = t.name
     and t.allownulls   = @bitoff

   update #unused
   set description = substring (description + ', null', 1, 33)
   from systypes t
   where #unused.type   = 'usertype'
     and #unused.object = t.name
     and t.allownulls   = @biton

   if (@viewflag = 0)
      begin
         select @now = convert (char (8), getdate (), 8)

         if (@srflag = 0)
            begin
               print '    CHECKING FOR UNUSED VIEWS.                     (%1!)', @now

               insert into #unused                              -- views
                  select o.name,
                         'view',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 2
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
            end
         else
            begin
               print '    CHECKING FOR UNUSED NON-SUBSCRIPTION VIEWS.    (%1!)', @now

               insert into #unused                              -- views
                  select o.name,
                         'view',
                         '-',
                         isnull (user_name (o.uid), '?'),
                         convert (char (8), o.crdate, 11)
                  from sysobjects o
                  where (o.sysstat & 15) = 2
                    and o.name not like 'sr[_]%'
                    and (not exists (select d.*
                                     from sysdepends d
                                     where d.depid = o.id))
                    and (not exists (select s.*
                                     from #systable s
                                     where s.name     = o.name
                                       and s.type     = 'U'
                                       and db_name () = 'master'))
            end
      end

   print ' '

   if (exists (select *
               from #unused))
      begin
         update #unused
         set description = ltrim (description)

         update #unused
         set object = substring (u.object + ' (' + u.owner + ')', 1, 30)
         from #unused u
         where u.owner <> 'dbo'
            or 1        < (select count (o.uid)
                           from sysobjects o
                           where o.name = u.object)

         update #unused
         set description = substring (description, 2, 30)
         where substring (description, 1, 1) = @LF

         update #unused
         set description = substring (description, 1, charindex (@LF, description) - 1)
         where charindex (@LF, description) > 1

         update #unused
         set description = substring (description, 1, charindex (@CR, description) - 1)
         where charindex (@CR, description) > 1

         select distinct OBJECT        = substring (object, 1, 24),
                         'OBJECT TYPE' = type,
                         DESCRIPTION   = substring (description, 1, 29),
--                       OWNER         = owner,
                         CREATED       = cdate
         from #unused
         order by object, owner
      end
   else
      begin
         print '    ALL CHECKED OBJECTS ARE USED.'
      end
go
sp_addcomment sp_unused, 11, 'Checks for unused objects (with multiple options)'
go
sp_addcomment sp_unused, 11, '   (NULL):    Maximum checking'
go
sp_addcomment sp_unused, 11, '   "PB":      Power Builder tables not checked (default)'
go
sp_addcomment sp_unused, 11, '   "SR":      Subscription tables/views not checked (default)'
go
sp_addcomment sp_unused, 11, '   "SYS":     System tables not checked (default)'
go
sp_addcomment sp_unused, 11, '   "USERTBL": User tables not checked'
go
sp_addcomment sp_unused, 11, '   "VIEW":    Views not checked'
go
sp_procxmode sp_unused, anymode
go

create procedure sp_xactagg (@DETAIL_FLAG varchar (1) = NULL) as
   print ' '

   if ((@@tranchained = 0)  and
       (@@trancount = 0)    and
       (exists (select *
                from master.dbo.sysdatabases
                where dbid             = db_id ()
                  and (status & 1024) <> 1024)))
      begin
         set ansinull                      off              -- Non-ANSI
         set chained                       off              -- Non-ANSI
         set flushmessage                  on
         set nocount                       on
         set string_rtruncation            on
         set transaction isolation level   1                -- Non-ANSI

         create table #syslogs
           (xactid      varbinary (6)  null,
            op          smallint       not null,
            opcount     int            null,
            prntxact    char (17)      null,
            xactid1     smallint       not null,
            xactid2     int            not null,
            sortorder   varbinary (6)  not null)

         if (db_id () = 2)
            begin
               print '    sp_xactagg CANNOT BE RUN FROM tempdb.'
            end
         else
            begin
               if (@DETAIL_FLAG IS NULL)
                  begin
                     insert into #syslogs
                        select xactid, op, 0, NULL, 0, 0, xactid
                        from syslogs

                     insert into #syslogs
                        select xactid, -2, count (*), NULL, 0, 0, xactid
                        from #syslogs
                        group by xactid

                     select distinct 'TRANSACTION COUNT'      = substring (convert (char (21),
                                                                                    convert (money,
                                                                                             count (*)),
                                                                                    1),
                                                                           1,
                                                                           18),
                                     'OPERATIONS/TRANSACTION' = substring (convert (char (25),
                                                                                    convert (money,
                                                                                             opcount),
                                                                                    1),
                                                                           1,
                                                                           22)
                     from #syslogs
                     where op = -2
                     group by opcount
                     having op = -2
                     order by opcount

                     print ' '

                     select 'TOTAL TRANSACTIONS' = substring (convert (char (21),
                                                                       convert (money,
                                                                                count (distinct xactid)),
                                                                       1),
                                                              1,
                                                              18),
                            'TOTAL OPERATIONS'   = substring (convert (char (25),
                                                                       convert (money,
                                                                                sum (opcount)),
                                                                       1),
                                                              1,
                                                              22)
                     from #syslogs
                     where op = -2

                     print ' '

                     select OCCURRENCES = substring (convert (char (21),
                                                              convert (money,
                                                                       count (l.op)),
                                                              1),
                                                     1,
                                                     18),
                            OPERATION   = isnull (substring (vOP.name,
                                                             1,
                                                             58),
                                                  'op = ' + convert (varchar (10), l.op))
                     from #syslogs l,
                          sybsystemprocs.dbo.usptvalues vOP
                     where l.op    <> -2
                       and l.op    *= vOP.number
                       and vOP.type = 'OP'
                     group by l.op
                     having l.op    <> -2
                        and l.op    *= vOP.number
                        and vOP.type = 'OP'
                     order by OCCURRENCES, OPERATION
                  end
               else
                  begin
                     insert into #syslogs
                        select xactid,
                               op,
                               count (op),
                               '     0/         0',
                               convert (int, substring (xactid, 1, 2)),
                               convert (int, substring (xactid, 3, 4)),
                               xactid
                        from syslogs
                        group by xactid, op

                     update #syslogs
                     set prntxact = substring (space (5 - convert (tinyint, log10 (1 + xactid1)))
                                               + convert (varchar (6), xactid1)
                                               + '/'
                                               + space (9 - convert (tinyint, log10 (1 + xactid2)))
                                               + convert (varchar (10), xactid2),
                                               1,
                                               17)
                     where xactid <> 0x00

                     insert into #syslogs
                        select NULL, -2, NULL, NULL, 0, 0, min (xactid)
                        from #syslogs
                        group by xactid

                     delete from #syslogs
                     where xactid    IS NULL
                       and op         = -2
                       and sortorder  = (select min (xactid)
                                         from #syslogs)

                     select XACTID    = isnull (prntxact, ' '),
                            'COUNT'   = isnull (substring (convert (char (10),
                                                                    convert (money,
                                                                             opcount),
                                                                    1),
                                                           1,
                                                           7),
                                                ' '),
                            OPERATION = isnull (substring (vOP.name,
                                                           1,
                                                           51),
                                                'op = ' + convert (varchar (10), l.op) + ')')
                     from #syslogs l,
                          sybsystemprocs.dbo.usptvalues vOP
                     where l.op     *= vOP.number
                       and vOP.type  = 'OP'
                     order by l.sortorder, vOP.name
                  end
            end
      end
   else
      begin
         print '    sp_xactagg CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         print '    @@TRANCHAINED = %1!, @@TRANCOUNT = %2!.', @@tranchained, @@trancount
      end

   print ' '
go
sp_addcomment sp_xactagg, 11, 'Lists transaction log aggregates (option: DETAIL_FLAG)'
go
sp_procxmode sp_xactagg, anymode
go

dump transaction sybsystemprocs with truncate_only
go
