TSQL: Get Last Queries Ran

Question

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

I am using Microsoft SQL Server 2005

Kulda

Answer 1

Yes, take a look, this will give you the 50 most recent executed SQL statements

sql 2005 and up 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

Shopping

37000(321)[Microsoft][ODBC SQL Server Driver][SQL Server]"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90. – Kulda

Like I said 2005 and up only, for 2000 run profiler. Next time indicate which version of sql server you are running – Shopping

Microsoft SQL Server 2005 - 9.00 – Kulda

Not according to the error. Run this and see if it returns 90 or not.....select compatibility_level from sys.databases where database_id = DB_ID() – Shopping

@SQLMenance: Compatibility_Level = 80. Weird because Select @@Version Returns 2005. Thank you for helping me troubleshoot. – Kulda

EXCELLENT!!!....THANK YOU....SAVED MY LIFE ;) Because I had to look at all the queries (not just Top 50)...It was easy for me to copy and past the query results into notepad and then CTRL+F to search for what I remembered was in the query...took me right to what I was looking for – Ozone

Is there a way to get the parameters used as well? – Haddad

How would I filter this to return only queries run against a specific database on the server? – Ozzie

@Ozzie did you ever figure out how to limit to one database? You can add to the WHERE clause: AND sql_statement LIKE '%dbname%' but that isn't reliable and there must be a better way. – Malamut

@jitendragarg: sys.dm_exec_query_stats has a plan_handle, which you can lookup in sys.dm_exec_query_plan to get the XML for the plan. Near the end of the plan XML is a <ParameterList> element containing the parameters used to generate the plan. In the case of an ad-hoc query, this will contain all the parameter values. Very handy. – Sickening

Answer 2

If 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!

Prior

@Michael Potter: ALTER DATABASE <database_name> SET COMPATIBILITY_LEVEL = 90; – Genitive

Answer 3

If you need to inspect parameter values, this addition returns the <ParameterList> XML

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

Prettified version

Creditable

Answer 4

The only way I'm aware of is to have the SQL Server Profiler running. Unfortunately this needs to be started prior to the queries being executed, so if you're hoping to catch something that's happened on an "ad hoc" basis, it won't be suitable. If you're trying to track what a piece of code's doing and want to capture the queries it executes, it should work a treat.

Specs