List storedprocedures or database objects by last modified date

I learnt something new today. I needed to make a release which includes lots of changes to DB over 3 months period and needed a quick way to figure out which staging db objects to move across to the production.

I wished many times to list storedprocedures by last modified date but stupid enough never Google it and just did the painful way of noting down and scripting them. Then I face the problem of all those script files keep piling up over the period of time and lost track of which scripts have been executed or not.

Today I’m in a bit of sensible mode and did a quick googling and found this useful script, so I’m posting here for later reference.

Listing last modified storedprocedures
select name, create_date, modify_date
from sys.procedures
order by modify_date desc

Listing last modified tables
select name, create_date, modify_date
from sys.tables
order by modify_date desc

Listing db objects by type and date
SELECT *
FROM sys.objects
WHERE type in ('P', 'U')
AND DATEDIFF(D,modify_date, GETDATE()) < 90

Type “P” is for storedprocedures and “U” for tables. More on available Types: http://msdn.microsoft.com/en-us/library/ms190324.aspx

Now that I know which objects have been last updated, I can do the DB script very easily by just selecting those objects. Sweet isn’t it?