List All the Stored Procedure Modified in Last Few Days – Interview Question of the Week #070

Question: How do I list stored procedures modified in the last seven days? Query their catalog timestamps in the target database, using an explicit time window and schema names.

I like this question because I have used the query outside interviews. During a security incident at a consulting client, recently modified procedures helped us find suspicious code. A timestamp was a starting point for investigation, not a record of who made the change.

-- Original calendar-boundary test, retained for historical comparison:
SELECT name,modify_date,create_date FROM sys.objects
WHERE type='P' AND DATEDIFF(day,modify_date,GETDATE())<7;

-- Rolling seven-day window, with one fixed current-time value:
DECLARE @Now datetime=GETDATE();
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name,modify_date,create_date
FROM sys.procedures
WHERE is_ms_shipped=0
 AND modify_date>=DATEADD(day,-7,@Now) AND modify_date<=@Now
ORDER BY modify_date DESC,SchemaName,name;

-- Matching timestamps, not proof of an unchanged lifetime:
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name,modify_date,create_date
FROM sys.procedures
WHERE is_ms_shipped=0 AND modify_date=create_date
ORDER BY SchemaName,name;

Choose what “last seven days” means

DATEDIFF(day,…) counts day boundaries. It is not a precise elapsed-hours test. The revised predicate uses a rolling seven-day window ending at one captured GETDATE value. SQL Server’s catalog timestamps are local server times, so compare them consistently.

sys.procedures includes SQL and CLR procedures; the original type=’P’ query selected T-SQL procedures only. Metadata visibility follows your permissions. An empty result means no visible matching rows, not proof that no procedure changed.

What if creation and modification dates are equal?

The final query preserves the original follow-up exercise, but describe its result accurately: the timestamps are equal. That alone does not prove a procedure has never been modified during its entire lifetime. Objects can be dropped and recreated, and a last-modified field is not a change history.

For the incident scenario, compare the actual definition with a trusted deployment copy and examine retained audit evidence. modify_date does not identify an actor, explain intent or store earlier procedure text.

DATEDIFF documents the boundary calculation. Do you have a diagnostic script you use often? Share it in the comments or on Twitter.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.