How to get it procedure version history [Resolved]

-- To get the list of procedures that are modified for past 30 days

SELECT name, create_date, Modify_date

FROM sys.objects

WHERE type = 'P'

AND DATEDIFF(D,modify_date, GETDATE()) < 30    -- last 30 days changes

ORDER BY modify_date DESC

-- To get the user name and all you must enable default trace. Follow the below link
http://stackoverflow.com/questions/4394888/how-can-i-find-the-last-modified-date-modified-user-of-an-stored-procedure-in-s http://msdn.microsoft.com/en-us/library/ms175513.aspx

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

this query is only last modified but my reqiurement is all modifcations

Mahendra
91-9908699686

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

-- Check the below query to get the host name, login name who altered the procedure

SELECT NTUserName, NTDomainName, HostName, LoginName, ServerName, ObjectName, SessionLoginName, StartTime ModifiedDateTime

FROM fn_trace_gettable

('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_239 .trc', default)  -- make sure to provide correct location where logs stored..

WHERE ObjectName LIKE '%spClassNotationMining1%'  --- Procedure name

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

FROM fn_trace_gettable this object is not there

Mahendra
91-9908699686

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

Which SQL Server version you are using..? Do you have access to MSSQL\LOG.... folder?
For that, you should enabled default trace earlier. It is enabled by default..

Check whether default trace is enabled or not?

SELECT* FROM sys.configurations WHERE configuration_id = 1568

If it is not enabled, how do we enable it? We can run this script in order to enable the default trace:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE; 

GO

sp_configure 'default trace enabled', 1;

GO

RECONFIGURE;

GO

-- To get the default trace path

SELECT path, start_time, last_event_time FROM sys.traces

Check the below link for the same
https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/
http://www.sqlservercentral.com/Forums/Topic1072850-1550-1.aspx

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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