Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

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

09 August,2012 by Tom Collins

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 ,
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

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server – Find sql text in active query with a sql derived table | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer