use sybsystemprocs
go

sp_dropprocedure sp_alllocks
go
sp_dropprocedure sp_blockers
go
sp_dropprocedure sp_busy
go
sp_dropprocedure sp_cache_db
go
sp_dropprocedure sp_cache
go
sp_dropprocedure sp_db_database
go
sp_dropprocedure sp_db_date
go
sp_dropprocedure sp_db_date_size
go
sp_dropprocedure sp_db_logdate_used
go
sp_dropprocedure sp_db_logdate
go
sp_dropprocedure sp_db_null
go
sp_dropprocedure sp_db_size
go
sp_dropprocedure sp_db
go
sp_dropprocedure sp_lockagg
go
sp_dropprocedure sp_locking
go
sp_dropprocedure sp_oldxacts
go
sp_dropprocedure sp_remotelogins
go
sp_dropprocedure sp_remoteservers
go
sp_dropprocedure sp_status
go

set quoted_identifier on
go

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

   print ' '

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

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

         print ' '

         return 1
      end

   declare @maxtext  tinyint

   if (exists (select *
               from master.dbo.syslocks
               where dbid = 2
                 and id   < 100
                 and ((spid = 1)  or  (spid > 7))))
      begin
         print '    THERE ARE SYSTEM TABLE LOCKS IN tempdb.'

         print ' '

         select SPID       = spid,
                'DATABASE' = dbid,
                'TABLE'    = id,
                'PAGE'     = page,
                TYPE       = type,
                'CURSOR'   = class
         from master.dbo.syslocks
         where spid <> @@spid
      end
   else
      begin
         set transaction isolation level 1

         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  left join  master.dbo.sysprocesses p
           on lk.spid = p.spid
         where lk.id    > 0
           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),
                            'LOGIN (spid)' = 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),
                            'LOGIN (spid)' = 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
      end
go
sp_addcomment sp_alllocks, 'Lists all locks for system (except your own spid)'
go
sp_procxmode sp_alllocks, anymode
go

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

   print ' '

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

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

         print ' '

         return 1
      end

   if (exists (select *
               from master.dbo.syslocks
               where dbid = 2
                 and id   < 100
                 and ((spid = 1)  or  (spid > 7))))
      begin
         print '    THERE ARE SYSTEM TABLE LOCKS IN tempdb.'

         print ' '

         select SPID           = spid,
                SUID           = suid,
                COMMAND        = cmd,
                LINE           = linenum,
                STATEMENT      = stmtnum,
                'BLOCKER spid' = blocked
         from master.dbo.sysprocesses
         where blocked <> 0
      end
   else
      begin
         set transaction isolation level 1

         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  left join  master.dbo.sysprocesses p2
           on p1.blocked = p2.spid
         where p1.blocked <> 0

         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 'LOGIN (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 'LOGIN (spid)'
            end
         else
            begin
               print '    THERE ARE NO BLOCKED PROCESSES.'

               print ' '
            end
      end
go
sp_addcomment sp_blockers, 'Lists blockee/blocker for 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 @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),
                      'LOGIN (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, (abs (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', 'LOGIN (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),
                      'LOGIN (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, (abs (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', 'LOGIN (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, 'Lists processes whose CPU or I/O usage is changing'
go
sp_addcomment sp_busy, '   SAMPLE (default 4) indicates how many samples'
go
sp_addcomment sp_busy, '   PAUSE (default 5) seconds between samples'
go
sp_procxmode sp_busy, anymode
go

------------ Temporary table needed for creation of sp_cache_db subroutine ---

create table #cache3
   (db         varchar ( 31)                   not null,
    objid      int                             not null,
    objname    varchar ( 94)                   not null,
    type       char (10)                       not null,
    cachename  varchar (120)                   not null,
    indid      tinyint        default 0        not null)
go


