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.

TSQL change in SQL 2008 can break legacy code

As of the 2008 version, SQL Server no longer supports the old antiquatedd syntax for outer joins. It now requires the use of the newer JOIN keyword syntax. While it is a good idea to get rid of this flawed syntax, there is a considerable amount of legacy code that uses it. Before upgrading to SQL 2008 you should search your TSQL code for examples of the old outer join operators ( *=, =*) to see if this is going to affect your application.

Here is an example of the old syntax using the AdventureWorks sample database:

SELECT SalesTerritory.Name, SalesPerson.SalesPersonID
FROM Sales.SalesTerritory, SALES.SalesPerson
WHERE Sales.SalesPerson.TerritoryID *= Sales.SalesTerritory.TerritoryID

This syntax will generate an error in SQL Server 2008. To get around this you need to change the code to the accepted ANSI syntax or else set the database into compatibility level 80. Here is an example of the supported syntax

SELECT SalesTerritory.Name, SalesPerson.SalesPersonID
FROM Sales.SalesTerritory
LEFT JOIN Sales.SalesPerson on Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID

So far Microsoft has only de-supported the outer join form of this syntax. The inner join form will still work. However, it should not be used in new code. I suspect it will be de-supported in a future release as it should be. Specifying join conditions in the WHERE clause makes it far too easy to leave a join condition out of your WHERE clause, especially in queries that join many tables.