-- process
SELECT A.SPID,
A.BLOCKED,
A.NT_USERNAME,
A.HOSTNAME,
A.PROGRAM_NAME,
A.CMD,
A.STATUS,
A.LOGINAME,
A.LOGIN_TIME,
A.LAST_BATCH
FROM MASTER.DBO.SYSPROCESSES A
WHERE A.HOSTPROCESS <> ''
-- lock
SELECT DISTINCT
B.SPID,
B.NT_USERNAME,
B.HOSTNAME,
B.PROGRAM_NAME,
B.CMD,
B.STATUS,
B.LOGINAME,
B.LOGIN_TIME,
B.LAST_BATCH
FROM MASTER.DBO.SYSLOCKINFO A
JOIN MASTER.DBO.SYSPROCESSES B ON A.REQ_SPID = B.SPID
JOIN MASTER.DBO.SPT_VALUES C ON A.RSC_TYPE = C.NUMBER
AND C.TYPE = 'LR'
JOIN MASTER.DBO.SPT_VALUES D ON A.REQ_STATUS = D.NUMBER
AND D.TYPE = 'LS'
JOIN MASTER.DBO.SPT_VALUES E ON A.REQ_MODE + 1 = E.NUMBER
AND E.TYPE = 'L'
AND E.NAME = 'X'
ORDER BY B.SPID