How To Find When A Stored Procedure Was Last Modified

Photo by Jon Tyson on Unsplash

Have you ever encountered a situation where you want to check if someone (or maybe you) modified a stored procedure or not?

Or maybe you want to see a list of all the most recently modified stored procedures. How do you do this?

Well, you can have this data without any instrumentation or any SQL Server tool. This information is stored in the SQL database itself.

Here are some SQL Queries that you might find useful

Stored Procedures Sorted By Last Modified

USE database_name
SELECT name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date DESC

Top 10 Most Recently Modified Stored Procedures

USE database_name
SELECT TOP 10 name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date DESC

Last Modified Date Of A Particular Stored Procedure

USE database_name
SELECT name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P'
AND name = "proc_name"

Understanding The SQL Queries

So these are the queries that can answer most of our questions, but what exactly are we doing here?

First things first, we all know that stored procedures are local to databases. Therefore it becomes extremely important to specify the database which owns our stored procedure.

We then make use of the sys.objects DMV. I recommend you go through the official docs for sys.objects as that would allow you to be more creative and customize the queries according to your needs.

In the queries above, we just fetch the creation date, name and last modified date from it.

We then filter our results for only stored procedure by mentioning the type ‘P’. And in the last example, we are also narrowing it down to the one particular stored procedure in case we are interested in only that!

Bonus

Another interesting thing about the queries above is that we can actually use these to list all our stored procedures.