When working on quality assurance, staging and production environments, it is very important to have control over the changes made to the database definition – objects and design. Today, I present a simple script to help you get a list of objects in your database with their creation and modification dates.

The script below (which will also be available on the Scripts module of the website shortly) can be customized to suit your requirements by modifying the WHERE clause to filter on the object types, creation and modification dates as required.

USE AdventureWorks2008R2 ;

GO

SELECT  so.name AS ObjectName,

        OBJECT_NAME(so.parent_object_id) AS ParentObjectName,

        SCHEMA_NAME(so.schema_id) AS SchemaName,

        so.type_desc AS ObjectType,

        so.create_date AS ObjectCreationDateTime,

        so.modify_date AS ObjectModificationDateTime,

        so.is_published AS IsPublished

FROM    sys.objects AS so

WHERE   so.is_ms_shipped = 0

ORDER BY CASE WHEN so.parent_object_id = 0 THEN so.object_id

              ELSE so.parent_object_id

         END,

        so.schema_id,

        so.type DESC,

        so.modify_date DESC,

        so.create_date DESC ;

GO

Until we meet next time,

Be courteous. Drive responsibly.