【IT168技术文档】

 　　下面介绍一个例子。该例子用于查询存储过程中所在的数据库的对象的锁住情况，可适用于MS SQL Server7.0 和 MS SQL Server 2000。

CREATE PROCEDURE dbo.usp_lock AS
set nocount on
select p.hostname,
l.req_spid as spid,
l.rsc_objid,
left(v.name,4) As Type,
left(u.name,8) As Mode,
left(x.name,6) As lockStatus,
p.program_name,
p.loginame,
p.status as status,
p.blocked
into #locks
from master.dbo.syslockinfo l
join (select name,number from master.dbo.spt_values v where type = 'LR') v on l.rsc_type = v.number
join (select name,number from master.dbo.spt_values v where type = 'L') u on l.req_mode + 1 = u.number
join (select name,number from master.dbo.spt_values v where type = 'LS') x on l.req_status = x.number
join master.dbo.sysprocesses p on l.req_spid=p.spid
where l.rsc_dbid=db_id()
and l.rsc_objid>0
select l.hostname,
l.spid,
isnull(o.name,' ') as objname,
l.Type,
l.Mode,
l.lockStatus,
l.program_name,
l.loginame,
l.status,
l.blocked,
isnull(p.hostname,' ') as blockhostname,
isnull(p.program_name,' ') as blockprogram
from #locks l
join sysobjects o on l.rsc_objid=o.id
left join master.dbo.sysprocesses p on l.blocked=p.spid
order by o.name,l.hostname
drop table #locks
GO