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 ;
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
so.create_date DESC ;
Until we meet next time,