Identifying Stored Procedures Created or Modified Within a Date Range in SQL Server

As part of rewriting the documentation for my custom API, I needed to test each endpoint to ensure it behaved as expected, including validating both input and output. During this process, I discovered that several existing stored procedures required modification. Unfortunately, I did not keep track of which procedures were changed. Once the documentation was complete, I had to go back and identify the stored procedures that had been altered.

This experience led me to think more seriously about how to track changes to stored procedures; something that would be valuable for auditing, deployment reviews, and debugging. Having visibility into when a stored procedure was created or last modified can help both developers and database administrators identify recent stored procedure changes.

To address this, I took a look at SQL Server's system catalog views. I decided on the sys.procedures view which contain metadata such as creation and modification dates for stored procedures. This brief write-up outlines how to use T-SQL to retrieve a list of stored procedures that were either created or modified within a specified date range, including the script I wrote to accomplish this task.

The TSQL Query

use [yourdatabase]
go

-- select SPs that were created or modified within a specific date range
select
  schema_name(schema_id) as schema_name, -- retrieve schema name (e.g., 'dbo') for the procedure
  name as procedure_name, -- the name of the stored procedure
  create_date, -- the date the procedure was originally created
  modify_date -- the date the procedure was last altered
from sys.procedures -- contains metadata for user-defined procedures
where create_date between '2025-06-01' and '2025-06-18' or modify_date between '2025-06-01' and '2025-06-18'
order by modify_date desc

What This Query Does
It retrieves a list of stored procedures from your selected SQL database that meet either of the following criteria:

• Created or altered between June 06 2025 and June 18 2025
• Each row in the result set includes:
• The schema name (such as dbo)
• The procedure name
• The creation date
• The last modification date

The results are sorted in descending order by modify_date (most recent appear at the top).

Additional Notes
Each database has their own "sys.procedures" and "sys.objects".

If you want to find other objects besides stored procedures you can use "sys.objects" instead of "sys.procedures". Example: Specify type = 'P' with sys.objects to limit the results to stored procedures.

Here is a query to find other object types such as triggers, user tables, etc.

select
distinct
type,
type_desc
from sys.objects

type type_desc
TT TYPE_TABLE
FN SQL_SCALAR_FUNCTION
UQ UNIQUE_CONSTRAINT
SQ SERVICE_QUEUE
F FOREIGN_KEY_CONSTRAINT
U USER_TABLE
D DEFAULT_CONSTRAINT
PK PRIMARY_KEY_CONSTRAINT
V VIEW
S SYSTEM_TABLE
IT INTERNAL_TABLE
P SQL_STORED_PROCEDURE
TR SQL_TRIGGER

Conclusion
SQL Server makes it easy to find out which stored procedures have been added or changed recently. Using the built-in system views, you can quickly get a list of procedures based on when they were created or last modified. This is helpful when trying to audit recent changes, review what went out in a deployment, or troubleshoot something that suddenly stopped working. Pretty good with keeping track of changes to help with things like documentation.