特定数据库的上次执行查询

我知道如何使用下面的 SQL 在 SSMS-中获取最后执行的查询

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

但我想找到他们的特定数据库。如果没有必要，我不想使用 SQL Profiler。另外，我不认为 SQLProfiler 允许我查看未启用分析就已经运行的查询。我需要通过 SSMS 做这件事。

296919 次浏览

小开

最佳答案

This works for me to find queries on any database in the instance. I'm sysadmin on the instance (check your privileges):

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('msdb')
ORDER BY deqs.last_execution_time DESC

This is the same answer that Aaron Bertrand provided but it wasn't placed in an answer.

小开

Following works perfectly for me : hope this helps

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