如何在SQL Server 2008中找到上次修改日期，修改后的存储过程用户

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?

让我了解时间。但是我怎么知道修改它的用户呢？

8 个解决方案

#1

Can you try this out?

你能试一试吗？

SELECT name, create_date, modify_date FROM sys.procedures

SELECT name，create_date，modify_date FROM sys.procedures

#2

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;

Source:- https://serverfault.com/questions/258111/finding-out-who-has-modified-a-stored-procedure-on-sql-server

资料来源： - https://serverfault.com/questions/258111/finding-out-who-has-modified-a-stored-procedure-on-sql-server

#3

Procedure changes are traced in the system default trace. Simply open the .trc file from your ...\MSSQL\LOG folder 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).

在系统默认跟踪中跟踪过程更改。只需打开... \ MSSQL \ LOG文件夹中的.trc文件，然后搜索ALTER PROCEDURE。唯一的问题是默认跟踪会及时重写，因此您只能将其用于最近的更改（天 - 周）。

#4

It seems you cant.

好像你不能。

#5

Try Schema Changes History Report.

尝试架构更改历史记录报告。

In SQl Server Management Sudio -> Right Click on server name or schema name -> Reports -> Standard Reports -> Schema Changes History

在SQl服务器管理Sudio中 - >右键单击服务器名称或模式名称 - >报告 - >标准报告 - >模式更改历史记录

This worked for me like a charm.

这对我来说就像一个魅力。

Taken from here

从这里开始

#6

If you are going to need this information in the future, it may be worth looking at implementing a DDL trigger on the CREATE_PROCEDURE and ALTER_PROCEDURE DDL events

如果将来需要此信息，可能需要在CREATE_PROCEDURE和ALTER_PROCEDURE DDL事件上实现DDL触发器。

Example B from the EVENTDATA page shows a trigger logging all DDL events, with user name captured.

EVENTDATA页面中的示例B显示了记录所有DDL事件的触发器，并捕获了用户名。

#7

-1

You can look at the default trace and figure this out easily.

您可以查看默认跟踪并轻松搞清楚。

This is to show as an example and you need to look at the Object:Altered of EVENT NAME column.

这是作为示例显示的，您需要查看对象：更改EVENT NAME列。

Execute the below SQL script,

执行以下SQL脚本，

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;

#8

-8

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

#1