Compare the schema of a table between two servers (For example dev and Prod)

-- Compare the schema of a table between two servers (For example dev and Prod)

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 server2.DBName.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 server2.DBName.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.
)
and table_name = 'TableName'