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

Hot Topic

Fuzzy Group Updates?
100% Width and Height within a master page
Getting XML web page in SQL 2000
about multiplication in sql
NULL values in Cube for measures
Need Bulk Insert help
Subscriptions and Windows NT Auth?
SQL Mail
Filter a Model Table?
Accessing Mobile SQL Databases in VB2005 - Windows Apps

SQL Server

I hate VS 2005 SQL pane
Line numbers in Report Builder
The RPC server is unavailable.
Default Values not Selected on Report Manager
Cannot find the driver for JDBC
Previous(Expression, AggFunction, PreviousScope, AggScope) ??
Oracle 9i -> SQL Server 2005: Initialization of table failes
again a problem with xml datasource extension
Error of Microsoft OLE DB Provider for SQL Server
Users Migration from Navision Native DB to SQL 2000

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

Answer this question

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

  • wabs27

    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.

  • Karthik R

    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.

  • Hafiz78664

    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.

  • Michael O. Schoneman

    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

  • tfinniga

    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 :)



  • How to detect changes to the structure of databases, tables and even SP
Answer this question