How can i find the last modified date ,modified us

Question

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?

gives me idea about the time. But how do I know the user who modified it?

Answer 1

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).

Answer 2

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

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

Answer 3

Try Schema Changes History Report.

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

This worked for me like a charm.

Taken from here

Answer 4

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

Answer 5

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

Answer 6

Can you try this out?

SELECT name, create_date, modify_date FROM sys.procedures