create procedure sp_cache_db  (@CACHENAME  varchar (120)) as
   set ansinull                      on
   set string_rtruncation            on

   insert into #cache3 (db, objid, objname, type, cachename)
      select db_name (),
             object,
             db_name (object),
             'Database',
             char_value
      from sysattributes
      where class       = 3
        and attribute   = 0
        and object_type = 'D'
        and char_value  = isnull (@CACHENAME, char_value)

   insert into #cache3 (db, objid, objname, type, cachename, indid)
      select db_name (),
             a.object,
             object_name (a.object) + '.' + i.name,
             'Index (' + convert (varchar (3), i.indid) + ')',
             a.char_value,
             i.indid
      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 (db, objid, objname, type, cachename)
      select db_name (),
             object,
             object_name (object),
             'Log',
             char_value
      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 (db, objid, objname, type, cachename, indid)
      select db_name (),
             object,
             object_name (object),
             'Image/Text',
             char_value,
             object_info1
      from sysattributes
      where class        = 3
        and attribute    = 0
        and object_info1 > 253
        and char_value   = isnull (@CACHENAME, char_value)

   insert into #cache3 (db, objid, objname, type, cachename, indid)
      select db_name (),
             a.object,
             object_name (a.object),
             'Table (' + convert (varchar (3), i.indid) + ')',
             a.char_value,
             i.indid
      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)

   update #cache3
   set objname = user_name (o.uid)  +  '.'  +  c.objname
   from #cache3 c,
        sysobjects o
   where ((c.type = 'Image/Text')  or  (c.type like 'Table (%'))
     and c.db    = db_name ()
     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 = user_name (o.uid)  +  '.'  +  c.objname
   from #cache3 c,
        sysobjects o
   where c.db      = db_name ()
     and 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))))
go
sp_existsprocedure sp_cache_db
go

