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           -- Replaced with sp_deviceright in right.sql
go
drop procedure sp_oldxacts
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,
          lk.context,
          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, ' ', 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

         update #alllocks
         set ltext = substring (ltext + vLK2.name, 1, 40)
         from #alllocks l,
              sybsystemprocs.dbo.usptvalues vLK2
         where vLK2.type   = 'LK2'
           and vLK2.number = l.context

         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, 37)
               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 as
   --
   --  Bug # 171718.  Coalesce will NOT work correctly if ANSINULL is set
   --
   set ansinull                      off              -- Non-ANSI
   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_blockers> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   select dbid     = p1.dbid,
          blockee  = suser_name (p1.suid)
                     + ' ('
                     + convert (varchar (6), p1.spid)
                     + ')',
          cmd      = p1.cmd,
          id       = p1.id,
          linenum  = p1.linenum,
          block    = p1.time_blocked,
          blocker  = suser_name (p2.suid)
                     + ' ('
                     + convert (varchar (6), p2.spid)
                     + ')',
          bdbid    = p2.dbid,
          bcmd     = p2.cmd,
          bid      = p2.id,
          blinenum = p2.linenum
   into #blockers
   from master.dbo.sysprocesses p1,
        master.dbo.sysprocesses p2
   where p1.blocked <> 0
     and p1.blocked *= p2.spid

   insert into #blockers
      select dbid,
             suser_name (suid) + ' (' + convert (varchar (6), spid) + ')',
             cmd,
             0,
             0,
             -1,
             '1',
             1,
             'LOG SUSPEND',
             0,
             0
      from master.dbo.sysprocesses
      where cmd = 'LOG SUSPEND'

   if (exists (select *
               from #blockers))
      begin
            select 'USER (SPID)'            = space (12),
                   'LINE: PROCEDURE / CMD'  = space (24),
                   'BLOCKER (SPID)'         = space (14),
                   'LINE: PROCEDURE / CMD ' = space (24)
            where 1 = 2
         union
            select substring (blockee, 1, 12),
                   substring (convert (varchar (5), linenum)
                              + ': '
                              + coalesce (object_name (id, dbid),
                                          cmd),
                              1,
                              24),
                   substring (blocker, 1, 14),
                   substring (convert (varchar (5), blinenum)
                              + ': '
                              + coalesce (object_name (bid, bdbid),
                                          bcmd),
                              1,
                              24)
            from #blockers
            where id <> 0
         union
            select substring (blockee, 1, 12),
                   substring (cmd, 1, 24),
                   substring (blocker, 1, 14),
                   substring (bcmd, 1, 24)
            from #blockers
            where id = 0
         order by 'USER (SPID)'
      end
   else
      begin
         print '    THERE ARE NO BLOCKED PROCESSES.'

         print ' '
      end
go
sp_addcomment sp_blockers, 16, 'Lists blockee/blocker for all databases'
go
sp_procxmode sp_blockers, anymode
go

create procedure sp_busy (@SAMPLES smallint = 4,
                          @PAUSE   tinyint  = 5) as
   --
   --  Bug # 171718.  Coalesce will NOT work correctly if ANSINULL is set
   --
   set ansinull                      off              -- Non-ANSI
   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 @cpu_play     decimal (14, 4),
           @now          varchar (9),
           @samplecnt    smallint,
           @secspertick  decimal (14, 4),
           @waitminchar  char (2),
           @waitsec      tinyint,
           @waitsecchar  char (2),
           @seq          smallint,
           @waitfor      char (8)

   print '    CPU 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

   --
   --  @cpu_play WILL ELIMINATE "BARELY RUNNING PROCESSES"; ADJUST IF DESIRED.
   --

   select @cpu_play    = .1,
          @waitminchar = convert (char (1), @PAUSE / 60)  +  ':',
          @waitsec     = @PAUSE % 60

   if (@waitsec < 10)
      begin
         select @waitsecchar = '0'  +  convert (char (1), @waitsec)
      end
   else
      begin
         select @waitsecchar = convert (char (2), @waitsec)
      end

   select @waitfor = '00:0'  +  @waitminchar  +  @waitsecchar

   print '    THERE WILL BE %1! SAMPLES WITH A PAUSE OF %2!.', @samplecnt, @waitfor

   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             decimal (14, 4)  not null,
           physical_io     int              not null,
           blocked         smallint         not null,
           dbid            smallint         not null,
           id              int              not null,
           linenum         smallint         not null,
           time_blocked    int              null,
           usr_spid_text   varchar (40)     not null,
           lp_text         varchar (40)     not 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,
                   (cpu * @secspertick),
                   physical_io,
                   blocked,
                   dbid,
                   id,
                   linenum,
                   time_blocked,
                   ' ',
                   ' '
            from master.dbo.sysprocesses
            where spid <> @@spid

         update #tmp
         set usr_spid_text = isnull (convert (varchar (14), suser_name (suid)), '-')
                             +  ' ('
                             +  convert (varchar (6), spid)
                             +  ')'
         where seq = @seq

         update #tmp
         set lp_text = cmd
         where seq = @seq
           and id  = 0

         update #tmp
         set lp_text = convert (varchar (6), linenum)
                       +  ': '
                       +  coalesce (object_name (id, dbid),
                                    object_name (id, db_id ('sybsystemprocs')),
                                    object_name (id, db_id ('master')),
                                    object_name (id, db_id ('dbccdb')),
                                    '(unknown SP)')
         where seq  = @seq
           and id  <> 0

         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 ' '

               print '                                                               CPU       BLCKD'

               select 'DATABASE'        = substring (db_name (n.dbid), 1, 8),
                      'USER (SPID)'     = substring (n.usr_spid_text, 1, 14),
                      CLIENT            = substring (n.hostname, 1, 8),
                      PROGRAM           = substring (n.program_name, 1, 8),
                      'LINE: PROCEDURE' = substring (n.lp_text, 1, 19),
                      'sec'             = convert (tinyint, abs (round ((n.cpu - o.cpu), 0))),
                      'I/O''s'          = substring (convert (char (9), convert (money, ((n.physical_io - o.physical_io) % 32767)), 1), 1, 6),
                      'sec '            = isnull (convert (tinyint, (n.time_blocked - o.time_blocked) % 255), 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.blocked     > 0)                  or
                      (n.cpu         > o.cpu + @cpu_play)  or
                      (n.physical_io > o.physical_io)      or
                      (n.cmd         = 'LOG SUSPEND')      or
                      (exists (select n2.spid
                               from #tmp n2
                               where n2.seq     = @seq
                                 and n2.blocked = n.spid)))
               order by 'DATABASE', 'USER (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 + @cpu_play)  or
                                 (n.cmd         = 'LOG SUSPEND'))))
            begin
               select @now = ' ' + convert (char (8), getdate(), 8)

               print ' '

               print '%1!', @now

               print ' '

               print '                                                                    CPU'

               select 'DATABASE'        = substring (db_name (n.dbid), 1, 8),
                      'USER (SPID)'     = substring (n.usr_spid_text, 1, 14),
                      CLIENT            = substring (n.hostname, 1, 8),
                      PROGRAM           = substring (n.program_name, 1, 8),
                      'LINE: PROCEDURE' = substring (n.lp_text, 1, 24),
                      'sec'             = convert (tinyint, abs (round ((n.cpu - o.cpu), 0))),
                      '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 + @cpu_play)  or
                      (n.cmd         = 'LOG SUSPEND'))
               order by 'DATABASE', 'USER (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

         waitfor delay @waitfor

         delete from #tmp
         where seq = @seq - 1

         select @seq = @seq + 1
      end
