Check Store Proc Process Inside

/*

Check Store Proc Process Inside

*/

SELECT  spid,

        sp.[status],

        loginame [Login],

        hostname,

        blocked BlkBy,

        sd.name DBName,

        cmd Command,

        cpu CPUTime,

        physical_io DiskIO,

        last_batch LastBatch,

        [program_name] ProgramName  

FROM master.dbo.sysprocesses sp

JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid

where sp.[status] = 'runnable                      '

ORDER BY spid

SELECT  requests.session_id,

        requests.status,

        requests.command,

        requests.statement_start_offset,

        requests.statement_end_offset,

        requests.total_elapsed_time,

        details.text

FROM    sys.dm_exec_requests requests

CROSS APPLY sys.dm_exec_sql_text (requests.plan_handle) details

WHERE   requests.session_id > 50

ORDER BY total_elapsed_time DESC

SELECT  SUBSTRING(detail.text,

                  requests.statement_start_offset / 2,

                  (requests.statement_end_offset - requests.statement_start_offset) / 2)

FROM    sys.dm_exec_requests requests

CROSS APPLY sys.dm_exec_sql_text (requests.plan_handle) detail

WHERE   requests.session_id = 68 -- this is the id of the store proc running.