create proc [dbo].[sp_lockinfo]
@kill_lock_spid bit=0, --ÊÇ·ñɱµô×èÈûµÄ½ø³Ì,1 ɱµô, 0 ½öÏÔʾ
@show_spid_if_nolock bit=0, --Èç¹ûûÓÐ×èÈûµÄ½ø³Ì,ÊÇ·ñÏÔʾÕý³£½ø³ÌÐÅÏ¢,1 ÏÔʾ,0 ²»ÏÔʾ
@dbname sysname='' --Èç¹ûΪ¿Õ,Ôò²éѯËùÓеĿâ,Èç¹ûΪnull,Ôò²éѯµ±Ç°¿â,·ñÔò²éѯָ¶¨¿â
as
set nocount on
declare @count int,@s nvarchar(2000),@dbid int
if @dbname='' set @dbid=db_id() else set @dbid=db_id(@dbname)
select id=identity(int,1,1),±êÖ¾,
½ø³ÌID=spid,Ïß³ÌID=kpid,¿é½ø³ÌID=blocked,Êý¾Ý¿âID=dbid,
Êý¾Ý¿âÃû=db_name(dbid),Óû§ID=uid,Óû§Ãû=loginame,ÀÛ¼ÆCPUʱ¼ä=cpu,
µÇ½ʱ¼ä=login_time,´ò¿ªÊÂÎñÊý=open_tran, ½ø³Ì״̬=status,
¹¤×÷Õ¾Ãû=hostname,Ó¦ÓóÌÐòÃû=program_name,¹¤×÷Õ¾½ø³ÌID=hostprocess,
ÓòÃû=nt_domain,Íø¿¨µØÖ·=net_address
into #t from(
select ±êÖ¾='×èÈûµÄ½ø³Ì',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses
where blocked>0
and(@dbid is