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