SQLServer查询历史执行记录的方法实现

目录

• 1. 使用 SQL Server Management Studio (SSMS) 的查询执行记录功能：
• 2. 使用系统视图：

SELECT TOP 100 *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY last_execution_time DESC;

SELECT TOP 100
    qs.last_execution_time,
    DB_NAME(st.dbid) AS database_name,
    OBJECT_NAME(st.objectid, st.dbid) AS object_name,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1) AS executed_sql
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.last_execution_time DESC;