Indepth SPID information

USE MASTER
GO

IF OBJECT_ID ('DBO.OPS_SP_DetailSPID') IS NOT NULL
DROP PROC DBO.OPS_SP_DetailSPID 
GO

CREATE PROC DBO.OPS_SP_DetailSPID @spid int 
AS

SELECT
SP.SPID, SP.status Status, SP.cmd Command, A.percent_complete PercentComplete, 
SP.Blocked BlockedBy, DB_NAME(SP.dbid) AS DatabaseName, SP.hostname Hostname,
SP.physical_io Physical_IO, SP.cpu CPU, SP.waittype WaitType, (SP.waittime/1000) WaitTimeSec, 
SP.lastwaittype LastWaitType, SP.waitresource WaitResource,
SP.login_time LoginTime, A.total_elapsed_time/1000 BatchElapsedTimeSec,
(SELECT SUBSTRING(C.text,A.statement_start_offset/2,(CASE WHEN A.statement_end_offset = -1 
THEN LEN(CONVERT(nvarchar(max), C.text)) * 2 ELSE A.statement_end_offset END -A.statement_start_offset)/2)) SQLBatchText,
C.text SQLStatementText,
B.query_plan as QueryPlan
FROM sys.sysprocesses AS SP
INNER JOIN sys.dm_exec_requests as A
ON SP.SPID = A.session_id
CROSS APPLY sys.dm_exec_query_plan(A.plan_handle) as B
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) as C
WHERE SP.spid = @spid