View The Last 50 Queries Ran on SQL Server

I ran into an issue where I needed to see the last few queries ran on SQL Server (Currently using SQL Server 2005). There were quite a few suggestions out there, but one I found on Stack Overflow seemed to be formated the best and didn’t require me messing with the query. (See Query Below)

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 %'
ORDER BY last_execution_time DESC

This however wasn’t working for me on the database I was in. I checked my compatibility level (code below), due to the suggestions in the comments, and it returned 80, not the expected 90.

SELECT compatibility_level from sys.databases WHERE name=db_name()

I then switched my db to tempdb, ran the query, and it worked. It really doesn’t matter where you run the query, it will pull the last 50 ran on any database. Just make sure the db you run it on has a compatibility level > 80 so you can use the cross apply (tempdb is usually untouched, so it’s a good one to default to).

Notes : I would suggest using the SQL Profiler if you can, however this is a great quick solution when you don’t have time to wait for permissions to change, etc…