What is the syntax of the query which is locking data?

Image Amagill

In live SQL environments queries can be locked by other queries. In the tables master..sysprocesses you can see by which SPID is blocking your query: 
SELECT Blocked FROM Master..Sysprocesses WHERE Blocked <> Spid and Blocked <> 0

However it is nice to know which SPID is blocking, but it more interesting to know what the syntax is of this blocking query. With next script you can retrieve the query syntax of the Spid which is locking the data.


SET NOCOUNT ON

DECLARE DB CURSOR FOR -- Select all SPID's which creates a lock
SELECT SP.blocked, SB.Name
FROM Master..Sysprocesses SP
INNER JOIN Master..Sysdatabases SB ON SP.DBID = SB.DBID
WHERE SP.blocked <> 0 AND SP.blocked <> SP.spid

OPEN DB
DECLARE @LOCKSPID Nvarchar (10); -- Spid of locking transaction
DECLARE @DBNAME Nvarchar(256); -- Dbname in which a locked transaction is.
DECLARE @Handle Nvarchar(256);
DECLARE @SQL nvarchar(4000); -- Generated SQL Script which will be executed against all databases

FETCH NEXT FROM DB INTO @LOCKSPID,@DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN

-- Generate SQL script for the first database in paramater @SQL
SET @Handle = '(Select sql_handle FROM Master..Sysprocesses WHERE Spid = ' + @LOCKSPID +')'
SET @SQL = 'SELECT Text FROM ::fn_get_sql('+ @Handle +')'
EXEC SP_EXECUTESQL @SQL -- Execute the generated script.

FETCH NEXT FROM DB INTO @LOCKSPID,@DBNAME -- Generate and execute script for the next locked query

END
CLOSE DB
DEALLOCATE DB


Previous script will work on SQL 2005 and SQL 2008. For SQL 2000 a hotfix is available.