Need script to determine schema changes

  • Hi all - I have been a fan of all you SQL guru's out there for sometime now and I would like to enlist your assistance.  Does anyone have a script that could identify if objects are added, delete or changed?  What I am in need of is some sort of T-SQL that could help me with this.

    Thanks a bunch!

    Marti

  • Are you talking about versions of stored procs/tables??

    Cause this is something that is really better handled by carefully planed releases and code versionning kept in history. The only way to do this with the system tables is to take a snapshot of the table, then every x minutes take another snap shot and take note of all the differences (can't put a trigger on system tables).

  • I agree - but there must be some way to monitor via sysobjects or somewhere - afterall - if embacedo can do it.... just was curious.

    Thanks for the input!

    -Marti

  • That was my reference to the snapshot (copy sysobjects into a table, then at next run check to see differences between the 2 tables). Both you'd have to log, sysobjects, syscomments, syscolumns and sysindex*. Quite a bit of work.

  • take a look at this article:

    http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf

    It's the foundation for the software components called DB Ghost (http://www.dbghost.com) a total database change management solution which promotes the idea of using source control to makes changes, rapid extraction and deployment of those changes or reporting on the differences - giving you total control on what, who, why, when and how changes were made to your database systems.

     

     

    regards,

    Mark Baekdal

    http://www.dbghost.com

    http://www.innovartis.co.uk

    +44 (0)208 241 1762

    Build, Comparison and Synchronization from Source Control = Database change management for SQL Server

     

     

     

  • SysObjects table has CRDate column and that should be starting point to check if any object was created in last 24 hrs.  Hope this helps.

     

    Thanks

    PP


    paul

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply