dump tran sybsystemprocs with truncate_only
go

use sybsystemprocs
go

if exists (select * from sysobjects where name = "sp_block" and type = 'P')
   drop proc sp_block
go

create procedure sp_block @dbname varchar(30) = "%"
as

/*
 * Name:   sp_block
 * Date:   Jan 10 1994 
 * Author: Mike Chachich (mdchachi@vela.acs.oakland.edu) 
 *
 * Modification History:
 * Date        Who            Description
 * ----------  ---            -------------------------------------------
 * 01-10-1994  Mike Chachich  Initial Version
 * 08-02-1995  Teresa Larson  modified ?? (larson@sled.gsfc.nasa.gov)
 * 08-01-1995  TLarson 	      per suggestion from Sharkey, Doug" 
 *  .   Added isnull(object_name(id,syslock.dbid) 
 *  so user no longer have to be using the database to get the proper object 
 *  name.
 * 09-01-1999  TBoss	      modified; had to fully qualify the calls to
 *  the "id" field upon installing in 11.5 for the first time.
 ****************
 * There is no warranty expressed or implied and the use of this code
 * is at your own risk.  Specifically, claims that this code causes SQL
 * Server to generate obscene messages will be vehemently denied.
 *
 * This procedure is very similar to sp_lock except that it provides more
 * information and prints out the name of locked tables that are within the 
 * current database.  It also lists any blocked processes and who they are
 * blocked by.  The use of multiple 'select' and 'print' statements was
 * avoided in order to use this in the current version of PowerBuilder's DBA 
 * Painter (in PB, print is treated like raiserror and each select is treated
 * as a separate result set)
 * 
 * Note:  this procedure, as is, is not compatible with
 *        versions of SQL Server older than 4.2
 * 
 * Usage : sp_block [database-name]
 *
 */
 
SELECT
   /* display all locks */
   syslocks.spid, 
   pid=sysprocesses.hostprocess,
   userid=substring(sysusers.name,1,7),
   dbname=substring(db_name(syslocks.dbid), 1, 15),
   /* "table / blcked_by"=convert(varchar(17),isnull(object_name(id, syslocks.dbid),convert(varchar(30),id))), */
   "table / blcked_by"=convert(varchar(17),isnull(object_name(syslocks.id, syslocks.dbid),convert(varchar(30),syslocks.id))),
   lock_type=convert(varchar(14),spt_values.name), 
   page=convert(varchar(10),syslocks.page), 
   status=substring(sysprocesses.status, 1, 10),
   program=substring(program_name,1,15), 
   command=substring(cmd,1,10) 
   FROM master.dbo.syslocks syslocks, 
        master.dbo.spt_values spt_values, 
        master.dbo.sysprocesses sysprocesses, 
        master.dbo.syslogins sysusers
   WHERE syslocks.type = spt_values.number
        and spt_values.type = 'L'
        and syslocks.spid = sysprocesses.spid
        and sysusers.suid = sysprocesses.suid
        and db_name(syslocks.dbid) like @dbname
UNION
SELECT 
    /* display all blocked processes */
    sp1.spid, 
    pid=sp1.hostprocess,
    userid=substring(suser_name(sp1.suid),1,7),
    dbname=substring(db_name(sp1.dbid),1,15),
    blk_user=convert(char(3),sp1.blocked) + substring(suser_name(sp2.suid),1,14),
    locktype=null,
    page=null,
    status=substring(sp1.status, 1, 10),
    program=substring(sp1.program_name,1,15), 
    command=substring(sp1.cmd,1,10) 
FROM master.dbo.sysprocesses sp1, master.dbo.sysprocesses sp2
WHERE sp1.blocked = sp2.spid

go

if object_id('sp_block') is not null
begin
    print '<<< Created procedure dbo.sp_block >>>'
    grant execute on dbo.sp_block to public
end
else
begin
    print '<<< Failed creating proc dbo.sp_block >>>'
end
go