September 24, 2011 at 6:47 pm
Comments posted to this topic are about the item Schema comparison
October 10, 2011 at 1:46 am
Left joins , extra join and checks can be ommitted in order to make it more simple. Here is the version i m referring to
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 AS difference_found
FROM
DB1.INFORMATION_SCHEMA.COLUMNS src_col -- source database here
INNER JOIN DB2.INFORMATION_SCHEMA.COLUMNS targ_col --target database here
ON src_col.TABLE_NAME = targ_col.TABLE_NAME
AND src_col.COLUMN_NAME = targ_col.COLUMN_NAME
AND src_col.TABLE_SCHEMA = targ_col.TABLE_SCHEMA
)
SELECT *
FROM column_compare
WHERE difference_found NOT IN ('None found')
Aqeel Ahmed
October 10, 2011 at 11:11 am
Thanks for the comments! I like your simplified version, but be aware this code returns different results. Without the left joins, it will compare only the columns that exist in both databases; it will not find columns missing in the target database. You could remove
WHEN targ_col.COLUMN_NAME IS NULL THEN 'Target column not found'
from your CASE statement, since that condition will never be hit. Still, your version is useful and probably easier to understand when finding missing columns is not a requirement.
May 12, 2016 at 7:09 am
Thanks for the script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply