A Tool for Herding Cats

Managing developers is often like herding cats. Many development teams lack formal change control procedures and in many teams everyone has a sysadmin password. Sometimes in smaller companies there is not an adequate test environment and developers acquire the habit of making changes in production. Often they do not communicate those changes to the team lead or the other members of the team.

As we all know, this is a terribly dangerous practice, but we have all done it. Sometimes we have no choice.

if you are responsible for managing a situation like that and the bean-counters are not likely to approve the cost of a real change control platform, here is a script that will make your life a little easier. It will quickly show you the important things that have changed in a database since a point in time that you specify.



declare @cutoffdate datetime
, @days smallint;
set @days = -7; -- change this as necessary
set @cutoffdate = DATEADD(dd, @days,getdate());
select db_name() as DBName
, name
, type_desc
, case when create_date > @cutoffdate then 'New' else 'Changed' end as [Status]
, create_date
, modify_date
from sys.objects
where (modify_date > @cutoffdate or create_date > @cutoffdate)
and is_ms_shipped = 0 -- exclude system objects
and is_published = 0 -- exclude repl tables that will always show change
order by DB_NAME(), type_desc, name;

This is a handy tool for a quick look but it has a lot of limitations. It's redeeming feature is that it is simple and puts no load on the database.

It doesn't show you what change was made or who made it. It doesn't allow you to roll back changes. It doesn't include all the types of database objects (no indexes included for example). Worst of all, it doesn't tell you what has been dropped or deleted.

However, it does alert you to newly created objects and changes for object types that are most relevant to developers: stored procedures, triggers, tables, etc. This will at least allow you to ask around to find out why the change was made and who made it. Most importantly, if the wheels suddenly come off of your website, you can quickly see what, if anything, was changed in the database at the same time the problem started.