Как-то давно позаимствовал SP у Вьейры и было это удобно (это небольшая модификация sp_lock).
USE master
GO
CREATE PROC sp_DBLocks
@DBNameIn sysname = NULL,
@ObjName sysname = NULL
WITH RECOMPILE
AS
/* Sproc Authored and Copyright By Robert M. Vieira, MCDBA 1999, 2000 */
/* This sproc is made availabe for public, non-commercial use */
/* All other rights reserved */
-- Declare holding variable for dynamic query
DECLARE @cmd varchar(1000)
DECLARE @DBName sysname
-- If no database was supplied, then assume current database
SELECT @DBName = ISNULL(@DBNameIn, DB_NAME())
-- Verify that the supplied database exists
IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases (NOLOCK)
WHERE Name = @DBName)
BEGIN
RAISERROR(15010,1,1,@DBname) WITH SETERROR
RETURN 15010
END
-- Build it (and they will come...)
SELECT @cmd =
' SELECT convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
so.Name As ObjectName,
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 sli (NOLOCK)
JOIN master.dbo.spt_values v (NOLOCK)
ON sli.rsc_type = v.number
AND v.type = ''LR''
JOIN master.dbo.spt_values x (NOLOCK)
ON sli.req_status = x.number
AND x.type = ''LS''
JOIN master.dbo.spt_values u (NOLOCK)
ON sli.req_mode + 1 = u.number
AND u.type = ''L''
JOIN master.dbo.sysdatabases sd (NOLOCK)
ON sli.rsc_dbid = sd.dbid
JOIN ' + @DBName + '.dbo.sysobjects so (NOLOCK)
ON sli.rsc_objid = so.id
WHERE sd.name = ''' + @DBName + ''''
-- If an object name was supplied, add the piece that will restrict to supplied name
-- Otherwise, leave it off to get all objects in the specified DB
IF NOT (@ObjName IS NULL)
SELECT @CMD = @CMD + ' AND so.Name = ''' + @ObjName + ''''
-- I'm Ordering by spid, but by Object Name seems a likely candidate if one wasn't supplied
SELECT @CMD = @CMD + ' ORDER BY spid'
-- Create a little header
IF @ObjName IS NULL
PRINT 'Resource Locks for Database ' + @DBName
ELSE
PRINT 'Resource Locks for Database ' + @DBName + ', Resource ' + @ObjName
-- We've built it, time for them to come....
EXEC (@cmd)
-- Hopefully, this is zero
RETURN @@ERROR
GO