go
sp_addcomment sp_busy, 16, 'Lists processes whose CPU or I/O usage is 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,
       policy     char (7)       not null)

   create table #cache2
      (name       varchar (48)   not null,
       iosize     char (8)       not null,
       washsize   int            not null,
       poolsize   int            not null,
       apf        tinyint        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),
             'Strict'
      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), 'Strict'
      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

         update #cache1
         set policy = 'Relaxed'
         where (status & 256) = 256

         if (exists (select *
                     from #cache1
                     where (status & 64) = 64))
            begin
               print '                                          LRU'

               select 'CACHE NAME'  = substring (name, 1, 19),
                      TYPE          = typetxt,
                      STATUS        = stattxt,
                      POLICY        = policy,
                      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 @maxcachename = max (char_length (name))
               from #cache1

               if (@maxcachename < 20)
                  begin
                     print '                                          LRU'

                     select 'CACHE NAME'  = substring (name, 1, 19),
                            TYPE          = typetxt,
                            STATUS        = stattxt,
                            POLICY        = policy,
                            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
                     print '                                                      LRU'

                     select 'CACHE NAME'  = substring (name, 1, 31),
                            TYPE          = typetxt,
                            STATUS        = stattxt,
                            POLICY        = policy,
                            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),
                   convert (tinyint, cc.apf_percent),
                   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),
                   convert (tinyint, cc.apf_percent),
                   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 < 20)
            begin
               select 'CACHE NAME' = substring (name, 1, 19),
                      '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),
                      'APF%'       = apf
               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),
                      'APF%'       = apf
               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),
                      'APF%'       = apf
               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 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_lockagg> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @maxdblen   tinyint,
           @maxtablen  tinyint

   if (exists (select l.*
               from master.dbo.syslocks l
               where not exists (select p.spid
                                 from master.dbo.sysprocesses p
                                 where p.spid = l.spid)))
      begin
         print '    THERE ARE LOCKS FOR NON-EXISTENT PROCESSES.'

         print ' '

         select distinct SPID       = l.spid,
                         'DATABASE' = db_name (l.dbid),
                         'TABLE'    = isnull (object_name (id, dbid), convert (varchar (11), id))
         from master.dbo.syslocks l
         where not exists (select p.spid
                           from master.dbo.sysprocesses p
                           where p.spid = l.spid)
         order by SPID, 'DATABASE', 'TABLE'

         print ' '
      end

   select distinct dbid    = dbid,
                   id      = id,
                   type    = type,
                   class   = class,
                   context = context,
                   C       = count (id),
                   tabname = convert (varchar (30), ' '),
                   ltext   = convert (varchar (40), class),
                   Ctext   = convert (char (7), ' ')
   into #lockagg
   from master.dbo.syslocks
   where id    > 0
     and spid <> @@spid
   group by dbid, id, type, class
   having id    > 0
      and 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 ltext = substring (ltext + vLK2.name, 1, 40)
         from #lockagg l,
              sybsystemprocs.dbo.usptvalues vLK2
         where vLK2.type   = 'LK2'
           and vLK2.number = l.context

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

         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'    = Ctext
                     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'    = Ctext
                     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'    = Ctext
                     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'    = Ctext
                     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 p.suid,
          lk.spid,
          lk.id,
          lk.page,
          lk.type,
          lk.class,
          lk.context,
          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

         update #locking
         set ltext = substring (ltext + vLK2.name, 1, 47)
         from #locking l,
              sybsystemprocs.dbo.usptvalues vLK2
         where vLK2.type   = 'LK2'
           and vLK2.number = l.context

         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_oldxacts 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_oldxacts> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   print ' '

   if (exists (select *
               from master.dbo.syslogshold))
      begin
         select 'DATABASE'    = substring (db_name (lh.dbid), 1, 12),
                'USER'        = substring (isnull (suser_name (p.suid), '-'), 1, 11),
                PROGRAM       = substring (isnull (p.program_name, '-'), 1, 9),
                'PROCEDURE'   = substring (coalesce (object_name (p.id, p.dbid),
                                                     object_name (p.id, db_id ('sybsystemprocs')),
                                                     object_name (p.id, db_id ('master')),
                                                     object_name (p.id, db_id ('dbccdb')),
                                                     'SPID: ' + convert (varchar (7), lh.spid)),
                                            1,
                                            12),
                'TRANSACTION' = substring (lh.name, 1, 12),
                'START TIME'  = substring (convert (char (25), lh.starttime, 111), 1, 10)
                                +  ' '
                                +  substring (convert (char (25), lh.starttime, 8), 1, 5)

         from master.dbo.syslogshold lh,
              master.dbo.sysprocesses p
         where lh.spid *= p.spid
         order by lh.starttime, db_name (lh.dbid)
      end
   else
      begin
         print '    THERE ARE NO ACTIVE TRANSACTIONS.'
      end
