Find last modified date for SQL Server database objects

Change control is for the database objects just as much as it is for the application layer. You'd like to think that everybody has a change control process in place, but that is not always the case. There are several third party tools, such as Apex, Red Gate and Quest Software, or there is even the SQL Server Audit tool , which happens to be one of my favorites. The Audit, however, is a much larger deployment, and requires quite a bit more due diligence to put together. When I have a chance, I will put a nice overview on the Audit for your review. Until then, this is just a quick script that you can use to see when an object was changed.   

        /* find last modified date per object */

        SELECT
        name [ObjectName],
        CASE WHEN type = 'U' THEN 'Table'
           WHEN type = 'P' THEN 'Procedure'
           WHEN type IN('AF','FN','FS','FT','IF','TF') THEN 'Function'
           WHEN type = 'V' THEN 'View'
           WHEN type = 'C' THEN 'CheckConstraint'
           WHEN type = 'D' THEN 'DefaultConstraint'
           WHEN type = 'PK' THEN 'PrimaryKeyConstraint'
           WHEN type = 'F' THEN 'ForeignKeyConstraint'
           WHEN type = 'UQ' THEN 'UniqueConstraint'
           WHEN type = 'SN' THEN 'Synonym'
           WHEN type = 'TR' THEN 'Trigger' END [ObjectType],

        modify_date [Modified]

        FROM
        sys.objects
        WHERE 
        is_ms_shipped = 0  -- exclude system objects
        ORDER BY
       [Modified] DESC

I ran it against AdventureWorks2012, this is just a snapshot of the results: