Last Modified / Created When - DB Objects

I run into this issue very often when the code is getting moved from development to staging to production environments. Most of us work in a multiple development resource environment and this is an issue because the changes gets made and sometimes it is easy for resources to forget to tell what got changed and when. This is where SQL 2005's sys.objects rocks.

1) The following query will tell you exactly which stored procedure or user table got modified say in the last 3 days.

SELECT name
FROM sys.objects
WHERE type in ('P', 'U')
AND DATEDIFF(D,modify_date, GETDATE()) < 3 -- 3 is a changeable value.

2)The following query will tell you exactly which stored procedure or user table got created say in the last 3 days.

SELECT name
FROM sys.objects
WHERE type in ('P', 'U')
AND DATEDIFF(D,create_date, GETDATE()) < 3 -- 3 is a changeable value.

I have a customer who has parameterized the value 3 in terms of hours because they have development resources in 3 different countries and they want to Police the coding standards.

Source - SQL Authorty, Pinal Dave