drop table #cache3
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 @adjust_flag  tinyint,
           @dbname       varchar ( 30),
           @cmdstring    varchar (255),
           @lru_string   varchar ( 78),
           @objlen       tinyint,
           @cachelen     tinyint

   create table #cache1
      (name       varchar (120)  not null,
       printname  varchar (125)  not null,
       status     smallint       not null,
       stattxt    char (9)       not null,
       typetxt    char (8)       not null,
       sz         int            not null,
       size       char (9)       not null,
       oh         int            not null,
       overhead   char (8)       not 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   char (9)       not null,
       poolsize   char (9)       not null,
       apf        tinyint        not null,
       sort       varchar (56)   not null)

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

   insert into #cache1
      select cf.name,
             cf.name  +  ' ['  +  convert (varchar (2), cf.value4)  +  ']',
             cf.status,
             ' ',
             ' ',
             (cc.value / 2),
             substring (convert (char (12), convert (money, (cc.value / 2)), 1), 1, 9),
             (cc.memory_used / 2),
             substring (convert (char (11), convert (money, (cc.memory_used / 2)), 1), 1, 8),
             (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,
             name,
             status,
             ' ',
             ' ',
             0,
             '      N/A',
             0,
             '     N/A',
             (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
         if (1 < (select count (*)
                  from #cache1))
            begin
               select 'TOTAL CACHE'    = substring (convert (char (17),
                                                             convert (money,
                                                                      sum (sz)),
                                                             1),
                                                    1,
                                                    14),
                      'TOTAL OVERHEAD' = substring (convert (char (17),
                                                             convert (money,
                                                                      sum (oh)),
                                                             1),
                                                    1,
                                                    14)
               from #cache1
            end

         update #cache1
         set printname = substring (printname, 1, char_length (printname) - 4)
         where printname like '% @[1@]'  escape '@'

         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

         select @cachelen = max (char_length (printname))
         from #cache1

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

               select 'CACHE NAME'  = substring (printname, 1, 19),
                      TYPE          = typetxt,
                      STATUS        = stattxt,
                      POLICY        = policy,
                      SIZE          = size,
                      OVERHEAD      = overhead,
                      'CONFIG SIZE' = substring (convert (char (14), convert (money, size_cfg), 1), 1, 11)
               from #cache1
               order by name
            end
         else
            begin
               select @lru_string = space (23 + @cachelen)  +  'LRU'

               print '%1!', @lru_string

               select @cmdstring = 'select ''CACHE NAME'' = substring (printname, 1,'
                                   +  convert (varchar (2), @cachelen)
                                   +  '),'
                                   +  'TYPE = typetxt,'
                                   +  'STATUS = stattxt,'
                                   +  'POLICY = policy,'
                                   +  'SIZE = size,'
                                   +  'OVERHEAD = overhead'
                                   +  ' from #cache1'
                                   +  ' order by name'

               execute  (@cmdstring)
            end

         print ' '

         insert into #cache2
            select substring (c.name, 1, 48),
                   space (6) + substring (cf.comment, 1, 2),
                   substring (convert (char (12), convert (money, (cc.memory_used / 2)), 1), 1, 9),
                   substring (convert (char (12), convert (money, (cc.value / 2)), 1), 1, 9),
                   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),
                   substring (convert (char (12), convert (money, (cc.memory_used / 2)), 1), 1, 9),
                   substring (convert (char (12), convert (money, (cc.value / 2)), 1), 1, 9),
                   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))

         if (exists (select *
                     from #cache2))
            begin
               update #cache2
               set name = c1.printname
               from #cache2 c2, #cache1 c1
               where c2.name = c1.name

               select @cmdstring = 'select ''CACHE NAME'' = substring (name, 1,'
                                   +  convert (varchar (2), @cachelen)
                                   +  '),'
                                   +  '''I/O SIZE'' = iosize,'
                                   +  '''WASH SIZE'' = washsize,'
                                   +  '''POOL SIZE'' = poolsize,'
                                   +  '''APF%'' = apf'
                                   +  ' from #cache2'
                                   +  ' order by sort'

               execute  (@cmdstring)

               print ' '
            end

         if (charindex ('sa_role', show_role ()) > 0)
            begin
               create unique clustered index UC on #cache3 (db, type, objid)

               execute  sp_globalexec  @UTILITY_SP='sp_cache_db',
                                       @ARG1=@CACHENAME
            end
         else
            begin
               select @dbname = db_name ()

               print '    ONLY BOUND OBJECTS IN %1! WILL BE LISTED.', @dbname

               print ' '

               execute  sp_cache_db  @CACHENAME=@CACHENAME
            end

         if (exists (select *
                     from #cache3))
            begin
               update #cache3
               set db = db + '.'

               if (charindex ('sa_role', show_role ()) > 0)
                  begin
                     select @cachelen = max (char_length (cachename)),
                            @objlen   = max (char_length (db) + char_length (objname))
                     from #cache3

                     if (@CACHENAME IS NULL)
                        begin
                           if ((@cachelen + @objlen) > 65)
                              begin
                                 if (@cachelen > 53)
                                    begin
                                       select @cachelen = 53
                                    end

                                 select @objlen = 65 - @cachelen
                              end

                           select @cmdstring = 'select ''CACHE NAME''=substring (cachename, 1,'
                                               +  convert (varchar (2), @cachelen)
                                               +  '),'
                                               +  '''DATABASE.OBJECT''=substring (db + objname,1,'
                        end
                     else
                        begin
                           if (@objlen > 65)
                              begin
                                 select @objlen = 65
                              end

                           select @cmdstring = 'select ''DATABASE.OBJECT'' = substring (db + objname,1,'
                        end
                  end
               else
                  begin
                     select @cachelen = max (char_length (cachename)),
                            @objlen   = max (char_length (objname))
                     from #cache3

                     if (@CACHENAME IS NULL)
                        begin
                           if ((@cachelen + @objlen) > 65)
                              begin
                                 if (@cachelen > 53)
                                    begin
                                       select @cachelen = 53
                                    end

                                 select @objlen = 65 - @cachelen
                              end

                           select @cmdstring = 'select ''CACHE NAME'' = substring (cachename, 1,'
                                               +  convert (varchar (2), @cachelen)
                                               +  '),'
                                               +  '''OBJECT'' = substring (objname, 1,'
                        end
                     else
                        begin
                           if (@objlen > 53)
                              begin
                                 select @objlen = 53
                              end

                           select @cmdstring = 'select ''OBJECT'' = substring (objname,1,'
                        end
                  end

               select @cmdstring = @cmdstring
                                   +  convert (varchar (2), @objlen)
                                   +  '),'
                                   +  ' TYPE = type'
                                   +  ' from #cache3'
                                   +  ' order by db, objname, substring (type, 1, 6) desc, indid'

               execute  (@cmdstring)
            end
         else
            begin
               if (@CACHENAME IS NULL)
                  begin
                     print '    THERE ARE NO OBJECTS BOUND TO A CACHE.'

                     print ' '
                  end
               else
                  begin
                     print '    THERE ARE NO OBJECTS BOUND TO CACHE %1!.',
                           @CACHENAME

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

         print ' '
      end
   else
      begin
         print '    CACHE %1! DOES NOT EXIST.', @CACHENAME

         print ' '
      end

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

------------ Temporary table needed for creation of sp_db subroutines --------

create table #db
   (dbid         int            default 0           not null,
    name         varchar ( 30)                      not null,
    status       smallint       default 0           not null,
    status2      int            default 0           not null,
    status3      int            default 0           null,
    owner        smallint       default 1           not null,
    crdate       datetime       default getdate ()  not null,
    dumptrdate   datetime       default getdate ()  not null,
    dataflag     bit            default 0           not null,
    logflag      bit            default 0           not null,
    mixedflag    bit            default 0           not null,
    datalogtext  varchar ( 30)  default ' '         not null,
    size         char (11)      default ' '         null,
    dbsize       char (11)      default ' '         null,
    logsize      char (11)      default ' '         null,
    txt          varchar (255)                      null,
    sortname     varchar ( 30)                      not null,
    seq          tinyint                            not null)
go

create procedure sp_db_database (@DATABASE  varchar (30)) as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   declare @cachename   varchar (13),
           @crdate      datetime,
           @formatdate  varchar (32),
           @owner       varchar (30)

   select @cachename = substring (char_value, 1, 13)
   from master.dbo.sysattributes
   where class       = 3
     and attribute   = 0
     and object_type = 'D'
     and object      = db_id (@DATABASE)

   select @crdate = crdate,
          @owner  = isnull (suser_name (suid), '?')
   from master.dbo.sysdatabases
   where name = @DATABASE

   execute  sp_formatdate  @FORMATED_DATE = @formatdate  output,
                           @INUT_DATE     = @crdate

   select OWNER   = @owner,
          CREATED = @formatdate,
          CACHE   = isnull (@cachename, '-')

   print ' '

   if (exists (select *
               from master.dbo.sysprocesses
               where dbid = db_id (@DATABASE)
                 and cmd  = 'LOG SUSPEND'))
      begin
         print '    THE LOG IS SUSPENDED FOR %1!.', @DATABASE

         print ' '
      end

   if (exists (select *
               from master.dbo.syslogshold lh
               where dbid    = db_id (@DATABASE)
                 and name like '$replication_tr%'))
      begin
         print '    DATABASE %1! IS REPLICATED.', @DATABASE

         print ' '
      end

   if (exists (select *
               from master.dbo.sysdatabases
               where name    = @DATABASE
                 and status  = 0
                 and status2 = 0))
      begin
         print '    NO DATABASE OPTIONS ARE SET FOR %1!.', @DATABASE

         print ' '
      end
   else
      begin
            select 'CONFIGURATION TEXT' = convert (char (30), ' '),
                   STATUS               = convert (int, 0),
                   STATUS2              = convert (int, 0),
                   STATUS3              = convert (int, 0)
            where 0 = 1
         union all
            select u.txt,
                   u.dboption,
                   0,
                   0
            from master.dbo.sysdatabases d,
                 sybsystemprocs.dbo.udboption u
            where d.name       = @DATABASE
              and u.dboption   = (d.status & u.dboption)
              and u.dboption2  = 0
              and u.dboption3  = 0
         union all
            select u.txt,
                   0,
                   u.dboption2,
                   0
            from master.dbo.sysdatabases d,
                 sybsystemprocs.dbo.udboption u
            where d.name       = @DATABASE
              and u.dboption   = 0
              and u.dboption2  = (d.status2 & u.dboption2)
              and u.dboption3  = 0
         union all
            select u.txt,
                   0,
                   0,
                   u.dboption3
            from master.dbo.sysdatabases d,
                 sybsystemprocs.dbo.udboption u
            where d.name       = @DATABASE
              and u.dboption   = 0
              and u.dboption2  = 0
              and u.dboption3  = (d.status3 & u.dboption3)
         order by STATUS3, STATUS2, STATUS
      end
go
sp_existsprocedure sp_db_database
go

create procedure sp_db_date as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       off
   set string_rtruncation            on

   declare @bitoff  bit,
           @biton   bit

   select @bitoff = convert (bit, 0)

   select @biton = ~(@bitoff)

   update #db
   set dataflag = @biton
   from #db
   where exists (select u.*
                 from master.dbo.sysusages u
                 where u.dbid          = #db.dbid
                   and (u.segmap & 7) in (0, 1, 2, 3, 5, 6))

   update #db
   set logflag = @biton
   from #db
   where exists (select u.*
                 from master.dbo.sysusages u
                 where u.dbid         = #db.dbid
                   and (u.segmap & 7) = 4)

   update #db
   set mixedflag = @biton
   from #db
   where exists (select u.*
                 from master.dbo.sysusages u
                 where u.dbid         = #db.dbid
                   and (u.segmap & 7) = 7)

   update #db
   set datalogtext = 'separate'
   where mixedflag = @bitoff

   update #db
   set datalogtext = 'mixed'
   where mixedflag = @biton
     and dataflag  = @bitoff
     and logflag   = @bitoff

   update #db
   set datalogtext = 'mixed + log'
   where mixedflag = @biton
     and dataflag  = @bitoff
     and logflag   = @biton

   update #db
   set datalogtext = 'mixed + data'
   where mixedflag = @biton
     and dataflag  = @biton
     and logflag   = @bitoff

   update #db
   set datalogtext = 'mixed + data + log'
   where mixedflag = @biton
     and dataflag  = @biton
     and logflag   = @biton

   select CREATED           = convert (char (8), crdate, 11),
          'DATABASE'        = substring (name, 1, 21),
          OWNER             = substring (isnull (suser_name (owner), '?'), 1, 15),
          'TOTAL PAGES'     = size,
          'DATA/LOG CONFIG' = substring (datalogtext, 1, 18)
   from #db
   order by crdate, name
go
sp_existsprocedure sp_db_date
go

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

   --
   --  NOTE THAT dbsize REPRESENTS USED LOG PAGES IN THIS CODE.
   --

   declare @dbname  char (30),
           @now     varchar (8)

   if (exists (select *
               from #db
               where dbid         = db_id ()
                 and logsize IS NOT NULL))
      begin
         select @dbname = db_name (),
                @now    = convert (char (8), getdate(), 8)

         print '    CHECKING USED LOG SPACE FOR %1! (%2!)', @dbname, @now

         update #db
         set dbsize = (select substring (convert (char (14),
                                                  convert (money,
                                                           data_pgs (id, doampg)),
                                                  1),
                                         1,
                                         11)
                       from sysindexes
                       where id = 8)
         where dbid = db_id ()
      end
go
sp_existsprocedure sp_db_logdate_used
go

create procedure sp_db_logdate as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       off
   set string_rtruncation            on

   update #db
   set logsize = (select substring (convert (char (14),
                                             convert (money,
                                                      sum (u.size)),
                                             1),
                                    1,
                                    11)
                  from master.dbo.sysusages u
                  where u.dbid         = #db.dbid
                    and (u.segmap & 7) = 4)

   if (charindex ('sa_role', show_role ()) > 0)
      begin
         execute  sp_globalexec  @UTILITY_SP='sp_db_logdate_used'

         print ' '
      end

   update #db
   set dbsize  = coalesce (dbsize, '          -'),
       logsize = coalesce (logsize, '          -')

   if (charindex ('sa_role', show_role ()) > 0)
      begin
         select 'LAST XACT DUMP' = convert (char (8), dumptrdate, 11)
                                   +  ' '
                                   +  substring (convert (char (8), dumptrdate, 8), 1, 5),
                'DATABASE'       = substring (name, 1, 16),
                OWNER            = substring (isnull (suser_name (owner), '?'), 1, 9),
                'TOTAL PAGES'    = size,
                'LOG SIZE'       = logsize,
                'LOG USED'       = dbsize
         from #db
         order by dumptrdate, name
      end
   else
      begin
         print '                                                               DEDICATED'

         select 'LAST XACT DUMP' = convert (char (8), dumptrdate, 11)
                                   +  ' '
                                   +  substring (convert (char (8), dumptrdate, 8), 1, 5),
                NAME             = substring (name, 1, 20),
                OWNER            = substring (isnull (suser_name (owner), '?'), 1, 13),
                'TOTAL PAGES'    = size,
                'LOG SIZE'       = logsize
         from #db
         order by dumptrdate, name
      end
go
sp_existsprocedure sp_db_logdate
go

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

   declare @cmdstring   varchar (255),
           @dblen       tinyint,
           @dbseq       smallint,
           @len1        tinyint,
           @len2        tinyint,
           @textlen     tinyint,
           @maxdbid     smallint,
           @maxseq      int,
           @maxtextseq  int,
           @seq1        int,
           @seq2        int

   create table #dbnull
      (dbid  smallint       not null,
       name  varchar ( 30)  not null,
       txt   varchar ( 30)  null,
       seq   smallint       not null)

   create table #dbnull2
      (dbid  smallint       not null,
       name  varchar ( 30)  not null,
       txt   varchar (255)  null,
       sort  varchar ( 30)  not null,
       seq   smallint       not null)

   select @maxtextseq = max (txt_seq),
          @seq1   = 1
   from sybsystemprocs.dbo.udboption

   insert into #dbnull
      select distinct dbid, db_name (dbid), 'LOG SUSPENDED', -3
      from master.dbo.sysprocesses
      where cmd  = 'LOG SUSPEND'

   insert into #dbnull
      select object, db_name (object), 'Cached', -2
      from master.dbo.sysattributes
      where class       = 3
        and attribute   = 0
        and object_type = 'D'

   insert into #dbnull
      select dbid, db_name (dbid), 'Replicated', -1
      from master.dbo.syslogshold
      where name like '$replication_tr%'

   while (@seq1 < @maxtextseq + 1)
      begin
         insert into #dbnull
            select d.dbid, d.name, o.txt, @seq1
            from master.dbo.sysdatabases d,
                 sybsystemprocs.dbo.udboption o
            where o.txt_seq   = @seq1
              and o.dboption  = (d.status & o.dboption)
              and o.dboption2 = 0
              and o.dboption3 = 0

         insert into #dbnull
            select d.dbid, d.name, o.txt, @seq1
            from master.dbo.sysdatabases d,
                 sybsystemprocs.dbo.udboption o
            where o.txt_seq   = @seq1
              and o.dboption  = 0
              and o.dboption2 = (d.status2 & o.dboption2)
              and o.dboption3 = 0

         insert into #dbnull
            select d.dbid, d.name, o.txt, @seq1
            from master.dbo.sysdatabases d,
                 sybsystemprocs.dbo.udboption o
            where o.txt_seq   = @seq1
              and o.dboption  = 0
              and o.dboption2 = 0
              and o.dboption3 = (d.status3 & o.dboption3)

         select @seq1 = @seq1 + 1
      end

   insert into #dbnull
      select d.dbid, d.name, '(No options set)', 0
      from master.dbo.sysdatabases d
      where not exists (select dn.name
                        from #dbnull dn
                        where dn.name = d.name)

   select @dblen   = max (char_length (name)),
          @maxdbid = max (dbid)
   from #dbnull

   select @dbseq    = 1,
          @textlen  = 76 - @dblen

   while (@dbseq <= @maxdbid)
      begin
         select @maxseq = max (seq),
                @seq1   = min (seq),
                @seq2   = 1
         from #dbnull
         where dbid = @dbseq

         insert into #dbnull2
            select dbid, name, txt, name, 1
            from #dbnull
            where dbid = @dbseq
              and seq  = @seq1

         select @seq1 = @seq1 + 1

         while (@seq1 <= @maxseq)
            begin
               select @len1 = char_length (txt)
               from #dbnull
               where dbid = @dbseq
                 and seq  = @seq1

               select @len2 = char_length (txt)
               from #dbnull2
               where dbid = @dbseq
                 and seq  = @seq2

               if ((@len1 + @len2) > @textlen + 3)
                  begin
                     update #dbnull2
                     set txt = txt  +  ','
                     where dbid = @dbseq
                       and seq  = @seq2

                     select @seq2 = @seq2 + 1

                     insert into #dbnull2
                        select dbid, name, txt, name, @seq2
                        from #dbnull
                        where dbid = @dbseq
                          and seq  = @seq1
                  end
               else
                  begin
                     update #dbnull2
                     set txt = s2.txt  +  ', '  +  s1.txt
                     from #dbnull s1, #dbnull2 s2
                     where s2.dbid = @dbseq
                       and s2.seq  = @seq2
                       and s1.dbid = @dbseq
                       and s1.seq  = @seq1
                  end

               select @seq1 = @seq1 + 1
            end

         select @dbseq = min (dbid)
         from #dbnull
         where dbid > @dbseq
      end

   update #dbnull2
   set name = ' '
   where seq > 1

   select @cmdstring = 'select NAME = substring (name, 1,'
                       +  convert (varchar (2), @dblen)
                       +  '),'
                       +  'CONFIGURATION = substring (txt, 1,'
                       +  convert (varchar (2), @textlen)
                       +  ')'
                       +  ' from #dbnull2'
                       +  ' order by sort, seq'

   execute  (@cmdstring)
go
sp_existsprocedure sp_db_null
go

create procedure sp_db_size as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       off
   set string_rtruncation            on

   print '                                                       <----- DEDICATED ----->'

   update #db
   set dbsize  = (select substring (convert (char (14),
                                             convert (money,
                                                      sum (u.size)),
                                             1),
                                    1,
                                    11)
                  from master.dbo.sysusages u
                  where u.dbid          = #db.dbid
                    and (u.segmap & 7) in (0, 1, 2, 3, 5, 6)),
       logsize = (select substring (convert (char (14),
                                             convert (money,
                                                      sum (u.size)),
                                             1),
                                    1,
                                    11)
                  from master.dbo.sysusages u
                  where u.dbid         = #db.dbid
                    and (u.segmap & 7) = 4)

   update #db
   set dbsize  = coalesce (dbsize, '          0'),
       logsize = coalesce (logsize, '          0')

   select CREATED       = convert (char (8), crdate, 11),
          'DATABASE'    = substring (name, 1, 19),
          OWNER         = substring (isnull (suser_name (owner), '?'), 1, 12),
          'TOTAL PAGES' = size,
          'DATA PAGES'  = dbsize,
          'LOG PAGES'   = logsize
   from #db
   order by size, name
go
sp_existsprocedure sp_db_size
go

drop table #db
go

create procedure sp_db (@DATABASE varchar (30) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       off
   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_db CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @dbid        smallint,
           @likestring  varchar ( 30)

   if (@DATABASE IS NULL)
      begin
         execute  sp_db_null
      end
   else if (upper (@DATABASE) in ('DATE', 'LOGDATE', 'SIZE'))
      begin
         create table #db
            (dbid         int            default 0           not null,
             name         varchar ( 30)                      not null,
             status       smallint       default 0           not null,
             status2      int            default 0           not null,
             status3      int            default 0           null,
             owner        smallint       default 1           not null,
             crdate       datetime       default getdate ()  not null,
             dumptrdate   datetime       default getdate ()  not null,
             dataflag     bit            default 0           not null,
             logflag      bit            default 0           not null,
             mixedflag    bit            default 0           not null,
             datalogtext  varchar ( 30)  default ' '         not null,
             size         char (11)      default ' '         null,
             dbsize       char (11)      default ' '         null,
             logsize      char (11)      default ' '         null,
             txt          varchar (255)                      null,
             sortname     varchar ( 30)                      not null,
             seq          tinyint                            not null)

         insert into #db
            select distinct d.dbid, d.name, d.status, d.status2, d.status3,
                            d.suid, d.crdate, d.dumptrdate, 0, 0, 0,
                            'LOGIC ERROR!',
                            substring (convert (char (14),
                                                convert (money,
                                                         sum (u.size)),
                                                1),
                                       1,
                                       11),
                            NULL, NULL, NULL, d.name, 1
            from master.dbo.sysdatabases d,
                 master.dbo.sysusages u
            where d.dbid = u.dbid
            group by d.dbid
            having d.dbid = u.dbid

         if (upper (@DATABASE) = 'DATE')
            begin
               execute  sp_db_date
            end
         else if (upper (@DATABASE) = 'LOGDATE')
            begin
               execute  sp_db_logdate
            end
         else if (upper (@DATABASE) = 'SIZE')
            begin
               execute  sp_db_size
            end
      end
   else
      begin
         select @dbid = dbid
         from master.dbo.sysdatabases
         where name = @DATABASE

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

               print ' '

               execute  sp_like_string  @INPUT_STRING=@DATABASE,
                                        @LIKE_STRING=@likestring  output

               if (exists (select *
                           from master.dbo.sysdatabases
                           where name like @likestring))
                  begin
                     print '    DATABASES LIKE %1! EXIST.', @likestring

                     print ' '

                     select 'DATABASE' = name
                     from master.dbo.sysdatabases
                     where name like @likestring
                     order by 'DATABASE'
                  end
            end
         else
            begin
               execute  sp_db_database  @DATABASE=@DATABASE
            end
      end
go
sp_addcomment sp_db, 'Lists database configurations with various scope/sort options:'
go
sp_addcomment sp_db, '   (NULL):    partial info for all databases (default)'
go
sp_addcomment sp_db, '   DATABASE:  complete status info only for specified database'
go
sp_addcomment sp_db, '   "DATE":    partial info in creation date order'
go
sp_addcomment sp_db, '   "LOGDATE": log info in last xact dump date order'
go
sp_addcomment sp_db, '   "SIZE":    size info in total database size order'
go
sp_procxmode sp_db, 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 @adjust_flag  tinyint,
           @cmdstring    varchar (255),
           @dblen        tinyint,
           @tablen       tinyint,
           @typelen      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 @dblen   = max (char_length (db_name (dbid))),
                @tablen  = max (char_length (tabname)),
                @typelen = max (char_length (ltext))
         from #lockagg

         while ((@dblen + @tablen + @typelen) > 67)
            begin
               select @adjust_flag = 1

               if (@dblen > 15)
                  begin
                     select @dblen = @dblen - 1
                  end

               if (@tablen > 15)
                  begin
                     select @tablen = @tablen - 1
                  end

               if (@typelen > 24)
                  begin
                     select @typelen = @typelen - 1
                  end
            end

         if (@adjust_flag = 1)
            begin
               select @tablen = 67 - @dblen - @typelen
            end

         select @cmdstring = 'select ''DATABASE'' = substring (db_name (dbid), 1,'
                             +  convert (varchar (2), @dblen)
                             +  '),'
                             +  '''TABLE'' = substring (tabname, 1,'
                             +  convert (varchar (2), @tablen)
                             +  '),'
                             +  'TYPE = substring (ltext, 1,'
                             +  convert (varchar (2), @typelen)
                             +  '),'
                             +  '''COUNT'' = Ctext'
                             +  ' from #lockagg'
                             +  ' order by db_name (dbid), tabname, ltext'

         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO LOCKS OTHER THAN IN tempdb FROM THIS EXECUTION.'

         print ' '
      end
go
sp_addcomment sp_lockagg, '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 > 0)
            begin
               set transaction isolation level 0
            end
      end
   else
      begin
         print '    sp_locking CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @adjust_flag  tinyint,
           @cmdstring    varchar (255),
           @dbid         smallint,
           @dbname       varchar ( 30),
           @lgnlen       tinyint,
           @objlen       tinyint,
           @pglen        tinyint,
           @pgsize       int,
           @pgsizetxt    varchar (  8),
           @typelen      tinyint

   if (exists (select *
               from master.dbo.syslocks
               where dbid = 2
                 and id   < 100
                 and ((spid = 1)  or  (spid > 7))))
      begin
         print '    THERE ARE SYSTEM TABLE LOCKS IN tempdb.'

         print ' '

         select @dbid = db_id (@DATABASE)

         select SPID       = spid,
                'TABLE'    = id,
                'PAGE'     = page,
                TYPE       = type,
                CONTEXT    = context,
                'CURSOR'   = class
         from master.dbo.syslocks
         where dbid  = isnull (@dbid, dbid)
           and spid <> @@spid
      end
   else
      begin
         set transaction isolation level 1

         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  left join  master.dbo.sysprocesses p
           on lk.spid = p.spid
         where lk.dbid = @dbid

         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 @lgnlen  = max (char_length (uname)),
                      @objlen  = max (char_length (oname)),
                      @pgsize  = max (abs (page)),
                      @typelen = max (char_length (ltext))
               from #locking

               if (@pgsize < 256)
                  begin
                     select @pglen     = 4,
                            @pgsizetxt = 'tinyint'
                  end
               else if (@pgsize < 32768)
                  begin
                     select @pglen     = 6,
                            @pgsizetxt = 'smallint'
                  end
               else
                  begin
                     select @pglen     = 11,
                            @pgsizetxt = 'int'
                  end

               while ((@lgnlen + @objlen + @pglen + @typelen) > 74)
                  begin
                     select @adjust_flag = 1

                     if (@lgnlen > 15)
                        begin
                           select @lgnlen = @lgnlen - 1
                        end

                     if (@objlen > 6)
                        begin
                           select @objlen = @objlen - 1
                        end

                     if (@objlen > 9)
                        begin
                           select @typelen = @typelen - 1
                        end
                  end

                  if (@adjust_flag = 1)
                     begin
                        select @objlen = (74 - @lgnlen - @pglen - @typelen)
                     end

                  select @cmdstring = 'select ''LOGIN (spid)'' = substring (uname,1,'
                                      +  convert (varchar (2), @lgnlen)
                                      +  '),'
                                      +  '''OBJECT'' = substring (oname, 1,'
                                      +  convert (varchar (2), @objlen)
                                      +  '),'
                                      +  'PAGE = convert ('
                                      +  @pgsizetxt
                                      +  ', page),'
                                      +  '''LOCK TYPE'' = substring (ltext, 1,'
                                      +  convert (varchar (2), @typelen)
                                      +  ')'
                                      +  ' from #locking'
                                      +  ' order by suser_name (suid), spid, oname, page'

                  execute  (@cmdstring)
            end
         else
            begin
               print '    THERE ARE NO LOCKS FOR DATABASE %1!.', @dbname

               print ' '
            end
      end
go
sp_addcomment sp_locking, '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, 14),
                'LOGIN (spid)' = substring (isnull (suser_name (p.suid), '-')
                                            +  ' ('
                                            +  convert (varchar (7), lh.spid)
                                            +  ')',
                                            1,
                                            16),
                PROGRAM        = substring (isnull (p.program_name, '-'), 1, 12),
                'TRANSACTION'  = substring (lh.name, 1, 15),
                '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  left join  master.dbo.sysprocesses p
           on 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, 'Lists oldest transaction information 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, '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.sysservers s  left join  master.dbo.sysremotelogins l
              on s.srvid = l.remoteserverid
            group by s.srvid

         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, '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 flushmessage                  on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   if (exists (select *
               from master.dbo.syslocks
               where dbid = 2
                 and id   < 100
                 and ((spid = 1)  or  (spid > 7))))
      begin
         print '    THERE ARE SYSTEM TABLE LOCKS IN tempdb.'

         print ' '

         select spid,
                suid,
                dbid,
                program_name,
                status,
                cmd,
                blocked
         from master.dbo.sysprocesses
         where dbid = isnull (db_id (@PARM), dbid)
      end
   else
      begin
         set transaction isolation level 1

         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 ('ASTC HANDLER', '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 ('ASTC HANDLER', '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 ('ASTC HANDLER', '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 ('ASTC HANDLER', '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
      end
go
sp_addcomment sp_status, 'Lists processes for DATABASE/LOGIN or (default) everyone'
go
sp_procxmode sp_status, anymode
go

dump transaction sybsystemprocs with truncate_only
go
