Codeman
3/23/2007 12:53:00 AM
"theredmiata@hotmail.com" wrote:
> All,
>
> I've been playing around with various forms of the sp_who2 script
> which shows quite a bit of current SQL activity. What I am now looking
> to accomplish is to show given a table name, who has this table locked
> and who is waiting to lock this table?
>
> Any help would be appreciated...
>
> Ian in San Diego
>
>
This should be a good start:
Select
tb.[Name] As [TableName],
Case (lk.req_Status)
When 1 Then 'Granted'
When 2 Then 'Converting'
When 3 Then 'Waiting'
End [LockStatus],
pr.loginame As [LoginName],
pr.nt_domain As [Domain],
pr.nt_username As [UserName]
From
sys.syslockInfo lk
Inner Join
sys.Tables tb
On lk.rsc_objid = tb.Object_ID
Inner Join
sys.sysProcesses pr
On pr.spid = lk.req_spid
Where
tb.[Name] = '<TableName>'