How to get all Stored Procedure modified/created in last N days

I recently come through a situation where I forgot the name of the procedure modified by me few days back. After a search from Google I found many solutions and decided to put them on one place .

For modified stored procedures :

SELECT name,create_date,
modify_date
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 10

This will give stored procedures modified between last 10 days.

Similarly

SELECT name,create_date,
modify_date
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 10

will give stored procedures created between last 10 days.

Similarly to get most recently modified stored procedures :

SELECT name,create_date,modify_date
from sys.procedures
order by modify_date desc

To get stored procedures created and / or modified on a particular date :

SELECT name,create_date,modify_date
FROM sys.procedures
WHERE modify_date = '20141218'

Note: modify_date or create_date is in the format YYYYMMDD
All the queries above are executed against SQL SERVER 2008.


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.