#0280 – SQL Server – Script to identify modified objects


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.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s