use sybsystemprocs
go
drop procedure sp_alllocks
go
drop procedure sp_blockers
go
drop procedure sp_busy
go
drop procedure sp_cache
go
drop procedure sp_lockagg
go
drop procedure sp_locking
go
drop procedure sp_mirrorright
go
drop procedure sp_remotelogins
go
drop procedure sp_remoteservers
go
drop procedure sp_status
go

set quoted_identifier on
go

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

         print ' '

         return 1

      end

   declare @maxtext      tinyint

   select lk.dbid,
          spid  = isnull (p.spid, 0),
          suid  = isnull (p.suid, 0),
          lk.id,
          lk.type,
          lk.class,
          uname = convert (varchar (30), ' '),
          oname = convert (varchar (30), ' '),
          ltext = convert (varchar (40), lk.class)
   into #alllocks
   from master.dbo.syslocks lk,
        master.dbo.sysprocesses p
   where lk.id    > 0
     and lk.spid *= p.spid
     and lk.spid <> @@spid
     and p.spid  <> @@spid

   insert into #alllocks
      select distinct dbid, spid, suid, 8, 0, ' ', ' ', ' ', 'SUSPENDED!'
      from master.dbo.sysprocesses
      where cmd = 'LOG SUSPEND'

   if (exists (select *
               from #alllocks))
      begin
         update #alllocks
         set uname = substring (isnull (suser_name (suid), '?') + ' (' + convert (varchar (6), spid) + ')', 1, 30),
             oname = isnull (object_name (id, dbid), convert (varchar (11), id))

         update #alllocks
         set ltext = stuff (ltext, charindex ('Non Cursor Lock', ltext), 15, NULL)
         where charindex ('Non Cursor Lock', ltext) > 0

         update #alllocks
         set ltext = substring (ltext + vLK.name, 1, 40)
         from #alllocks l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type   = 'LK'
           and vLK.number = (l.type % 256)

         update #alllocks
         set ltext = substring (ltext + vLK.name, 1, 40)
         from #alllocks l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type             = 'LK'
           and vLK.number           = 256
           and ((l.type / 256) % 2) = 1

         update #alllocks
         set ltext = substring (ltext + vLK.name, 1, 40)
         from #alllocks l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type             = 'LK'
           and vLK.number           = 512
           and ((l.type / 512) % 2) = 1

         update #alllocks
         set ltext = substring (ltext + vLK.name, 1, 40)
         from #alllocks l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type   = 'LK'
           and vLK.number = 768
           and l.type     > 768

         select @maxtext = max (char_length (ltext))
         from #alllocks

         if (@maxtext < 17)
            begin
               select 'DATABASE'  = substring (db_name (dbid), 1, 16),
                      'USER'      = substring (uname, 1, 18),
                      'OBJECT'    = substring (oname, 1, 24),
                      'LOCK TYPE' = substring (ltext, 1, 16)
               from #alllocks
               order by db_name (dbid), suser_name (suid), oname, ltext
            end
         else
            begin
               select 'DATABASE'  = substring (db_name (dbid), 1, 12),
                      'USER'      = substring (uname, 1, 13),
                      'OBJECT'    = substring (oname, 1, 12),
                      'LOCK TYPE' = substring (ltext, 1, 38)
               from #alllocks
               order by db_name (dbid), suser_name (suid), oname, ltext
            end
      end
   else
      begin
         print '    THERE ARE NO LOCKS OTHER THAN IN tempdb FROM THIS EXECUTION.'

         print ' '
      end
go
sp_addcomment sp_alllocks, 16, 'Lists all locks for system (except for sp_alllocks)'
go
sp_procxmode sp_alllocks, anymode
go

create procedure sp_blockers (@DATABASE  varchar (30) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   declare @bcpuflag     int,
           @blockflag    int,
           @cpuflag      int,
           @dbid         smallint,
           @secspertick  decimal (14, 4)

   print ' '

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

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

         print ' '

         return 1
      end

   select @secspertick = convert (decimal (14, 4), @@timeticks) /
                         convert (decimal (14, 4), 1000000)

   if (@DATABASE IS NOT NULL)
      begin
         select @dbid = db_id (@DATABASE)
      end

   select dbid     = p1.dbid,
          blockee  = suser_name (p1.suid)
                     +  ' ('
                     +  convert (varchar (6), p1.spid)
                     +  ')',
          cmd      = p1.cmd,
          cpu      = convert (int, (p1.cpu * @secspertick)),
          block    = p1.time_blocked,
          blocker  = isnull (suser_name (p2.suid), '???')
                     +  ' ('
                     +  convert (varchar (6), p1.blocked)
                     +  ')',
          bcmd     = isnull (p2.cmd, '?'),
          bcpu     = isnull (convert (int, (p2.cpu * @secspertick)), 0)
   into #blockers
   from master.dbo.sysprocesses p1,
        master.dbo.sysprocesses p2
   where p1.blocked <> 0
     and p1.blocked *= p2.spid
     and p1.dbid     = isnull (@dbid, p1.dbid)

   insert into #blockers
      select dbid,
             suser_name (suid),
             cmd,
             convert (int, (cpu * @secspertick)),
             -1,
             '1',
             'LOG SUSPEND',
             convert (int, (cpu * @secspertick))
      from master.dbo.sysprocesses
      where cmd  = 'LOG SUSPEND'
        and dbid = isnull (@dbid, dbid)

   select @bcpuflag  = max (bcpu),
          @blockflag = max (block),
          @cpuflag   = max (cpu)
   from #blockers

   select @bcpuflag  = (@bcpuflag / 255),
          @blockflag = (@blockflag / 255),
          @cpuflag   = (@cpuflag / 255)

   if ((@DATABASE IS NOT NULL)  and  (@dbid IS NULL))
      begin
         print '    DATABASE <%1!> DOES NOT EXIST.', @DATABASE

         print ' '
      end
   else if ((@DATABASE IS NULL)  and  (@blockflag IS NULL))
      begin
         print '    THERE ARE NO BLOCKED PROCESSES.'

         print ' '
      end
   else if ((@DATABASE IS NOT NULL)  and  (@blockflag IS NULL))
      begin
         print '    THERE ARE NO BLOCKED PROCESSES FOR DATABASE <%1!>.',
               @DATABASE

         print ' '
      end
   else if (@DATABASE IS NULL)
      begin
         if ((@bcpuflag = 0)  and  (@blockflag = 0)  and  (@cpuflag = 0))
            begin
               select 'DATABASE' = substring (db_name (dbid), 1, 12),
                      'USER'     = substring (blockee, 1, 11),
                      COMMAND    = substring (cmd, 1, 12),
                      CPU        = convert (tinyint, cpu),
                      BLCK       = convert (tinyint, abs (block)),
                      BLOCKER    = substring (blocker, 1, 11),
                      BCOMMAND   = substring (bcmd, 1, 12),
                      BCPU       = convert (tinyint, bcpu)
               from #blockers
               order by db_name (dbid), blockee, block
            end
         else if ((@bcpuflag > 127)  or  (@blockflag > 127)  or  (@cpuflag > 127))
            begin
               select 'DATABASE'   = substring (db_name (dbid), 1, 9),
                      'USER'       = substring (blockee, 1, 7),
                      COMMAND      = substring (cmd, 1, 7),
                      'CPU TIME'   = cpu,
                      'BLOCK TIME' = substring (convert (char (10), convert (money, block), 1), 1, 7),
                      BLOCKER      = substring (blocker, 1, 7),
                      'B CMD'      = substring (bcmd, 1, 7),
                      'B CPU TIME' = substring (convert (char (11), convert (money, bcpu), 1), 1, 8)
               from #blockers
               order by db_name (dbid), blockee, block
            end
         else
            begin
               select 'DATABASE'  = substring (db_name (dbid), 1, 12),
                      'USER'      = substring (blockee, 1, 10),
                      COMMAND     = substring (cmd, 1, 10),
                      CPU         = convert (smallint, cpu),
                      BLOCK       = substring (convert (char (9), convert (money, block), 1), 1, 6),
                      BLOCKER     = substring (blocker, 1, 10),
                      'B COMMAND' = substring (bcmd, 1, 10),
                      'B CPU'     = substring (convert (char (9), convert (money, bcpu), 1), 1, 6)
               from #blockers
               order by db_name (dbid), blockee, block
            end
      end
   else
      begin
         if ((@bcpuflag = 0)  and  (@blockflag = 0)  and  (@cpuflag = 0))
            begin
               select 'USER'            = substring (blockee, 1, 14),
                      COMMAND           = substring (cmd, 1, 15),
                      CPU               = convert (tinyint, cpu),
                      BLCK              = convert (tinyint, abs (block)),
                      BLOCKER           = substring (blocker, 1, 14),
                      'BLOCKER COMMAND' = substring (bcmd, 1, 15),
                      'B CPU'           = convert (tinyint, bcpu)
               from #blockers
               order by blockee, block
            end
         else if ((@bcpuflag > 127)  or  (@blockflag > 127)  or  (@cpuflag > 127))
            begin
               select 'USER'       = substring (blockee, 1, 10),
                      COMMAND      = substring (cmd, 1, 9),
                      'CPU TIME'   = cpu,
                      'BLOCK TIME' = substring (convert (char (10), convert (money, block), 1), 1, 7),
                      BLOCKER      = substring (blocker, 1, 10),
                      'B COMMAND'  = substring (bcmd, 1, 9),
                      'B CPU TIME' = substring (convert (char (11), convert (money, bcpu), 1), 1, 8)
               from #blockers
               order by blockee, block
            end
         else
            begin
               select 'USER'      = substring (blockee, 1, 14),
                      COMMAND     = substring (cmd, 1, 13),
                      CPU         = convert (smallint, cpu),
                      BLOCK       = substring (convert (char (8), convert (money, block), 1), 1, 5),
                      BLOCKER     = substring (blocker, 1, 13),
                      'B COMMAND' = substring (bcmd, 1, 13),
                      'B CPU'     = substring (convert (char (8), convert (money, bcpu), 1), 1, 5)
               from #blockers
               order by blockee, block
            end
      end
go
sp_addcomment sp_blockers, 16, 'Lists blockee/blocker for DATABASE or (default) all databases'
go
sp_procxmode sp_blockers, anymode
go

create procedure sp_busy (@SAMPLES smallint = 4,
                          @PAUSE   tinyint  = 5)  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @now          varchar (8),
           @samplecnt    smallint,
           @secspertick  decimal (14, 4),
           @seq          smallint


   print '    DISK AND I/O VALUES REPRESENT CHANGES FROM THE PREVIOUS VALUE.'

   print ' '

   set transaction isolation level 1

   if (@SAMPLES < 1)
      begin
         select @samplecnt = 4
      end
   else
      begin
         select @samplecnt = @SAMPLES
      end

   if (@PAUSE < 4)
      begin
         print '    THERE WILL BE %1! SAMPLES WITH A PAUSE OF 3 SECONDS.', @samplecnt
      end
   else if (@PAUSE < 6)
      begin
         print '    THERE WILL BE %1! SAMPLES WITH A PAUSE OF 5 SECONDS.', @samplecnt
      end
   else if (@PAUSE < 11)
      begin
         print '    THERE WILL BE %1! SAMPLES WITH A PAUSE OF 10 SECONDS.', @samplecnt
      end
   else if (@PAUSE < 16)
      begin
         print '    THERE WILL BE %1! SAMPLES WITH A PAUSE OF 15 SECONDS.', @samplecnt
      end
   else if (@PAUSE < 21)
      begin
         print '    THERE WILL BE %1! SAMPLES WITH A PAUSE OF 20 SECONDS.', @samplecnt
      end
   else if (@PAUSE < 31)
      begin
         print '    THERE WILL BE %1! SAMPLES WITH A PAUSE OF 30 SECONDS.', @samplecnt
      end
   else
      begin
         print '    THERE WILL BE %1! SAMPLES WITH A PAUSE OF 1 MINUTE.', @samplecnt
      end

   create table #tmp
          (seq             smallint   not null,
           spid            smallint   not null,
           suid            smallint   not null,
           hostname        char (10)  not null,
           program_name    char (16)  not null,
           cmd             char (16)  not null,
           cpu             int        not null,
           physical_io     int        not null,
           blocked         smallint   not null,
           dbid            smallint   not null,
           time_blocked    int        null)

   select @secspertick = convert (decimal (14, 4), @@timeticks) /
                         convert (decimal (14, 4), 1000000),
          @seq         = 0

   while (@seq <= @samplecnt)
      begin
         insert into #tmp
            select @seq,
                   spid,
                   suid,
                   hostname,
                   program_name,
                   cmd,
                   convert (int, cpu * @secspertick),
                   convert (int, physical_io * @secspertick),
                   convert (int, blocked * @secspertick),
                   dbid,
                   time_blocked
            from master.dbo.sysprocesses
            where spid     <> @@spid
              and cmd  not in ('CHECKPOINT SLEEP',
                               'DEADLOCK TUNNEL',
                               'HOUSEKEEPER',
                               'MIRROR HANDLER',
                               'NETWORK HANDLER')

         if (exists (select n.seq
                     from #tmp o, #tmp n
                     where o.seq     = @seq - 1
                       and n.seq     = @seq
                       and n.spid    = o.spid
                       and n.suid    = o.suid
                       and n.blocked > 0))
            begin
               select @now = convert (char (8), getdate(), 8)

               print ' '

               print ' %1!', @now

               print ' '

               select 'DATABASE' = substring (db_name (n.dbid), 1, 10),
                      'USER'     = isnull (convert (char (10), suser_name (n.suid)), '-'),
                      CLIENT     = n.hostname,
                      PROGRAM    = substring (n.program_name, 1, 13),
                      SPID       = n.spid,
                      'CPU sec'  = substring (convert (char (10), convert (money, ((n.cpu - o.cpu) % 32767)), 1), 1, 7),
                      'I/O''s'   = substring (convert (char (9), convert (money, ((n.physical_io - o.physical_io) % 32767)), 1), 1, 6),
                      'BLK sec'  = isnull (substring (convert (char (10), convert (money, ((n.time_blocked - o.time_blocked) % 32767)), 1), 1, 7), '      0')
               from #tmp o, #tmp n
               where o.seq     = @seq - 1
                 and n.seq     = @seq
                 and n.spid    = o.spid
                 and n.suid    = o.suid
                 and ((n.physical_io > o.physical_io)  or
                      (n.cpu         > o.cpu)          or
                      (n.cmd         = 'LOG SUSPEND')  or
                      (n.blocked     > 0))
               order by db_name (n.dbid), suser_name (n.suid), n.spid
            end
         else if (exists (select n.seq
                          from #tmp o, #tmp n
                          where o.seq  = @seq - 1
                            and n.seq  = @seq
                            and n.spid = o.spid
                            and n.suid = o.suid
                            and ((n.physical_io > o.physical_io)  or
                                 (n.cpu         > o.cpu)          or
                                 (n.cmd         = 'LOG SUSPEND'))))
            begin
               select @now = convert (char (8), getdate(), 8)

               print ' '

               print ' %1!', @now

               print ' '

               select 'DATABASE' = substring (db_name (n.dbid), 1, 13),
                      'USER'     = isnull (convert (char (13), suser_name (n.suid)), '-'),
                      CLIENT     = n.hostname,
                      PROGRAM    = n.program_name,
                      SPID       = n.spid,
                      'CPU sec'  = substring (convert (char (10), convert (money, ((n.cpu - o.cpu) % 32767)), 1), 1, 7),
                      'I/O''s'   = substring (convert (char (9), convert (money, ((n.physical_io - o.physical_io) % 32767)), 1), 1, 6)
               from #tmp o, #tmp n
               where o.seq  = @seq - 1
                 and n.seq  = @seq
                 and n.spid = o.spid
                 and n.suid = o.suid
                 and ((n.physical_io > o.physical_io)  or
                      (n.cpu         > o.cpu)          or
                      (n.cmd         = 'LOG SUSPEND'))
               order by db_name (n.dbid), suser_name (n.suid), n.spid
            end
         else if (@seq = 0)
            begin
               print ' '
            end
         else
            begin
               print ' '

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

               print ' %1! - NO PROCESSING CHANGES', @now

               print ' '
            end

         if (@PAUSE < 4)
            begin
               waitfor delay '00:00:03'
            end
         else if (@PAUSE < 6)
            begin
               waitfor delay '00:00:05'
            end
         else if (@PAUSE < 11)
            begin
               waitfor delay '00:00:10'
            end
         else if (@PAUSE < 16)
            begin
               waitfor delay '00:00:15'
            end
         else if (@PAUSE < 21)
            begin
               waitfor delay '00:00:20'
            end
         else if (@PAUSE < 31)
            begin
               waitfor delay '00:00:30'
            end
         else
            begin
               waitfor delay '00:01:00'
            end

         delete from #tmp
         where seq = @seq - 1

         select @seq = @seq + 1
      end
go
sp_addcomment sp_busy, 16, 'Lists processes whose status''s are changing'
go
sp_addcomment sp_busy, 16, '   SAMPLE (default 4) indicates how many samples'
go
sp_addcomment sp_busy, 16, '   PAUSE (default 5) seconds between samples'
go
sp_procxmode sp_busy, anymode
go

create procedure sp_cache (@CACHENAME  varchar (120) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @dbname        varchar (30),
           @maxobjname    tinyint,
           @maxcachename  tinyint

   create table #cache1
      (name       varchar (120)  not null,
       status     smallint       not null,
       stattxt    char (9)       not null,
       typetxt    char (8)       not null,
       size       int            null,
       overhead   int            null,
       size_cfg   int            not null)

   create table #cache2
      (name       varchar (48)   not null,
       iosize     char (8)       not null,
       washsize   int            not null,
       poolsize   int            not null,
       sort       varchar (56)   not null)

   create table #cache3
      (objid      int            not null,
       objname    varchar (94)   not null,
       type       char (10)      not null,
       cachename  varchar (120)  not null,
       indid      tinyint        not null,
       pages      int            not null)

   insert into #cache1
      select cf.name,
             cf.status,
             ' ',
             ' ',
             (cc.value / 2),
             (cc.memory_used / 2),
             (cf.value / 2)
      from master.dbo.sysconfigures cf,
           master.dbo.syscurconfigs cc
      where cf.parent  = 19
        and cf.config  = 19
        and cf.name    = isnull (@CACHENAME, cf.name)
        and cc.config  = 19
        and cc.comment = cf.name

   insert into #cache1
      select name, status, ' ', ' ', NULL, NULL, (value / 2)
      from master.dbo.sysconfigures
      where parent        = 19
        and name          = isnull (@CACHENAME, name)
        and (status & 64) = 64

   if (exists (select *
               from #cache1))
      begin
         update #cache1
         set typetxt = 'Default'
         where (status & 1) = 1

         update #cache1
         set typetxt = 'Data/Log'
         where (status & 2) = 2

         update #cache1
         set typetxt = 'Log'
         where (status & 4) = 4

         update #cache1
         set stattxt = 'Active   '
         where (status & 32) = 32

         update #cache1
         set stattxt = 'Inactive '
         where (status & 64) = 64

         update #cache1
         set stattxt = 'Actv/Del '
         where (status & 128) = 128

         select @maxcachename = max (char_length (name))
         from #cache1

         if (exists (select *
                     from #cache1
                     where (status & 64) = 64))
            begin
               if (@maxcachename < 21)
                  begin
                     select 'CACHE NAME'  = substring (name, 1, 20),
                            TYPE          = typetxt,
                            STATUS        = stattxt,
                            SIZE          = isnull (substring (convert (char (12), convert (money, size), 1), 1, 9), '      N/A'),
                            OVERHEAD      = isnull (substring (convert (char (11), convert (money, overhead), 1), 1, 8), '     N/A'),
                            'CONFIG SIZE' = substring (convert (char (14), convert (money, size_cfg), 1), 1, 11)
                     from #cache1
                     order by name
                  end
               else if (@maxcachename < 28)
                  begin
                     select 'CACHE NAME'  = substring (name, 1, 27),
                            TYPE          = typetxt,
                            STATUS        = stattxt,
                            SIZE          = isnull (substring (convert (char (12), convert (money, size), 1), 1, 9), '      N/A'),
                            OVERHEAD      = isnull (substring (convert (char (11), convert (money, overhead), 1), 1, 8), '     N/A'),
                            'CONFIG SIZE' = substring (convert (char (14), convert (money, size_cfg), 1), 1, 11)
                     from #cache1
                     order by name
                  end
               else
                  begin
                     select 'CACHE NAME'  = substring (name, 1, 48),
                            TYPE          = typetxt,
                            STATUS        = stattxt,
                            SIZE          = isnull (substring (convert (char (12), convert (money, size), 1), 1, 9), '      N/A'),
                            OVERHEAD      = isnull (substring (convert (char (11), convert (money, overhead), 1), 1, 8), '     N/A'),
                            'CONFIG SIZE' = substring (convert (char (14), convert (money, size_cfg), 1), 1, 11)
                     from #cache1
                     order by name
                  end
            end
         else
            begin
               if (@maxcachename < 21)
                  begin
                     select 'CACHE NAME'  = substring (name, 1, 20),
                            TYPE          = typetxt,
                            STATUS        = stattxt,
                            SIZE          = isnull (substring (convert (char (12), convert (money, size), 1), 1, 9), '      N/A'),
                            OVERHEAD      = isnull (substring (convert (char (11), convert (money, overhead), 1), 1, 8), '     N/A')
                     from #cache1
                     order by name
                  end
               else if (@maxcachename < 28)
                  begin
                     select 'CACHE NAME'  = substring (name, 1, 27),
                            TYPE          = typetxt,
                            STATUS        = stattxt,
                            SIZE          = isnull (substring (convert (char (12), convert (money, size), 1), 1, 9), '      N/A'),
                            OVERHEAD      = isnull (substring (convert (char (11), convert (money, overhead), 1), 1, 8), '     N/A')
                     from #cache1
                     order by name
                  end
               else
                  begin
                     select 'CACHE NAME'  = substring (name, 1, 48),
                            TYPE          = typetxt,
                            STATUS        = stattxt,
                            SIZE          = isnull (substring (convert (char (12), convert (money, size), 1), 1, 9), '      N/A'),
                            OVERHEAD      = isnull (substring (convert (char (11), convert (money, overhead), 1), 1, 8), '     N/A')
                     from #cache1
                     order by name
                  end
            end

         print ' '

         insert into #cache2
            select substring (c.name, 1, 48),
                   space (6) + substring (cf.comment, 1, 2),
                   (cc.memory_used / 2),
                   (cc.value / 2),
                   substring (c.name, 1, 54) + ' ' + substring (cf.comment, 1, 2)
            from #cache1 c,
                 master.dbo.sysconfigures cf,
                 master.dbo.syscurconfigs cc
            where cf.name                      like '%' + c.name + '%'
              and cf.comment                   like '%Buffer Pool'
              and cf.parent                       = 19
              and substring (cf.comment, 3, 1)    = ' '
              and cc.config                       = cf.config
              and cc.comment                      = cf.name

         insert into #cache2
            select substring (c.name, 1, 48),
                   space (5) + substring (cf.comment, 1, 3),
                   (cc.memory_used / 2),
                   (cc.value / 2),
                   substring (c.name, 1, 53) +  substring (cf.comment, 1, 3)
            from #cache1 c,
                 master.dbo.sysconfigures cf,
                 master.dbo.syscurconfigs cc
            where cf.name                      like '%' + c.name + '%'
              and cf.comment                   like '%Buffer Pool'
              and cf.parent                       = 19
              and substring (cf.comment, 4, 1)    = ' '
              and cc.config                       = cf.config
              and cc.comment                      = cf.name

         update #cache2
         set name = ' '
         from #cache2 c
         where exists (select *
                       from #cache2 c2
                       where c2.name   = c.name
                         and c2.iosize = c.iosize
                         and c2.iosize > (select min (c3.iosize)
                                          from #cache2 c3
                                          where c3.name = c2.name))

         select @maxcachename = max (char_length (name))
         from #cache2

         if (@maxcachename < 21)
            begin
               select 'CACHE NAME' = substring (name, 1, 20),
                      'I/O SIZE'   = iosize,
                      'WASH SIZE'  = substring (convert (char (12), convert (money, washsize), 1), 1, 9),
                      'POOL SIZE'  = substring (convert (char (12), convert (money, poolsize), 1), 1, 9)
               from #cache2
               order by sort
            end
         else if (@maxcachename < 28)
            begin
               select 'CACHE NAME' = substring (name, 1, 27),
                      'I/O SIZE'   = iosize,
                      'WASH SIZE'  = substring (convert (char (12), convert (money, washsize), 1), 1, 9),
                      'POOL SIZE'  = substring (convert (char (12), convert (money, poolsize), 1), 1, 9)
               from #cache2
               order by sort
            end
         else
            begin
               select 'CACHE NAME' = name,
                      'I/O SIZE'   = iosize,
                      'WASH SIZE'  = substring (convert (char (12), convert (money, washsize), 1), 1, 9),
                      'POOL SIZE'  = substring (convert (char (12), convert (money, poolsize), 1), 1, 9)
               from #cache2
               order by sort
            end

         print ' '

         if (exists (select *
                     from sysattributes
                     where class      = 3
                       and char_value = isnull (@CACHENAME, char_value)
                       and attribute  = 0))
            begin
               insert into #cache3
                  select object, db_name (object), 'Database', char_value, 0, 0
                  from sysattributes
                  where class       = 3
                    and attribute   = 0
                    and object_type = 'D'
                    and char_value  = isnull (@CACHENAME, char_value)

               insert into #cache3
                  select a.object, object_name (a.object) + '.' + i.name, 'Index (' + convert (varchar (3), i.indid) + ')', a.char_value, i.indid, 0
                  from sysattributes a,
                       sysindexes i
                  where a.class       = 3
                    and a.attribute   = 0
                    and a.object_type = 'I'
                    and a.char_value  = isnull (@CACHENAME, a.char_value)
                    and i.id          = a.object
                    and i.indid       = a.object_info1
                    and i.indid       < 255

               insert into #cache3
                  select object, object_name (object), 'Log', char_value, 0, 0
                  from sysattributes
                  where class       = 3
                    and attribute   = 0
                    and object_type = 'T'
                    and object      = 8
                    and char_value  = isnull (@CACHENAME, char_value)

               insert into #cache3
                  select a.object, object_name (a.object), 'Table (' + convert (varchar (3), i.indid) + ')', a.char_value, 0, 0
                  from sysattributes a,
                       sysindexes i
                  where a.class        = 3
                    and a.attribute    = 0
                    and a.object_type  = 'T'
                    and a.object      <> 8
                    and a.char_value   = isnull (@CACHENAME, a.char_value)
                    and i.id           = a.object
                    and i.indid       in (0, 1)

               insert into #cache3
                  select object, object_name (object), 'Image/Text', char_value, 0, 0
                  from sysattributes
                  where class        = 3
                    and attribute    = 0
                    and object_info1 > 254
                    and char_value   = isnull (@CACHENAME, char_value)

               update #cache3
               set objname = c.objname + ' (' + user_name (o.uid) + ')'
               from #cache3 c,
                    sysobjects o
               where ((c.type =    'Image/Text')  or
                      (c.type like 'Table (%'))
                 and o.id    = c.objid
                 and ((o.uid <> 1)  or
                      (1      < (select count (o2.uid)
                                 from sysobjects o2
                                 where o2.name            = c.objname
                                   and (o2.sysstat & 15) in (2, 3))))

               update #cache3
               set objname = c.objname + ' (' + user_name (o.uid) + ')'
               from #cache3 c,
                    sysobjects o
               where c.type like 'Index%'
                 and o.id      = c.objid
                 and ((o.uid <> 1)  or
                      (1      < (select count (o2.uid)
                                 from sysobjects o2
                                 where o2.name            = substring (c.objname, 1, charindex ('.', c.objname) - 1)
                                   and (o2.sysstat & 15) in (2, 3))))

               update #cache3
               set pages = (select sum (u.size)
                            from master.dbo.sysusages u
                            where u.dbid = c.objid)
               from #cache3 c
               where c.type = 'Database'

               update #cache3
               set pages = (select data_pgs (i.id, i.ioampg)
                            from sysindexes i
                            where i.id    = c.objid
                              and i.indid = c.indid)
               from #cache3 c
               where c.type like 'Index%'

               update #cache3
               set pages = (select data_pgs (i.id, i.doampg)
                            from sysindexes i
                            where i.id    = c.objid
                              and i.indid = 0)
               from #cache3 c
               where c.type = 'Log'

               update #cache3
               set pages = (select data_pgs (i.id, i.doampg)
                            from sysindexes i
                            where i.id    = c.objid
                              and i.indid < 2)
               from #cache3 c
               where c.type like 'Table%'

               select @maxcachename = max (char_length (cachename)),
                      @maxobjname   = max (char_length (objname))
               from #cache3

               if ((@maxobjname > 33)  and  (@maxcachename < 23))
                  begin
                     select 'CACHE NAME'  = substring (cachename, 1, 10),
                            TYPE          = type,
                            'OBJECT NAME' = substring (objname, 1, 43),
                            PAGES         = substring (convert (char (14),
                                                                convert (money,
                                                                         pages),
                                                                1),
                                                       1,
                                                       11)
                     from #cache3
                     order by object_name (objid), substring (type, 1, 6) desc, indid
                  end
               else
                  begin
                     select 'CACHE NAME'  = substring (cachename, 1, 20),
                            TYPE          = type,
                            'OBJECT NAME' = substring (objname, 1, 33),
                            PAGES         = substring (convert (char (14),
                                                                convert (money,
                                                                         pages),
                                                                1),
                                                       1,
                                                       11)
                     from #cache3
                     order by object_name (objid), substring (type, 1, 6) desc, indid
                  end
            end
         else
            begin
               select @dbname = ltrim (db_name ())

               if (@CACHENAME IS NULL)
                  begin
                     print '    THERE ARE NO OBJECTS IN <%1!> BOUND TO A CACHE.', @dbname

                     print ' '
                  end
               else
                  begin
                     print '    THERE ARE NO OBJECTS IN <%1!> BOUND TO CACHE <%2!>.',
                           @dbname, @CACHENAME

                     print ' '
                  end
            end
      end
   else if (@CACHENAME IS NULL)
      begin
         print '    THERE ARE NO CACHES.'

         print ' '
      end
   else
      begin
         print '    THERE IS NO CACHE <%1!>.', @CACHENAME

         print ' '
      end
go
sp_addcomment sp_cache, 16, 'Displays basic data for (default) all caches or CACHE'
go
sp_procxmode sp_cache, anymode
go

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

         print ' '

         return 1
      end

   declare @maxdblen   tinyint,
           @maxtablen  tinyint

   select distinct dbid    = lk.dbid,
                   id      = lk.id,
                   type    = lk.type,
                   class   = lk.class,
                   C       = count (lk.id),
                   tabname = convert (varchar (30), ' '),
                   ltext   = convert (varchar (40), lk.class),
                   Ctext   = convert (varchar (10), ' ')
   into #lockagg
   from master.dbo.syslocks lk,
        master.dbo.sysprocesses p
   where lk.id    > 0
     and lk.spid *= p.spid
     and lk.spid <> @@spid
     and p.spid  <> @@spid
   group by lk.dbid, lk.id, lk.type, lk.class
   having lk.id    > 0
      and lk.spid *= p.spid
      and lk.spid <> @@spid
      and p.spid  <> @@spid

   if (exists (select *
               from #lockagg))
      begin
         update #lockagg
         set tabname = isnull (object_name (id, dbid), convert (varchar (11), id))

         update #lockagg
         set ltext = stuff (ltext, charindex ('Non Cursor Lock', ltext), 15, NULL)
         where charindex ('Non Cursor Lock', ltext) > 0

         update #lockagg
         set ltext = substring (ltext + vLK.name, 1, 40)
         from #lockagg l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type   = 'LK'
           and vLK.number = (l.type % 256)

         update #lockagg
         set ltext = substring (ltext + vLK.name, 1, 40)
         from #lockagg l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type             = 'LK'
           and vLK.number           = 256
           and ((l.type / 256) % 2) = 1

         update #lockagg
         set ltext = substring (ltext + vLK.name, 1, 40)
         from #lockagg l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type             = 'LK'
           and vLK.number           = 512
           and ((l.type / 512) % 2) = 1

         update #lockagg
         set ltext = substring (ltext + vLK.name, 1, 40)
         from #lockagg l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type   = 'LK'
           and vLK.number = 768
           and l.type     > 768

         update #lockagg
         set Ctext = convert (char (10), convert (money, C), 1)

         select @maxdblen  = max (char_length (db_name (dbid))),
                @maxtablen = max (char_length (tabname))
         from #lockagg

         if (@maxdblen < 16)
            begin
               if (@maxtablen < 16)
                  begin
                     select 'DATABASE' = substring (db_name (dbid), 1, 15),
                            'TABLE'    = substring (tabname, 1, 15),
                            'TYPE'     = substring (ltext, 1, 37),
                            'COUNT'    = substring (Ctext, 1, 7)
                     from #lockagg
                     order by db_name (dbid), tabname, ltext
                  end
               else
                  begin
                     select 'DATABASE' = substring (db_name (dbid), 1, 15),
                            'TABLE'    = substring (tabname, 1, 23),
                            'TYPE'     = substring (ltext, 1, 29),
                            'COUNT'    = substring (Ctext, 1, 7)
                     from #lockagg
                     order by db_name (dbid), tabname, ltext
                  end
            end
         else
            begin
               if (@maxtablen < 16)
                  begin
                     select 'DATABASE' = substring (db_name (dbid), 1, 20),
                            'TABLE'    = substring (tabname, 1, 15),
                            'TYPE'     = substring (ltext, 1, 32),
                            'COUNT'    = substring (Ctext, 1, 7)
                     from #lockagg
                     order by db_name (dbid), tabname, ltext
                  end
               else
                  begin
                     select 'DATABASE' = substring (db_name (dbid), 1, 20),
                            'TABLE'    = substring (tabname, 1, 23),
                            'TYPE'     = substring (ltext, 1, 24),
                            'COUNT'    = substring (Ctext, 1, 7)
                     from #lockagg
                     order by db_name (dbid), tabname, ltext
                  end
            end
      end
   else
      begin
         print '    THERE ARE NO LOCKS OTHER THAN IN tempdb FROM THIS EXECUTION.'

         print ' '
      end
go
sp_addcomment sp_lockagg, 16, 'Lists agggregate lock data for system (except for sp_lockagg)'
go
sp_procxmode sp_lockagg, anymode
go

create procedure sp_locking (@DATABASE varchar (30) = null) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @dbid     smallint,
           @dbname   varchar (30),
           @maxpage  int,
           @maxtext  tinyint

   if (@DATABASE IS NULL)
      begin
         select @dbid   = db_id (),
                @dbname = db_name ()
      end
   else
      begin
         select @dbid   = db_id (@DATABASE),
                @dbname = @DATABASE
      end

   select suid  = isnull (p.suid, -1),
          lk.spid,
          lk.id,
          lk.page,
          lk.type,
          lk.class,
          uname = convert (varchar (30), ' '),
          oname = convert (varchar (30), ' '),
          ltext = convert (varchar (47), lk.class)
   into #locking
   from master.dbo.syslocks lk,
        master.dbo.sysprocesses p
   where lk.dbid  = @dbid
     and lk.spid *= p.spid

   if (@dbid IS NULL)
      begin
         print '    DATABASE <%1!> DOES NOT EXIST.', @DATABASE

         print ' '
      end
   else if (exists (select *
                    from #locking))
      begin
         update #locking
         set uname = substring (isnull (suser_name (suid), '?') + ' (' + convert (varchar (6), spid) + ')', 1, 30),
             oname = object_name (id, @dbid),
             ltext = stuff (ltext, charindex ('Non Cursor Lock', ltext), 15, NULL)

         update #locking
         set ltext = substring (ltext + vLK.name, 1, 47)
         from #locking l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type   = 'LK'
           and vLK.number = (l.type % 256)

         update #locking
         set ltext = substring (ltext + vLK.name, 1, 47)
         from #locking l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type             = 'LK'
           and vLK.number           = 256
           and ((l.type / 256) % 2) = 1

         update #locking
         set ltext = substring (ltext + vLK.name, 1, 47)
         from #locking l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type             = 'LK'
           and vLK.number           = 512
           and ((l.type / 512) % 2) = 1

         update #locking
         set ltext = substring (ltext + vLK.name, 1, 47)
         from #locking l,
              sybsystemprocs.dbo.usptvalues vLK
         where vLK.type           = 'LK'
           and vLK.number         = 768
           and l.type             > 768

         select @maxpage = max (page),
                @maxtext = max (char_length (ltext))
         from #locking

         if (@maxpage < 266)
            begin
               if (@maxtext < 17)
                  begin
                     select 'USER'      = substring (uname, 1, 27),
                            'OBJECT'    = substring (oname, 1, 28),
                            PG          = convert (tinyint, page),
                            'LOCK TYPE' = substring (ltext, 1, 16)
                     from #locking
                     order by suser_name (suid), spid, oname, page
                  end
               else
                  begin
                     select 'USER'      = substring (uname, 1, 12),
                            'OBJECT'    = substring (oname, 1, 12),
                            PG          = convert (tinyint, page),
                            'LOCK TYPE' = ltext
                     from #locking
                     order by suser_name (suid), spid, oname, page
                  end
            end
         else if (@maxpage < 32768)
            begin
               if (@maxtext < 17)
                  begin
                     select 'USER'      = substring (uname, 1, 26),
                            'OBJECT'    = substring (oname, 1, 26),
                            PAGE        = convert (smallint, page),
                            'LOCK TYPE' = substring (ltext, 1, 16)
                     from #locking
                     order by suser_name (suid), spid, oname, page
                  end
               else
                  begin
                     select 'USER'      = substring (uname, 1, 12),
                            'OBJECT'    = substring (oname, 1, 12),
                            PAGE        = convert (smallint, page),
                            'LOCK TYPE' = substring (ltext, 1, 44)
                     from #locking
                     order by suser_name (suid), spid, oname, page
                  end
            end
         else
            begin
               if (@maxtext < 17)
                  begin
                     select 'USER'      = substring (uname, 1, 23),
                            'OBJECT'    = substring (oname, 1, 24),
                            PAGE        = page,
                            'LOCK TYPE' = substring (ltext, 1, 16)
                     from #locking
                     order by suser_name (suid), spid, oname, page
                  end
               else
                  begin
                     select 'USER'      = substring (uname, 1, 12),
                            'OBJECT'    = substring (oname, 1, 12),
                            PAGE        = page,
                            'LOCK TYPE' = substring (ltext, 1, 39)
                     from #locking
                     order by suser_name (suid), spid, oname, page
                  end
            end
      end
   else
      begin
         print '    THERE ARE NO LOCKS FOR <%1!>.', @dbname

         print ' '
      end
go
sp_addcomment sp_locking, 16, 'Lists all locks for DATABASE or (default) current database'
go
sp_procxmode sp_locking, anymode
go

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

         print ' '

         return 1
      end

   if (exists (select *
               from master.dbo.sysdevices
               where cntrltype = 0
                 and (((status > 31)  and  (status & 512 <> 512))  or
                      ((status < 32)  and  ((status > 3)  or  (mirrorname IS NOT NULL))))))
      begin
         print '    AT LEAST ONE DATABASE DEVICE HAS AN INCORRECT STATUS.'

         print ' '

         select 'DISABLED MIRROR' = substring (dv.name, 1, 16),
                STATUSES          = substring (vV.name, 1, 16),
                PHYSICAL          = substring (dv.phyname, 1, 21),
                'MIRROR'          = substring (dv.mirrorname, 1, 21)
         from master.dbo.sysdevices dv,
              sybsystemprocs.dbo.usptvalues vV
         where dv.cntrltype = 0
           and vV.type      = 'V'
           and vV.number    = (dv.status & vV.number)
           and (((dv.status > 31)  and  (dv.status & 512 <> 512))  or
                ((dv.status < 32)  and  ((dv.status > 3)  or  (dv.mirrorname IS NOT NULL))))
         order by dv.name, vV.number
      end
   else
      begin
         print '    ALL DATABASE DEVICES ARE MIRRORED/UNMIRRORED CONSISTENTLY.'

         print ' '
      end

   if (1 < any (select count (distinct (dv.status - (dv.status % 2)))
                from master.dbo.sysdevices dv,
                     master.dbo.sysusages  u
                where dv.cntrltype  = 0
                  and u.dbid        = u.dbid
                  and dv.low       <= u.vstart + u.size
                  and dv.high      >= u.vstart + u.size - 1
                group by u.dbid, u.segmap
                having dv.cntrltype  = 0
                  and u.dbid         = u.dbid
                  and dv.low        <= u.vstart + u.size
                  and dv.high       >= u.vstart + u.size - 1))
      begin
         print '    AT LEAST ONE DATABASE/LOG IS SPLIT ON UNLIKE DEVICES.'

         print ' '

         create table #mrdv
            (low    int          not null,
             high   int          not null,
             name   varchar (16) not null,
             number smallint     not null)

         create table #mru
            (dbid   smallint     not null,
             segmap int          not null,
             size   int          not null,
             vstart int          not null,
             dbname varchar (18) not null,
             type   char (13)    not null,
             pages  char (11)    not null)

         insert into #mrdv
            select dv.low, dv.high, dv.name, max (vV.number)
            from master.dbo.sysdevices dv,
                 sybsystemprocs.dbo.usptvalues vV
            where dv.cntrltype = 0
              and vV.type      = 'V'
              and vV.number   in (2, 64, 128, 256, 512)
              and vV.number    = (dv.status & vV.number)
            group by dv.name
            having dv.cntrltype = 0
               and vV.type      = 'V'
               and vV.number   in (2, 64, 128, 256, 512)
               and vV.number    = (dv.status & vV.number)

         insert into #mru
            select u.dbid,
                   u.segmap,
                   u.size,
                   u.vstart,
                   db_name (u.dbid),
                   vS.name,
                   substring (convert (char (14), convert (money, u.size), 1), 1, 11)
            from master.dbo.sysusages u,
                 sybsystemprocs.dbo.usptvalues vS
            where vS.type   = 'S'
              and vS.number = (u.segmap & 7)
              and 1         < (select count (distinct (dv2.status - (dv2.status % 2)))
                               from master.dbo.sysdevices dv2,
                                    master.dbo.sysusages  u2
                               where dv2.cntrltype    = 0
                                 and u2.dbid          = u.dbid
                                 and (u2.segmap & 7)  = (u.segmap & 7)
                                 and dv2.low         <= u2.vstart + u2.size
                                 and dv2.high        >= u2.vstart + u2.size -1)

         select distinct 'SPLIT MIRRORED DB' = #mru.dbname,
                         TYPE                = #mru.type,
                         DEVICE              = #mrdv.name,
                         'HIGHEST STATUS'    = substring (vV.name, 1, 14),
                         '      PAGES'       = #mru.pages
         from #mru,
              #mrdv,
              sybsystemprocs.dbo.usptvalues vV
         where vV.type     = 'V'
           and vV.number   = #mrdv.number
           and #mrdv.low  <= #mru.vstart + #mru.size
           and #mrdv.high >= #mru.vstart + #mru.size - 1
         order by #mru.dbname, vV.name desc, #mrdv.name
      end
   else
      begin
         print '    NO DATABASE/LOG IS SPLIT ON MIRRORED/UNMIRRORED DEVICES.'

         print ' '
      end
go
sp_addcomment sp_mirrorright, 16, 'Checks for disabled mirrors and split mirrored/unmirrored DB''s'
go
sp_procxmode sp_mirrorright, anymode
go

create procedure sp_remotelogins (@REMOTESERVER varchar (30) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @existsflag  tinyint,
           @srvid       smallint

   create table #remotelogins
      (server   varchar (30)  not null,
       sname    varchar (30)  null,
       lname    varchar (30)  not null,
       status   smallint      not null,
       text     varchar (35)  null)

   insert into #remotelogins
      select s.srvname,
             '(other)',
             '(remotename)',
             r.status,
             'untrustworthy'
      from master.dbo.sysremotelogins r,
           master.dbo.sysservers      s
      where s.srvname         = isnull (@REMOTESERVER, s.srvname)
        and r.remoteserverid  = s.srvid
        and r.remoteusername IS NULL
        and r.suid            = -1

   insert into #remotelogins
      select s.srvname,
             isnull (r.remoteusername, '(other)'),
             l.name,
             r.status,
             'untrustworthy'
      from master.dbo.syslogins       l,
           master.dbo.sysremotelogins r,
           master.dbo.sysservers      s
      where s.srvname        = isnull (@REMOTESERVER, s.srvname)
        and r.remoteserverid = s.srvid
        and r.suid           = l.suid

   update #remotelogins
   set text = 'trustworthy'
   where status = 1

   select @existsflag = 0

   select @existsflag = 1
   where exists (select *
                 from #remotelogins)

   if ((@REMOTESERVER IS NULL)  and  (@existsflag = 1))
      begin
         select 'REMOTE SERVER.NAME' = substring (server + '.' + sname, 1, 35),
                'LOCAL NAME'         = substring (lname, 1, 25),
                STATUS               = substring (text, 1, 15)
         from #remotelogins
         order by server, sname
      end
   else if (@REMOTESERVER IS NULL)
      begin
         print '    NO REMOTE LOGINS ARE DEFINED.'

         print ' '
      end
   else if (@existsflag = 1)
      begin
         select 'REMOTE NAME' = sname,
                'LOCAL NAME'  = lname,
                STATUS        = substring (text, 1, 15)
         from #remotelogins
         order by sname
      end
   else
      begin
         select @srvid = srvid
         from master.dbo.sysservers
         where srvname = @REMOTESERVER

         if (@srvid IS NULL)
            begin
               print '    REMOTE SERVER <%1!> DOES NOT EXIST.', @REMOTESERVER
            end
         else if (@srvid = 0)
            begin
               print '    <%1!> IS THE LOCAL SERVER.', @REMOTESERVER
            end
         else
            begin
               print '    THERE ARE NO REMOTE LOGINS FROM <%1!>.', @REMOTESERVER
            end

         print ' '
      end
go
sp_addcomment sp_remotelogins, 16, 'Lists remote servers and users (option: REMOTE_SERVER)'
go
sp_procxmode sp_remotelogins, anymode
go

create procedure sp_remoteservers as
   set ansinull                      off              -- Non-ANSI
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   create table #servers
      (srvid    smallint      not null,
       name     varchar (35)  not null,
       netname  varchar (30)  null,
       status   smallint      not null,
       users    smallint      not null,
       text     varchar (34)  null)

   if (exists (select *
               from master.dbo.sysservers))
      begin
         insert into #servers
            select s.srvid,
                   s.srvname,
                   s.srvnetname,
                   s.srvstatus,
                   count (l.suid),
                   'timeouts,'
            from master.dbo.sysremotelogins l,
                 master.dbo.sysservers      s
            where s.srvid *= l.remoteserverid
            group by s.srvid

         if (exists (select *
                     from master.dbo.sysremotelogins))
            begin
               print '    REMOTE LOGIN MAPPING OPTIONS:'

               print ' '

               print '       (1): Map remote logins to particular local names'

               print '       (2): (Other) remote logins keep remote names'

               print '       (3): Map (other) remote logins to one name'

               print ' '

               update #servers
               set name = name + ', (1)'
               from #servers s
               where exists (select l.*
                             from master.dbo.sysremotelogins l
                             where s.srvid           = l.remoteserverid
                               and l.remoteusername IS NOT NULL)

               update #servers
               set name = name + ', (2)'
               from #servers s
               where exists (select l.*
                             from master.dbo.sysremotelogins l
                             where s.srvid           = l.remoteserverid
                               and l.remoteusername IS NULL
                               and l.suid            = -1)

               update #servers
               set name = name + ', (3)'
               from #servers s
               where exists (select l.*
                             from master.dbo.sysremotelogins l
                             where s.srvid           = l.remoteserverid
                               and l.remoteusername IS NULL
                               and l.suid           <> -1)
            end

         update #servers
         set text = 'no timeouts,'
         where (status & 1) = 1

         update #servers
         set text = text + ' no network encrytion'
         where (status & 2) <> 2

         update #servers
         set text = text + ' network encrytion'
         where (status & 2) = 2

         update #servers
         set text = 'local server'
         where srvid = 0

         if (exists (select *
                     from master.dbo.sysservers
                     where srvname <> srvnetname))
            begin
               print '    AT LEAST ONE NETWORK SERVER NAME DIFFERS FROM ITS SERVER NAME.'

               print ' '

               select 'SERVER NAME & MAPPING OPTIONS' = substring (name, 1, 29),
                      'NETWORK NAME'                  = substring (netname, 1, 15),
                      LOGINS                          = users,
                      STATUS                          = substring (text, 1, 24)
               from #servers
               order by name
            end
         else
            begin
               print '    ALL SERVER NAMES ARE IDENTICAL WITH THEIR NETWORK SERVER NAMES.'

               print ' '

               select 'SERVER NAME & MAPPING OPTIONS' = name,
                      LOGINS                          = users,
                      STATUS                          = text
               from #servers
               order by name
            end
      end
   else
      begin
         print '    NO SERVERS ARE DEFINED.'

         print ' '
      end
go
sp_addcomment sp_remoteservers, 16, 'Lists status of all local and remote servers'
go
sp_procxmode sp_remoteservers, anymode
go

create procedure sp_status (@PARM varchar (30) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @secspertick  decimal (14, 4)

   select @secspertick = convert (decimal (14, 4), @@timeticks) /
                         convert (decimal (14, 4), 1000000)

   if (@PARM IS NULL)
      begin
         if (exists (select *
                     from master.dbo.sysprocesses
                     where blocked > 0))
            begin
               select 'DATABASE' = substring (db_name (dbid), 1, 12),
                      'USER'     = substring (isnull (suser_name (suid), '-'), 1, 12),
                      PROGRAM    = substring (program_name, 1, 9),
                      SPID       = spid,
                      'CPU sec'  = substring (convert (char (10), convert (money, cpu * @secspertick), 1), 1, 7),
                      BSPID      = blocked,
                      COMMAND    = substring (cmd, 1, 10),
                      STATUS     = substring (status, 1, 8)
               from master.dbo.sysprocesses
               order by db_name (dbid), suser_name (suid), program_name, spid
            end
         else
            begin
               select 'DATABASE' = substring (db_name (dbid), 1, 13),
                      'USER'     = substring (isnull (suser_name (suid), '-'), 1, 13),
                      PROGRAM    = substring (program_name, 1, 10),
                      SPID       = spid,
                      'CPU sec'  = substring (convert (char (10), convert (money, cpu * @secspertick), 1), 1, 7),
                      COMMAND    = substring (cmd, 1, 12),
                      STATUS     = substring (status, 1, 10)
               from master.dbo.sysprocesses
               order by db_name (dbid), suser_name (suid), program_name, spid
            end
      end
   else if (exists (select *
                    from master.dbo.sysdatabases
                    where name = @PARM))
      begin
         if (exists (select *
                     from master.dbo.sysprocesses
                     where dbid = db_id (@PARM)))
            begin
               if (exists (select *
                           from master.dbo.sysprocesses
                           where dbid    = db_id (@PARM)
                             and blocked > 0))
                  begin
                     select 'USER'    = substring (isnull (suser_name (suid), '-'), 1, 11),
                            CLIENT    = upper (substring (hostname, 1, 9)),
                            PROGRAM   = substring (program_name, 1, 11),
                            SPID      = spid,
                            'CPU sec' = substring (convert (char (10), convert (money, cpu * @secspertick), 1), 1, 7),
                            BSPID     = blocked,
                            COMMAND   = substring (cmd, 1, 10),
                            STATUS    = substring (status, 1, 8)
                     from master.dbo.sysprocesses
                     where dbid = db_id (@PARM)
                     order by suser_name (suid), upper (hostname), program_name, spid
                  end
               else
                  begin
                     select 'USER'    = substring (isnull (suser_name (suid), '-'), 1, 13),
                            CLIENT    = upper (hostname),
                            PROGRAM   = substring (program_name, 1, 13),
                            SPID      = spid,
                            'CPU sec' = substring (convert (char (10), convert (money, cpu * @secspertick), 1), 1, 7),
                            COMMAND   = substring (cmd, 1, 12),
                            STATUS    = substring (status, 1, 10)
                     from master.dbo.sysprocesses
                     where dbid = db_id (@PARM)
                     order by suser_name (suid), upper (hostname), program_name, spid
                  end
            end
         else
            begin
               print '    THERE ARE NO PROCESSES FOR DATABASE <%1!>.', @PARM

               print ' '
            end
      end
   else if (suser_id (@PARM) IS NOT NULL)
      begin
         if (exists (select *
                     from master.dbo.sysprocesses
                     where suid = suser_id (@PARM)))
            begin
               if (exists (select *
                           from master.dbo.sysprocesses
                           where suid    = suser_id (@PARM)
                             and blocked > 0))
                  begin
                     select 'DATABASE' = substring (db_name (dbid), 1, 12),
                            CLIENT     = upper (substring (hostname, 1, 9)),
                            PROGRAM    = substring (program_name, 1, 12),
                            SPID       = spid,
                            'CPU sec'  = substring (convert (char (10), convert (money, cpu * @secspertick), 1), 1, 7),
                            BSPID      = blocked,
                            COMMAND    = substring (cmd, 1, 10),
                            STATUS     = substring (status, 1, 8)
                     from master.dbo.sysprocesses
                     where suid = suser_id (@PARM)
                     order by db_name (dbid), upper (hostname), program_name, spid
                  end
               else
                  begin
                     select 'DATABASE' = substring (db_name (dbid), 1, 13),
                            CLIENT     = upper (hostname),
                            PROGRAM    = substring (program_name, 1, 13),
                            SPID       = spid,
                            'CPU sec'  = substring (convert (char (10), convert (money, cpu * @secspertick), 1), 1, 7),
                            COMMAND    = substring (cmd, 1, 12),
                            STATUS     = substring (status, 1, 10)
                     from master.dbo.sysprocesses
                     where suid = suser_id (@PARM)
                     order by db_name (dbid), upper (hostname), program_name, spid
                  end
            end
         else
            begin
               print '    THERE ARE NO PROCESSES FOR USER <%1!>.', @PARM

               print ' '
            end
      end
   else
      begin
         print '    NEITHER DATABASE NOR USER <%1!> EXISTS.', @PARM

         print ' '
      end
go
sp_addcomment sp_status, 16, 'Lists processes for DATABASE/USER or (default) everyone'
go
sp_procxmode sp_status, anymode
go

dump transaction sybsystemprocs with truncate_only
go
