How to track table schema changes

  • Hi,

    I have a development database and some developer are use this one. I need to track the new tables and modified existing tables such as adding new columns, indexes or modified existing one. May i know any way to track these changes. I am not bother about data changes.

    Thanks...

  • Hi,

    Try this may help you find which objects were changed since yesterday

    select o.name, o.create_date, o.modify_date, o.[type], o.type_desc

    from sys.objects o

    where o.is_ms_shipped !=1 and datediff(d,o.modify_date,getdate()) <= 1

    select c.name,o.name,o.create_date,o.modify_date,o.[type],o.type_desc

    from sys.columns c

    join sys.objects o on c.object_id = o.object_id and datediff(d,o.modify_date,getdate()) <= 1 and o.is_ms_shipped !=1

    select i.name,o.name,o.create_date,o.modify_date,o.[type],o.type_desc

    from sys.indexes i

    join sys.objects o on i.object_id = o.object_id and datediff(d,o.modify_date,getdate()) <= 1 and o.is_ms_shipped !=1

    If you want to track the very exact changes like "varchar(10) changed to nvarchar(10)" then you'll have to

    make your work around.

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • Try using a database trigger

    CREATE trigger mytrigger on database for create_table,alter_table,drop_table

    AS

    ...

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 3 posts - 1 through 2 (of 2 total)

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