check for blocks in database

IF OBJECT_ID('dbo.sp_dba_dblock') IS NOT NULL

BEGIN

    DROP PROCEDURE dbo.sp_dba_dblock

    IF OBJECT_ID('dbo.sp_dba_dblock') IS NOT NULL

        PRINT '<<<>>>'

    ELSE

        PRINT '<<<>>>'

END

go

create procedure sp_dba_dblock

as

declare @PRNTBUFFER varchar(150),

@CURRENTDB varchar(30),

@CURRENTDBID smallint,

        @LOCKCOUNT int

select @CURRENTDB = rtrim(db_name())

select @CURRENTDBID = db_id()

select @LOCKCOUNT = count(dbid)

       from master.dbo.syslocks

       where master.dbo.syslocks.dbid = @CURRENTDBID

if ( @LOCKCOUNT = 0 )

begin

select @PRNTBUFFER = " " + replicate("=",68)

print @PRNTBUFFER

select @PRNTBUFFER = " No Locks In Database : " + rtrim(@CURRENTDB)

print @PRNTBUFFER

                select @PRNTBUFFER = " " + replicate("=",68) 

                print @PRNTBUFFER 

end

else

  begin

                select @PRNTBUFFER = " " + replicate("=",68) 

                print @PRNTBUFFER 

                select @PRNTBUFFER = " Locks In Database : " + rtrim(@CURRENTDB) 

                print @PRNTBUFFER 

                select @PRNTBUFFER = " " + replicate("=",68)  

                print @PRNTBUFFER  

    select Spid = convert(char(4),master.dbo.syslocks.spid),

           SybaseUser = convert(char(12),master.dbo.syslogins.name),

           LockType = convert(char(14),master.dbo.spt_values.name),

           TableName = convert(char(18),object_name(master.dbo.syslocks.id)),

           PageNumber = convert(char(14),master.dbo.syslocks.page),

  UnixHost = convert(char(8),master.dbo.sysprocesses.hostname),

           UnixPid = convert(char(8),master.dbo.sysprocesses.hostprocess)

      from master.dbo.syslocks,

           master.dbo.spt_values,

           master.dbo.syslogins,

           master.dbo.sysprocesses

     where master.dbo.syslocks.type = master.dbo.spt_values.number

       and master.dbo.sysprocesses.suid = master.dbo.syslogins.suid

       and master.dbo.spt_values.type = "L"

       and master.dbo.syslocks.spid = master.dbo.sysprocesses.spid

       and master.dbo.syslocks.dbid = @CURRENTDBID

     order by 1,4,5

  end

return (0)

go

EXEC sp_procxmode 'dbo.sp_dba_dblock','unchained'

go

IF OBJECT_ID('dbo.sp_dba_dblock') IS NOT NULL

    PRINT '<<<>>>'

ELSE

    PRINT '<<<>>>'

go

USE sybsystemprocs

go

GRANT EXECUTE ON dbo.sp_dba_dblock TO public