How to extract query text of a SPID.

Problem: How to know what query running inside a session using SPID.

Solution: sys.dm_exec_sql_text

Under Dynamic Management Views and functions there is a system function which stores the query text of all the executed queries against databases, servers. This system function accepts a parameter to show  

Also there is an alternative function fn_get_sql, but this is going to be deprecated in future versions.

Example:

In this article I am going to show you how we can retrieve query of a SPID.

First let’s generate some activity to find the query text inside the session, I am going to update a table and add some delay to the query to trace it from the execution requests.

Example 1:

-- query text of a session.

select dest.*

from  sys.dm_exec_requests as der

             cross apply sys.dm_exec_sql_text (der.sql_handle) as dest

where session_id = @@spid

OUTPUT:

session_id	status	text
54	suspended	Update dbo.History SET Entry_Date=DATEADD(DD,1,Entry_Date)  waitfor delay '00:00:10'

Featured Script:

--All the necessary information about the sessions

SELECT

    SPID                = er.session_id

    ,STATUS             = ses.STATUS

    ,[Login]            = ses.login_name

    ,Host               = ses.host_name

    ,BlkBy              = er.blocking_session_id

    ,DBName             = DB_Name(er.database_id)

    ,CommandType        = er.command

    ,SQLStatement       = st.text

    ,ObjectName         = OBJECT_NAME(st.objectid)

    ,ElapsedMS          = er.total_elapsed_time

    ,CPUTime            = er.cpu_time

    ,IOReads            = er.logical_reads + er.reads

    ,IOWrites           = er.writes

    ,LastWaitType       = er.last_wait_type

    ,StartTime          = er.start_time

    ,Protocol           = con.net_transport

    ,ConnectionWrites   = con.num_writes

    ,ConnectionReads    = con.num_reads

    ,ClientAddress      = con.client_net_address

    ,Authentication     = con.auth_scheme

FROM sys.dm_exec_requests er

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

LEFT JOIN sys.dm_exec_sessions ses

ON ses.session_id = er.session_id

LEFT JOIN sys.dm_exec_connections con

ON con.session_id = ses.session_id

To learn about CROSS APPLY, SPID please go through the following  articles:
1. CROSS APPLY,
2. SPID