如何在SQL Server中获取近一个月用户执行的所有T-SQL语句？

Alright, let's break down how you can retrieve the T-SQL statements users executed over the past month in SQL Server. The default system tables don't store this historical data long-term, so you'll need to leverage specific auditing or monitoring tools—some of which require prior setup. Here are your actionable options:

Extended Events is SQL Server's modern, lightweight replacement for Profiler (introduced in 2012). If you already have an Extended Events session capturing SQL statements, you can query its target files directly:

• First, check if any relevant sessions exist:
  SELECT name, state_desc FROM sys.server_event_sessions;
• Look for sessions that capture events like sql_statement_completed or batch_completed. If you find one, use a query like this to extract the last month's data:

  SELECT 
      event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
      event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_time,
      event_data.value('(event/action[@name="username"]/value)[1]', 'varchar(100)') AS username,
      event_data.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)') AS sql_statement
  FROM 
      (SELECT CAST(event_data AS XML) AS event_data 
       FROM sys.fn_xe_file_target_read_file('YourSessionFileName*.xel', NULL, NULL, NULL)) AS x
  WHERE 
      event_data.value('(event/@timestamp)[1]', 'datetime2') >= DATEADD(MONTH, -1, GETDATE())
  ORDER BY 
      event_time DESC;
  

• If you didn't set up an Extended Events session beforehand, you can't retroactively get past data—but you should set one up immediately to track future statements.

Query Store (SQL Server 2016+) is designed to track query performance and execution history, and it's often enabled by default. Here's how to use it:

• First, verify if Query Store is turned on for your database:
  SELECT name, is_query_store_on FROM sys.databases;
• If it's enabled, run this query to pull the last month's statements:

  SELECT 
      q.last_execution_time,
      qt.query_sql_text,
      s.login_name,
      s.host_name
  FROM 
      sys.query_store_query q
  JOIN 
      sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
  JOIN 
      sys.query_store_plan qp ON q.query_id = qp.query_id
  JOIN 
      sys.query_store_runtime_stats qrs ON qp.plan_id = qrs.plan_id
  LEFT JOIN 
      sys.dm_exec_sessions s ON qrs.session_id = s.session_id
  WHERE 
      q.last_execution_time >= DATEADD(MONTH, -1, GETDATE())
  ORDER BY 
      q.last_execution_time DESC;
  

• Note: Query Store has a retention policy (default is 30 days). If someone modified it to keep data for less time, you might not get the full month's history.

Profiler is the old-school tool for tracking queries, but it's not recommended for long-term production use due to performance overhead. That said, if you ran a Profiler trace and saved the .trc file, you can query it:

• Either open the trace file in Profiler and filter for StartTime in the last month (look for events like SQL:BatchCompleted or RPC:Completed), or use T-SQL:

  SELECT 
      StartTime,
      LoginName,
      TextData AS sql_statement
  FROM 
      fn_trace_gettable('C:\Path\To\Your\TraceFile.trc', DEFAULT)
  WHERE 
      StartTime >= DATEADD(MONTH, -1, GETDATE())
      AND EventClass IN (12, 10) -- 12 = SQL:BatchCompleted, 10 = RPC:Completed
  ORDER BY 
      StartTime DESC;
  

• Warning: Don't leave Profiler running continuously on production—it can slow down your server.

If you set up server-level or database-level auditing, you can pull the audit logs to see executed statements:

• Use this query to retrieve audit data from the last month:

  SELECT 
      event_time,
      session_server_principal_name AS username,
      statement AS sql_statement
  FROM 
      sys.fn_get_audit_file('\\YourAuditShare\Audit_*.sqlaudit', DEFAULT, DEFAULT)
  WHERE 
      event_time >= DATEADD(MONTH, -1, GETDATE())
      AND action_id IN ('SL', 'ST') -- Actions corresponding to SQL statement execution
  ORDER BY 
      event_time DESC;
  

• Like the other methods, auditing needs to have been configured before the statements were executed to capture historical data.

Critical Note #

If none of these features were enabled before the past month, you can't retrieve the historical T-SQL statements. SQL Server doesn't store all user-executed queries in default system tables long-term—you have to set up monitoring/auditing in advance. For future tracking, stick with Extended Events or Query Store for minimal performance impact.

内容的提问来源于stack exchange，提问作者Sanjeeb Singh