--create procedure sp_WhoLock with ENCRYPTION as
-------------------------------------------------------------
-- 一、根据sp_who改编产生SPID对应的用户#who表
-------------------------------------------------------------
if (object_id('tmp_dbuser') is not null)
drop table tmp_dbuser
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
declare @loginame sysname
set @loginame= NULL
set nocount on
declare @retcode int
declare @sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame
IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame) ,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
IF (lower(@loginame) IN ('active')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
begin
select @spidlow = convert(int, @loginame) ,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end
SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame)) as loginname
,spid as 'spid_sort'
,substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char'
INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
--------Screen out any rows?
IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping' and
upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
) and
blocked = 0
--------Prepare to dynamically optimize column widths.
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
into tmp_dbuser --Added line by 王建军
from #tb1_sysprocesses --Usually DB qualification is needed in exec().
where spid >= ' + @charspidlow + '
and spid <= ' + @charspidhigh + '
-- (Seems always auto sorted.) order by spid_sort
SET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
sid >= ' + @charsidlow + '
and sid <= ' + @charsidhigh + '
and
**************/
LABEL_86RETURN:
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
-----------------------------------------------------------
-- 显示锁定资源 选自sp_lock系统存储过程
-----------------------------------------------------------
select lock.spid,
who.dbname as 数据库,
object_name(lock.objId) as 被锁定表,
lock.type as 锁类型,
lock.mode as 锁模式,
lock.status as 锁状态,
who.hostname as 用户主机,
who.login as 登录名,
who.programname as 应用程序,
who.status as 用户状态,
lock.indid,
lock.resource
from
(
select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
) lock,tmp_dbuser who
where lock.spid=who.spid and who.dbname=db_name() and lock.objid>0
order by lock.spid
if (object_id('tmp_dbuser') is not null)
drop table tmp_dbuser
GO