go
sp_addcomment sp_oldxacts, 16, 'Lists oldest transaction info by database'
go
sp_procxmode sp_oldxacts, 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 logins (option: REMOTE_SERVER)'
go
sp_procxmode sp_remotelogins, anymode
go

create procedure sp_remoteservers as
   set ansinull                      off              -- Non-ANSI
   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_remoteservers> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @i          int,
           @loginflag  tinyint,
           @seq        tinyint

   create table #servers
      (srvid       smallint      not null,
       name        varchar (30)  not null,
       netname     varchar (32)  null,
       status      smallint      not null,
       class       smallint      null,
       classtext   varchar (13)  not null,
       logins      int           not null,
       srvsecmech  varchar (30)  null,
       optiontext  varchar (160) null,
       sortname    varchar (30)  not null,
       seq         tinyint       not null)

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

         print '                                REMOTE'

         select 'SERVER NAME'  = name,
                LOGINS         = substring (convert (char (10),
                                                     convert (money, logins),
                                                     1),
                                            1,
                                            7),
                'NETWORK NAME' = netname
         from #servers
         order by name

         print ' '

         if (exists (select *
                     from master.dbo.sysremotelogins))
            begin
               select @loginflag = 1

               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 classtext = 'local'
         where srvid = 0

         update #servers
         set classtext = substring (vX.name, 1, 13)
         from #servers s,
              sybsystemprocs.dbo.usptvalues vX
         where vX.type               = 'X'
           and vX.number             > 0
           and (vX.number & s.class) = vX.number

         update #servers
         set optiontext = ', timeouts'
         where (status & 1) <> 1

         select @i = 0

         while (@i <= 8)
            begin
               update #servers
               set optiontext = optiontext + ', ' + vA.name
               from #servers s,
                    sybsystemprocs.dbo.usptvalues vA
               where vA.type                = 'A'
                 and vA.number              = power (2, @i)
                 and (vA.number & s.status) = vA.number

               select @i = @i + 1
            end

         update #servers
         set optiontext = substring (optiontext, 3, 160)

         update #servers
         set optiontext = optiontext
                          +  ', '
                          +  s.srvsecmech
                          +  '('
                          +  m.available_service
                          +  ')'
         from #servers s,
              master.dbo.syssecmechs m
         where m.sec_mech_name = s.srvsecmech

         select @seq = 1

         while (exists (select *
                        from #servers
                        where seq                      = @seq
                          and char_length (optiontext) > 40))
            begin
               insert into #servers
                  select 1, ' ', ' ', 1, 1, ' ', 1, ' ', optiontext, sortname, (@seq + 1)
                  from #servers
                  where char_length (optiontext) > 40

               update #servers
               set optiontext = substring (optiontext, 1, 40)
               where seq                      = @seq
                 and char_length (optiontext) > 40

               update #servers
               set optiontext = substring (s1.optiontext, 1, 41 - charindex (',', reverse (s1.optiontext)))
               from #servers s1,
                    #servers s2
               where s1.seq      = @seq
                 and s2.seq      = @seq + 1
                 and s2.sortname = s1.sortname

               update #servers
               set optiontext = substring (s2.optiontext, 2 + char_length (s1.optiontext), 160)
               from #servers s2,        -- s2 MUST BE FIRST!
                    #servers s1
               where s1.seq      = @seq
                 and s2.seq      = @seq + 1
                 and s2.sortname = s1.sortname

               select @seq = @seq + 1
            end

         if (@loginflag = 1)
            begin
               select 'SERVER/OPTION' = substring (name, 1, 22),
                      CLASS           = classtext,
                      STATUS          = substring (optiontext, 1, 40)
               from #servers
               order by sortname, seq
            end
         else
            begin
               select 'SERVER' = substring (name, 1, 22),
                      CLASS    = classtext,
                      STATUS   = substring (optiontext, 1, 40)
               from #servers
               order by sortname, seq
            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
   --
   --  Bug # 171718.  Coalesce will NOT work correctly if ANSINULL is set
   --
   set ansinull                      off              -- Non-ANSI
   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

   create table #status
      (dbid        smallint      not null,
       dbname      varchar (30)  not null,
       status      char (12)     not null,
       login_name  varchar (40)  null,
       program     char (16)     null,
       hostname    char (10)     not null,
       line_sp     varchar (35)  not null,
       cmd         char (16)     null,
       spid        int           not null,
       bspid       int           null)

   if ((@PARM IS NULL)  or  (db_id (@PARM) IS NOT NULL))
      begin
         insert into #status
            select dbid,
                   db_name (dbid),
                   status,
                   isnull (suser_name (suid), '-'),
                   program_name,
                   hostname,
                   convert (varchar (6), linenum)
                   +  ': '
                   +  coalesce (object_name (id, dbid),
                                object_name (id, db_id ('sybsystemprocs')),
                                object_name (id, db_id ('master')),
                                object_name (id, db_id ('dbccdb')),
                                '(unknown SP)'),
                   cmd,
                   spid,
                   blocked
            from master.dbo.sysprocesses
            where dbid  = isnull (db_id (@PARM), dbid)
              and id   <> 0
              and ((status <> 'sleeping')  or
                   (cmd not in ('CHECKPOINT SLEEP', 'DEADLOCK TUNE', 'HOUSEKEEPER',
                                'MIRROR HANDLER', 'NETWORK HANDLER')))

         insert into #status
            select dbid,
                   db_name (dbid),
                   status,
                   isnull (suser_name (suid), '-'),
                   program_name,
                   hostname,
                   '-',
                   cmd,
                   spid,
                   blocked
            from master.dbo.sysprocesses
            where dbid = isnull (db_id (@PARM), dbid)
              and id   = 0
              and ((status <> 'sleeping')  or
                   (cmd not in ('CHECKPOINT SLEEP', 'DEADLOCK TUNE', 'HOUSEKEEPER',
                                'MIRROR HANDLER', 'NETWORK HANDLER')))
      end
   else if (suser_id (@PARM) IS NOT NULL)
      begin
         insert into #status
            select dbid,
                   db_name (dbid),
                   status,
                   isnull (suser_name (suid), '-'),
                   program_name,
                   hostname,
                   convert (varchar (6), linenum)
                   +  ': '
                   +  coalesce (object_name (id, dbid),
                                object_name (id, db_id ('sybsystemprocs')),
                                object_name (id, db_id ('master')),
                                object_name (id, db_id ('dbccdb')),
                                '(unknown SP)'),
                   cmd,
                   spid,
                   blocked
            from master.dbo.sysprocesses
            where suid  = suser_id (@PARM)
              and id   <> 0
              and ((status <> 'sleeping')  or
                   (cmd not in ('CHECKPOINT SLEEP', 'DEADLOCK TUNE', 'HOUSEKEEPER',
                                'MIRROR HANDLER', 'NETWORK HANDLER')))

         insert into #status
            select dbid,
                   db_name (dbid),
                   status,
                   isnull (suser_name (suid), '-'),
                   program_name,
                   hostname,
                   '-',
                   cmd,
                   spid,
                   blocked
            from master.dbo.sysprocesses
            where suid  = suser_id (@PARM)
              and id   = 0
              and ((status <> 'sleeping')  or
                   (cmd not in ('CHECKPOINT SLEEP', 'DEADLOCK TUNE', 'HOUSEKEEPER',
                                'MIRROR HANDLER', 'NETWORK HANDLER')))
      end
   else
      begin
         print '    NEITHER DATABASE NOR USER <%1!> EXISTS.', @PARM

         print ' '
      end

   update #status
   set login_name = s.login_name + ' (' + convert (varchar (6), p.origsuid) + ')'
   from master.dbo.sysprocesses p,
        #status s
   where p.spid      = s.spid
     and p.origsuid IS NOT NULL

   insert into #status
      select distinct dbid, db_name (dbid), '?', '?', '?', '?', '?', '-', spid, 0
      from master.dbo.syslocks l
      where not exists (select *
                        from master.dbo.sysprocesses p
                        where p.spid = l.spid)

   if (@PARM IS NULL)
      begin
         if (exists (select *
                     from #status
                     where bspid > 0))
            begin
               select 'DATABASE'        = substring (dbname, 1, 11),
                      'LOGIN'           = substring (login_name, 1, 10),
                      PROGRAM           = substring (program, 1, 9),
                      'LINE: PROCEDURE' = substring (line_sp, 1, 19),
                      COMMAND           = substring (cmd, 1, 10),
                      SPID              = convert (smallint, spid),
                      BSPID             = convert (smallint, bspid)
               from #status
               order by 'DATABASE', 'LOGIN', SPID
            end
         else
            begin
               select 'DATABASE'        = substring (dbname, 1, 12),
                      'LOGIN'           = substring (login_name, 1, 12),
                      PROGRAM           = substring (program, 1, 10),
                      'LINE: PROCEDURE' = substring (line_sp, 1, 21),
                      COMMAND           = substring (cmd, 1, 11),
                      SPID              = convert (smallint, spid)
               from #status
               order by 'DATABASE', 'LOGIN', SPID
            end
      end
   else if (db_id (@PARM) IS NOT NULL)
      begin
         if (exists (select *
                     from #status
                     where dbid = db_id (@PARM)))
            begin
               if (exists (select *
                           from #status
                           where bspid > 0))
                  begin
                     select 'LOGIN'           = substring (login_name, 1, 12),
                            CLIENT            = substring (hostname, 1, 10),
                            'LINE: PROCEDURE' = substring (line_sp, 1, 28),
                            COMMAND           = substring (cmd, 1, 9),
                            SPID              = convert (smallint, spid),
                            BSPID             = convert (smallint, bspid)
                     from #status
                     order by 'LOGIN', CLIENT, SPID
                  end
               else
                  begin
                     select 'LOGIN'           = substring (login_name, 1, 10),
                            CLIENT            = substring (hostname, 1, 10),
                            'LINE: PROCEDURE' = substring (line_sp, 1, 28),
                            COMMAND           = substring (cmd, 1, 9),
                            STATUS            = substring (status, 1, 9),
                            SPID              = convert (smallint, spid)
                     from #status
                     order by 'LOGIN', CLIENT, 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 #status))
            begin
               if (exists (select *
                           from #status
                           where bspid > 0))
                  begin
                     select 'DATABASE'        = substring (dbname, 1, 10),
                            CLIENT            = substring (hostname, 1, 10),
                            'LINE: PROCEDURE' = substring (line_sp, 1, 28),
                            COMMAND           = substring (cmd, 1, 9),
                            SPID              = convert (smallint, spid),
                            BSPID             = convert (smallint, bspid)
                     from #status
                     order by 'LOGIN', CLIENT, SPID
                  end
               else
                  begin
                     select 'DATABASE'        = substring (dbname, 1, 10),
                            CLIENT            = substring (hostname, 1, 10),
                            'LINE: PROCEDURE' = substring (line_sp, 1, 28),
                            COMMAND           = substring (cmd, 1, 9),
                            STATUS            = substring (status, 1, 9),
                            SPID              = convert (smallint, spid)
                     from #status
                     order by 'LOGIN', CLIENT, SPID
                  end
            end
         else
            begin
               print '    THERE ARE NO PROCESSES FOR USER %1!.', @PARM

               print ' '
            end
      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
