How to detect changes to the structure of databases, tables and even SP

HI,

Any help here is appreciated, I work in a large software company that has many small teams. I am faced with the issue of some of the other teams are changing the structure of tables and views and even SPs and functions without letting the rest know of these changes.

My question is, is there a way of tracking these changes through a job to alert everyone else in case this situation happens.

regards

You could write some TSQL code that will monitor or snapshot system table information, compare it to the database and find the differences. You can also go the 3rd party route and look at tools like Redgate's SQLCompare to do this for you. Your best bet however is to use your source code control system to track the changes and maintain them (I am assuming you already have such system in place). Apart from these, there are no built-in methods that are easy enough to detect differences out of the box.|||

That is what i thought, but I was hopping for an easy solution. Currently we are tracking it with source control although not all developers adhere to the rules.

thanks anyways for your quick response.

emad

|||

Yeah, RedGate has a nice snapshot feature that you can use to keep a version around of a server. I use SQLCompare when I am migrating changes just to make sure nothing has changed that hasn't been done quite right.

In 2005, you might also consider using a DDL trigger to capture changes (I do this to make sure no one makes any unkown changes. You could then check this table for who has made changes (of course if the changer has dbo rights they could disable this trigger before making changes, but if you have malicious programmers (rather than those who ar just too lazy to do things right :) then you have way more problems than any of us can help you with :)

|||I understand that the DML trigger can be used to find a one or many changes that happen to data in a user table purposely or otherwise. I did that using deleted, inserted, and updated tables. I do not know how to get the user name or id that changed it. Where can I get this information from?. Also, is it possible to get the column names in these three tables? Please advise. Thanks.|||DML trigger is for tracking data changes. You can use SYSTEM_USER to track login information for example. But the relevance of this information depends on your application architecture and how the interaction with the database happens from the end-user. DDL triggers (new in SQL Server 2005) on the other hand can be used to track schema changes for example and that is more closer to what you want.sql