How to write a SQL query to get all modified dates of a stored procedure

Question

I want to get all modified dates (n modified dates) of a stored procedure.

Though I am able to get last modified date of a stored procedure like this:

select name, create_date, modify_date 
from sys.procedures 
where name = 'SPF_OLS_GET_CUSTOMER_SUMMARY' 
order by modify_date desc;

but I want to get all modified dates. Is it possible? If yes, then how to do that?

Thanks in advance

No, SQL Server doesn't track history like that. One last modified date is all you get. — Jeroen Mostert, Oct 03 '19 at 10:33
Possible duplicate of [How to check date of last change in stored procedure or function in SQL server](https://stackoverflow.com/questions/5579223/how-to-check-date-of-last-change-in-stored-procedure-or-function-in-sql-server) — Amira Bedhiafi, Oct 03 '19 at 10:34
As Jeroen wrote in his comment, SQL Server doesn't track such history changes. If you need such information, you can write a DDL trigger that will catch the modification of the procedure and record it into a change log table. — Zohar Peled, Oct 03 '19 at 10:45
SQL Server does not keep these changes. That is not what it is designed to do. As such we always keep the SQL statements in a source code repository (TFS, Subversion, git, etc). SQL changes are installed from the repository so a history can be kept. — JazzmanJim, Oct 03 '19 at 13:59

score 0 · Answer 1 · edited Oct 03 '19 at 13:16

0

You can try below query:

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type_desc = 'SQL_STORED_PROCEDURE'

edited Oct 03 '19 at 13:16

Prashant Pimpale

answered Oct 03 '19 at 11:30

Shivani

This would only be the last modified date, not a history of each date the procedure was modified. – Tom Oct 03 '19 at 11:43
Please check modified answer, where I get both dates – Shivani Oct 03 '19 at 12:06
@Shivani I want each date that of Stored procedure modified as Tom said – Hussain Md Oct 03 '19 at 12:14
@Shivani. Your code is getting the latest change dates for each stored procedure on a database. This is not what the OP is asking. He's looking for all change dates for a particular stored procedure. For this he'll need to use some type of source code repository outside of SQL Server. – JazzmanJim Oct 03 '19 at 14:04

1 Answers1