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

09 August,2012 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

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


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer