Schema comparison

smartin-756027, 2011-09-26 (first published: 2011-09-14)

I support some databases which are occasionally upgraded by the vendor through a series of obfuscated stored procedures. The datatype changes and such are sometimes undocumented and difficult to suss out, so I wrote this script to compare table structures in one database vs another (say, test vs prod).

It's come in handy for me, maybe it will for you as well. It uses a CTE, so it will only work in SQL2005 and above. Just change the target database name in the JOIN clause and you're good to go. You can also include a linked server name to do the comparison against a remote server. Alter the WHERE clause at the end to suit your needs.

There are a couple things I may improve at some point. If an entire table is missing in your target, the script reports each and every column as "not found", rather than the whole table. The differences reported are also coded in a CASE statement, so it's not smart enough to tell you if there are two differences in one column. It's good enough for me at the time, though!

Cheers...

WITH column_compare AS ( SELECT src_col.TABLE_NAME table_name, src_col.COLUMN_NAME column_name, src_col.ORDINAL_POSITION src_position, src_col.DATA_TYPE src_dtype, src_col.CHARACTER_MAXIMUM_LENGTH src_len, src_col.NUMERIC_PRECISION src_prec, src_col.NUMERIC_SCALE src_scale, targ_col.ORDINAL_POSITION targ_position, targ_col.DATA_TYPE targ_dtype, targ_col.CHARACTER_MAXIMUM_LENGTH targ_len, targ_col.NUMERIC_PRECISION targ_prec, targ_col.NUMERIC_SCALE targ_scale, CASE WHEN targ_col.COLUMN_NAME IS NULL THEN 'Target column not found' WHEN src_col.DATA_TYPE <> targ_col.DATA_TYPE THEN 'Data type' WHEN src_col.CHARACTER_MAXIMUM_LENGTH <> targ_col.CHARACTER_MAXIMUM_LENGTH THEN 'Length' WHEN src_col.NUMERIC_PRECISION <> targ_col.NUMERIC_PRECISION THEN 'Precision' WHEN src_col.NUMERIC_SCALE <> targ_col.NUMERIC_SCALE THEN 'Scale' WHEN src_col.DATETIME_PRECISION <> targ_col.DATETIME_PRECISION THEN 'Datetime precision' WHEN src_col.ORDINAL_POSITION <> targ_col.ORDINAL_POSITION THEN 'Ordinal position' ELSE 'None found' END difference_found FROM INFORMATION_SCHEMA.COLUMNS src_col LEFT JOIN db_2.INFORMATION_SCHEMA.COLUMNS targ_col -- change database as needed ON src_col.TABLE_NAME = targ_col.TABLE_NAME AND src_col.COLUMN_NAME = targ_col.COLUMN_NAME LEFT JOIN db_2.INFORMATION_SCHEMA.TABLES trg_tab -- change database as needed ON src_col.TABLE_SCHEMA = trg_tab.TABLE_SCHEMA AND src_col.TABLE_NAME = trg_tab.TABLE_NAME ) SELECT * FROM column_compare --modify the WHERE clause to suit needs WHERE difference_found NOT IN ( 'None found' --, 'Target column not found' --etc. )

Schema comparison

Rate

Share

Share

Rate