How to track table schema changes

• purushottam2

  SSCrazy

  Points: 2021

  More actions

  April 29, 2013 at 12:38 am

  #274652

  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...

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  April 29, 2013 at 1:50 am

  #1610693

  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

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  April 29, 2013 at 1:59 am

  #1610694

  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