Reading the text of a running query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Reading the text of a running query

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-08-07 : 03:23:04

Morning all

I'm not entirely sure if this is even possible.

I'd like to be able to run through the list of currently running queries on the server and look for certain keywords (or lack of) and get the query text and the username that it was initiated by.

I've Googled like mad and got nowhere.

Does anyone know if what I want to do is possible?

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2012-08-07 : 03:29:09

This will help you...

SELECT
OBJECT_NAME(ObjectID) as ObjectName,
st.Text,
DB_NAME(database_ID) as dbname,
session_id, sp.open_tran, nt_username, nt_domain
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
LEFT JOIN sys.sysprocesses sp ON r.session_id=sp.spid

------------------------------------------------
The answer is always no till than you don't ask.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-08-07 : 03:37:24

Works perfectly, thank you.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2012-08-07 : 03:39:06

Your Welcome..!!!

quote:
Originally posted by rmg1

Works perfectly, thank you.

------------------------------------------------
The answer is always no till than you don't ask.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-07 : 04:49:01

Also try Adam Machanics script spWhoIsActive

N 56°04'39.26"
E 12°55'05.63"

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-08-07 : 05:35:00

I've used spWho2 before now to see who's hoging the CPU but I wanted to check for keywords in running scripts.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-07 : 11:00:13

Just to mention , sys.sysprocesses is a deprecated feature (due to be not included in future versions). This query returns similar information - but using the DMV.

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

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources