如何在 SQL Server 2008 中找到存储过程的最后修改日期和修改用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4394888/
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
How can i find the last modified date ,modified user of an stored procedure in SQL Server 2008
提问by kbvishnu
I need to find the user name of the person who modified a particular stored procedure.
我需要找到修改特定存储过程的人的用户名。
How do I find out when a stored procedure was last modified or compiled in Oracle?
如何确定存储过程最后一次在 Oracle 中修改或编译的时间？
gives me idea about the time. But how do I know the user who modified it?
让我了解时间。但是我怎么知道修改它的用户呢？
采纳答案by kbvishnu
This can be achieve by running any of the following query.
这可以通过运行以下任何查询来实现。
SELECT
[procedure] = QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
+ '.' + QUOTENAME(OBJECT_NAME([object_id])),
last_execution_time,
avg_execution_time = CONVERT(DECIMAL(30,2), total_worker_time * 1.0 / execution_count),
max_worker_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
ORDER BY avg_execution_time DESC;
------------OR--------------------------------------
- - - - - - 或者 - - - - - - - - - - - - - - - - - - - ——
SELECT
COALESCE(DB_NAME(t.[dbid]),'Unknown') AS [DB Name],
ecp.objtype AS [Object Type],
t.[text] AS [Adhoc Batch or Object Call],
SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
,qs.[last_execution_time] AS [Last Exec Time]
,qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
JOIN sys.dm_exec_cached_plans ecp
ON qs.plan_handle = ecp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
where
ecp.objtype = 'Proc'
order by [Last Exec Time] desc
回答by Habesha
Here what it works for me:-
这对我有用：-
DECLARE @filename VARCHAR(255)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT gt.HostName,
gt.ApplicationName,
gt.NTUserName,
gt.NTDomainName,
gt.LoginName,
gt.SPID,
gt.EventClass,
te.Name AS EventName,
gt.EventSubClass,
gt.TEXTData,
gt.StartTime,
gt.EndTime,
gt.ObjectName,
gt.DatabaseName,
gt.FileName,
gt.IsSystem
FROM [fn_trace_gettable](@filename, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass in (164) --AND gt.EventSubClass = 2
ORDER BY StartTime DESC;
回答by Jaykay
Can you try this out?
你能试试这个吗？
SELECT name, create_date, modify_date FROM sys.procedures
从 sys.procedures 选择名称、创建日期、修改日期
回答by Remus Rusanu
Procedure changes are traced in the system default trace. Simply open the .trcfile from your ...\MSSQL\LOGfolder and search for the ALTER PROCEDURE. The only problem is that the default trace gets rewriten in time, so you can only use it for recent changes (days-weeks).
在系统默认跟踪中跟踪过程更改。只需.trc从您的...\MSSQL\LOG文件夹中打开文件并搜索 ALTER PROCEDURE。唯一的问题是默认跟踪会被及时重写，因此您只能将其用于最近的更改（天-周）。
回答by Umar T.
Try Schema Changes HistoryReport.
尝试架构更改历史报告。
In SQl Server Management Sudio -> Right Click on server name or schema name -> Reports -> Standard Reports -> Schema Changes History
在 SQl Server Management Sudio -> 右键单击服务器名称或架构名称 -> Reports -> Standard Reports -> Schema Changes History
This worked for me like a charm.
这对我来说就像一种魅力。
Taken from here
取自这里
回答by Vaishali Chauhan
I tried the following query and it worked for me:
我尝试了以下查询，它对我有用：
SELECT *
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date DESC
This query is for stored procedures.
此查询用于存储过程。
回答by Damien_The_Unbeliever
If you are going to need this information in the future, it may be worth looking at implementing a DDL triggeron the CREATE_PROCEDURE and ALTER_PROCEDURE DDL events
如果您将来需要此信息，可能值得考虑在CREATE_PROCEDURE 和 ALTER_PROCEDURE DDL 事件上实现DDL 触发器
Example B from the EVENTDATApage shows a trigger logging all DDL events, with user name captured.
EVENTDATA页面中的示例 B显示了记录所有 DDL 事件的触发器，并捕获了用户名。
