0

I need to compare two databases and identify what columns have changed. Once I have identified a column that has changed in some way (size, type, etc) I need to capture (write to a table) the old column definition and the new column definition.

For instance, if using the INFORMATION_SCHEMA.COLUMNS table I discover that a column size has changed from 25 to 50 I will need to store the two column definition. In this case it may be 'char(25)' and 'char(50)'.

I have not problem using the INFORMATION_SCHEMA.COLUMNS table to identify when something has changed.

The issue I have is once I determine that the column has changed how do I build the column definition? In this case how to I build 'char(25)' and 'char(50)'?

Is there somewhere I can obtain this type of definition? If I have to build the definition piece by piece how do I determine all the components of the definition.

Any advice or suggestions are appreciated.

Thanks in advance.

user2135970
  • 795
  • 2
  • 9
  • 22
  • 1
    Do you realize there are a myriad of tools on the market that will do schema comparisons for you, and create synchronization scripts to update the target, to make it equal to the source? – Randy Minder Nov 27 '13 at 15:20

1 Answers1

1
select table_name, column_name,
column_definition = data_type + isnull('(' + convert(varchar, character_maximum_length) + ')', '') 
from information_schema.columns
kgu87
  • 2,050
  • 14
  • 12