Log table Schema changes

问题:

Is there any way to log the changes made in Schema of a Table whenever I do the schema changes?

I was reading an article here about DDL Triggers. But it does not tell about the specific changes made in schema of a table.

回答1:

this would be very difficult as quite often in SSMS the table is actually dropped and rebuilt in the background (depending on the complexity of the schema change & whether or not you enabled the "Prevent saving changes that require the table to be re-created " option in SSMS) - logging all the different types of changes would be a nightmare. (constraints being dropped, only to be re-created - bulk re-inserts, renames etc when all you might have done is re-arranged columns in joined table)

If you're serious about tracking schema changes i'd strongly recommend you script the schema (using the generate scripts option in MSSMS) & check the resulting file into SVN / SourceSafe / TFS & use the many comparison tools available for those systems.

OR, you can use 3rd party products that do all this for you, such as Red Gates SQL Source Control:

http://www.red-gate.com/products/sql-development/sql-source-control/

Edit: You may find this useful - it makes use of the Service Broker (SQL 2005+) and SSB queues:

http://www.mssqltips.com/sqlservertip/2121/event-notifications-in-sql-server-for-tracking-changes/

回答2:

For this issue i would probably use Event Notifications. Although DDL trigger's in my opinion do tell about specific changes made to table, just trigger definition:

Create Trigger tr_DDLNotikums
    On DataBase
For **DDL_DATABASE_LEVEL_EVENTS** 

回答3:

Use DDL Trigger In Below Format

 CREATE TRIGGER tr_DDL_Database ON DATABASE 
 FOR DDL_SCHEMA_EVENTS
 AS Begin
   Insert Into LogTable (XmlColumn)
   SELECT EVENTDATA()
 End