CODEWITHSUNDEEP
sqlsql-server
kbvishnu
kbvishnu

Reputation: 15630

How can I find the last modified date, modified user of an stored procedure in 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?

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

Upvotes: 18

Views: 99139

Answers (8)

Umar T.
Umar T.

Reputation: 421

Try Schema Changes History Report.

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

This worked for me like a charm.

Taken from here

Upvotes: 5

Vaishali Chauhan
Vaishali Chauhan

Reputation: 11

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.

Upvotes: -1

Jaykay
Jaykay

Reputation: 666

Can you try this out?

SELECT name, create_date, modify_date FROM sys.procedures

Upvotes: 12

Habesha
Habesha

Reputation: 485

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

Upvotes: 27

kbvishnu
kbvishnu

Reputation: 15630

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

Upvotes: -11

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

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.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294287

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

Upvotes: 8

Kamran Khan
Kamran Khan

Reputation: 9986

It seems you cant.

Upvotes: 0

Related Questions