Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Sybase » Administration » Monitoring » Utilities

Display Locks on Tables

     





This procedure will display the names of the locked tables
(shared, update, etc)
across databases.








create procedure sp_locks
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to c heck for locks */
as

declare @length int
declare @msg varchar(250)



if @@trancount = 0
begin
set transaction isolation level 1
set chained off
end

/* Print warning message about cursor lock info:
** 18052, "The class column will display the cursor
name for locks associated
** with a cursor for the current user and the cursor id for other
** users."
*/
exec sp_getmessage 18052, @msg out
print @msg

/*
** Show the locks for both parameters.
*/
if @spid1 is not NULL
begin
select @length = max(datalength(db_name(dbid)))
from master..syslocks
where spid in (@spid1, @spid2)

if (@length > 15)

select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = db_name(dbid), class
from master..syslocks l, master..spt_values v
where l.type = v.number
and v.type = "L"
and spid in (@spid1, @spid2)
else
select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = convert(char(15), db_name(dbid)), class
from master..syslocks l,
master..spt_values v
where l.type = v.number
and v.type = "L"
and spid in (@spid1, @spid2)
end

/*
** No paramete
rs, so show all the locks.
*/
else
begin
select @length = max(datalength(db_name(dbid)))
from master..syslocks

if (@length
> 15)
select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = db_name(dbid), class
from master..syslocks l, master..spt_values v
where l.type = v.number
and v.type = "L"
order by spid, dbname, table_name, locktype, page

else
select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = convert(char(15), db_name(dbid)), class
from master..syslocks l, master..spt_values v
where l.type = v.number
and v.type = "L"
order by spid, dbname, table_name, locktype, page
end

return (0)













Sybase : Related Topics

Sybase : Administration : Transaction Log Checking: Wait Until It Clears

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.