SQL Server查询指定表的操作记录

首先请确保您的数据库兼容模式设置为 90，以下语句才会成功！

右键数据库->属性->选项->兼容级别调整为sql server 2005 以上

 

SELECT TOP 1000
QS.creation_time, 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 statement_text, ST.text, QS.total_worker_time, QS.last_worker_time, QS.max_worker_time, QS.min_worker_time

FROM
sys.dm_exec_query_stats QS
--关键字
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE
QS.creation_time BETWEEN '2020-11-10 00:00:00' AND '2020-11-17 00:00:00'
-- AND ST.text NOT LIKE '%SELECT * FROM T_LOCATIONINFO WHERE STRCLIPLOGICID in(%'
AND ST.text LIKE '%Txj_web_user_record%'
ORDER BY
QS.creation_time DESC

官方文档：

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sql-text-transact-sql?view=sql-server-2017

 

sys.dm_exec_sql_text (Transact-SQL)

• 2017/10/20
• • 
  • 

适用于： SQL Server（所有支持的版本） Azure SQL 数据库

返回由指定 sql_handle 标识的 SQL 批处理的文本。 该表值函数将替换系统函数 fn_get_sql。

语法

复制

 

sys.dm_exec_sql_text(sql_handle | plan_handle)  

参数

sql_handle
是一个标记，用于唯一标识已执行或当前正在执行的批处理。 sql_handle 为 varbinary (64)。

可以从以下动态管理对象中获取 sql_handle ：

• sys.dm_exec_query_stats

• sys.dm_exec_requests

• sys.dm_exec_cursors

• sys.dm_exec_xml_handles

• sys.dm_exec_query_memory_grants

• sys.dm_exec_connections

plan_handle
是一个标记，用于唯一标识已执行并且其计划驻留在计划缓存中或当前正在执行的批处理的查询执行计划。 plan_handle 为 varbinary (64)。

可以从以下动态管理对象中获取 plan_handle ：

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (Transact-sql)

• sys.dm_exec_trigger_stats (Transact-sql)

返回的表

返回的表

列名称	数据类型	说明
dbid	smallint	数据库的 ID。 对于临时和预定义 SQL 语句，指编译这些语句时所在的数据库的 ID。
objectid	int	对象的 ID。 对于临时和预定义 SQL 语句为 NULL。
数字	smallint	对于带编号的存储过程，此列返回存储过程的编号。 有关详细信息，请参阅 (transact-sql)sys.numbered_procedures 。 对于临时和预定义 SQL 语句为 NULL。
过	bit	1 = SQL 文本已加密。 0 = SQL 文本未加密。
text	nvarchar (max )	SQL 查询的文本。 对于已加密对象为 NULL。

权限

要求具有对服务器的 VIEW SERVER STATE 权限。

备注

对于即席查询，SQL 句柄是基于提交给服务器的 SQL 文本的哈希值，并且可以来自任何数据库。

对于诸如存储过程、触发器或函数之类的数据库对象，SQL 句柄派生自数据库 ID、对象 ID 和对象编号。

计划句柄是派生自整个批处理的已编译计划的一个哈希值。

 备注

无法根据即席查询 sql_handle 确定 dbid 。 若要确定适用于即席查询的 dbid ，请改用 plan_handle 。

示例

A. 概念示例

下面是一个基本示例，演示如何直接或通过 交叉应用 传递 sql_handle 。

1. 创建活动。
   在的新查询窗口中执行以下 T-sql SQL Server Management Studio 。

   SQL复制

    

   -- Identify current spid (session_id)
   SELECT @@SPID;
   GO
   
   -- Create activity
     WAITFOR DELAY '00:02:00';
   

2. 使用 交叉应用。
   将使用 跨应用 将 sys.dm_exec_requests 中的 sql_handle 传递给 sys.dm_exec_sql_text 。 打开一个新的查询窗口，并传递步骤1中标识的 spid。 在此示例中，spid 的行为是 59 。

   SQL复制

    

   SELECT t.*
   FROM sys.dm_exec_requests AS r
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
   WHERE session_id = 59 -- modify this value with your actual spid
   

3. 直接传递 sql_handle 。
   从 sys.dm_exec_requests 中获取 sql_handle 。 然后，将 sql_handle 直接传递到 sys.dm_exec_sql_text。 打开一个新的查询窗口，并将步骤1中标识的 spid 传递到 sys.dm_exec_requests。 在此示例中，spid 的行为是 59 。 然后将返回的 sql_handle 作为参数传递给 sys.dm_exec_sql_text。

   SQL复制

    

   -- acquire sql_handle
   SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id = 59  -- modify this value with your actual spid
   
   -- pass sql_handle to sys.dm_exec_sql_text
   SELECT * FROM sys.dm_exec_sql_text(0x01000600B74C2A1300D2582A2100000000000000000000000000000000000000000000000000000000000000) -- modify this value with your actual sql_handle
   

B. 按平均 CPU 时间获取有关前五个查询的信息

以下示例返回前五个查询的 SQL 语句文本和平均 CPU 时间。

SQL复制

 

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
    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 statement_text  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
ORDER BY total_worker_time/execution_count DESC;  

C. 提供批处理执行统计信息

以下示例返回按批执行的 SQL 查询的文本，并提供有关它们的统计信息。

SQL复制

 

SELECT s2.dbid,   
    s1.sql_handle,    
    (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,  
    execution_count,   
    plan_generation_num,   
    last_execution_time,     
    total_worker_time,   
    last_worker_time,   
    min_worker_time,   
    max_worker_time,  
    total_physical_reads,   
    last_physical_reads,   
    min_physical_reads,    
    max_physical_reads,    
    total_logical_writes,   
    last_logical_writes,   
    min_logical_writes,   
    max_logical_writes    
FROM sys.dm_exec_query_stats AS s1   
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2    
WHERE s2.objectid is null   
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;  

请参阅

动态管理视图和函数 (Transact-SQL)
与执行相关的动态管理视图和函数 (Transact-sql)
sys.dm_exec_query_stats (Transact-sql)
sys.dm_exec_requests (Transact-SQL)
sys.dm_exec_cursors (Transact-sql)
sys.dm_exec_xml_handles (Transact-sql)
sys.dm_exec_query_memory_grants (Transact-sql)
使用 APPLY
sys.dm_exec_text_query_plan (Transact-sql)

---

建议的内容

• sys.dm_exec_query_stats (Transact-sql) - SQL Server

  sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_db_index_usage_stats (Transact-sql) - SQL Server

  sys.dm_db_index_usage_stats (Transact-SQL)

• sys.dm_tran_active_transactions (Transact-sql) - SQL Server

  sys.dm_tran_active_transactions (Transact-SQL)

• sys.dm_os_waiting_tasks (Transact-sql) - SQL Server

  sys.dm_os_waiting_tasks (Transact-SQL)