TSQL: Retrieving Recent Ran Queries

Question

Is there a way to get the SQL text for the last few queries?

I am using Microsoft SQL Server 2005

Answer 1

Yes, look, this will give you the 50 most recently executed SQL statements.

sql 2005 and only

SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName, execution_count,s2.objectid, (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, last_execution_time FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %' --and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1 ORDER BY last_execution_time DESC 

Answer 2

If you are using SQL Server 2005 +:

 SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESC 

Great tip from SQLAuthority !

Answer 3

The only way I know about is to start SQL Server Profiler . Unfortunately, this needs to be run before the queries are executed, so if you are hoping to catch something that happened on the "ad hoc", it does not work. If you are trying to keep track of what the code is doing and want it to execute requests, it should work.

Answer 4

If you need to check parameter values, this add-on returns an XML <ParameterList>

 SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName, execution_count,s2.objectid, (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, SUBSTRING( s3.query_plan,CHARINDEX('<ParameterList>',s3.query_plan), CHARINDEX('</ParameterList>',s3.query_plan) + LEN('</ParameterList>') - CHARINDEX('<ParameterList>',s3.query_plan) ) AS Parameters, last_execution_time FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 CROSS APPLY sys.dm_exec_text_query_plan(s1.plan_handle, s1.statement_start_offset, s1.statement_end_offset) AS s3 ) x WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %' ORDER BY last_execution_time DESC 

Preconfirmed Version