How to get it procedure version history [Resolved]

Posted by NADH123 under Sql Server on 8/8/2013 | Points: 10 | Views : 1096 | Status : [Member] | Replies : 5
how to get it proc last modified which machine s

Mahendra
91-9908699686



Responses

Posted by: Bandi on: 8/8/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

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

Posted by: Nadh123 on: 8/8/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 8/8/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
-- 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

Posted by: Nadh123 on: 8/8/2013 [Member] Starter | Points: 25

Up
0
Down
FROM fn_trace_gettable this object is not there


Mahendra
91-9908699686

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

Posted by: Bandi on: 8/8/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Login to post response