How to check last modified changes in a procedure [Resolved]

Posted by Leelailla under Sql Server on 3/25/2016 | Points: 10 | Views : 311 | Status : [Member] | Replies : 2
How to check last modified changes in a procedure along with date and person name and also the place where actually the code got changed.




Responses

Posted by: Professionaluser on: 3/25/2016 [Member] [MVP] Bronze | Points: 50

Up
0
Down

Resolved
Below query gives you the trace data of a database ( it lists out the all changes done to the database)...
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;


Leelailla, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Professionaluser on: 3/25/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
If your organization/team is maintaining code at centralized area such as TFS (Team Foundation Server), SVN(Subversion), there you can analyse the changes who, when, or what changes have been done at the database objects ( or even any single item).

Within SQL Server, we can get to know who has done changes to particular database object and when they done, but could NOT compare it with previous code.

Below query gives you when SP got changed recently,
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
and name = 'SPName'


refer the below links,
http://serverfault.com/questions/258111/finding-out-who-has-modified-a-stored-procedure-on-sql-server

Leelailla, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response