CODEWITHSUNDEEP
sql-server
Sanjeev Agrawal
Sanjeev Agrawal

Reputation: 21

How to get the definition of Store Procedure which was executed on any particular date?

How to get the definition of Store Procedure which was executed on any particular date? I want to see/get the definition(Text) of Store Procedure in SQL Server 2012 which was run few days back. Actually I am able to see the definition of Store Prodecure which was last modified by any user. But I need the previous version/ definition of Store Procedure.

Upvotes: 2

Views: 58

Answers (2)

Mathusuthanan
Mathusuthanan

Reputation: 117

There is no possibility to get the definition of sp which was executed on the particular date but you can able to get the last modified time of the particular sp by using the below schema.

select name, create_date, modify_date
from sys.procedures
where name = 'Your sp name'

Upvotes: 1

Ruchi
Ruchi

Reputation: 1236

Get the backup of the Database that consists of the previous version of the procedure you want, restore it alongside the existing DB and then script out the procedure.

Using the same database, without backup, you cant restore it to previous version.

Also, if you have source control, you can get the previous checked in scripts by other users.

Besides above means, you cannot get the previous version of script on same database.

select name,create_date,modify_date from sys.objects where type='p' and name like 'Your SP Name'

Upvotes: 0

Related Questions