Код

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