SQL TSQL：获取上次运行的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3579092/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL: Get Last Queries Ran
提问by SQLMenace
Is there a way to get the SQL text for the last few queries?
有没有办法获取最后几个查询的 SQL 文本？
I am using Microsoft SQL Server 2005
我使用的是 Microsoft SQL Server 2005
回答by SQLMenace
Yes, take a look, this will give you the 50 most recent executed SQL statements
是的，看看，这会给你最近执行的50条SQL语句
sql 2005 and up only
仅 sql 2005 及更高版本
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 %'
--and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
ORDER BY last_execution_time DESC
回答by Dustin Laine
If using SQL Server 2005+:
如果使用 SQL Server 2005+：
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
Great tip from SQLAuthority!
回答by Rob
The only way I'm aware of is to have the SQL Server Profilerrunning. Unfortunately this needs to be started prior to the queries being executed, so if you're hoping to catch something that's happened on an "ad hoc" basis, it won't be suitable. If you're trying to track what a piece of code's doing and want to capture the queries it executes, it should work a treat.
我知道的唯一方法是运行SQL Server Profiler。不幸的是，这需要在执行查询之前启动，因此如果您希望捕捉在“临时”基础上发生的事情，那么它就不合适了。如果您试图跟踪一段代码在做什么并想要捕获它执行的查询，那么它应该是一种享受。
回答by David Vaughan
If you need to inspect parameter values, this addition returns the <ParameterList>XML
如果您需要检查参数值，此添加将返回<ParameterList>XML
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,
SUBSTRING(
s3.query_plan,CHARINDEX('<ParameterList>',s3.query_plan),
CHARINDEX('</ParameterList>',s3.query_plan) + LEN('</ParameterList>') - CHARINDEX('<ParameterList>',s3.query_plan)
) AS Parameters,
last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
CROSS APPLY sys.dm_exec_text_query_plan(s1.plan_handle, s1.statement_start_offset,
s1.statement_end_offset) AS s3
) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
ORDER BY last_execution_time DESC
