SQL Server – Find sql text in active query with a sql derived table

Question: How can I search through a list of active queries on a SQL Server Instance and search for keywords, return the sql query  text and the username ?

Answer:  Use this query to search through active queries .  The query uses the DMVs : dm_exec_sessions , dm_exec_requests and dm_exec_sql_text.  The main query is returned as  sql derived table. If you were to attempt using the WHERE on an alias column as part of the SELECT statement , an “Invalid column” message would appear.  When you use the WHERE on a derived table , it ‘s possible to search on an alias column from the derived table

 
select * from 
 
(
 select OBJECT_NAME(ObjectID) as ObjectName,
  (SELECT TOP 1    SUBSTRING(s2.text,statement_start_offset / 2+1 ,
       ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
        ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement ,
DB_NAME(er.database_ID) as dbname,er.open_transaction_count ,
 es.nt_user_name,es.nt_domain
FROM sys.dm_exec_sessions as es
INNER JOIN sys.dm_exec_requests as er ON er.session_id = es.session_id CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 
) derived_table

where sql_statement LIKE '%my_search_term%'

Follow up

SQL Server – sys.dm_exec_sessions and troubleshooting sql memory usage

Monitoring a Rollback and sys.dm_exec_requests