How to diff columns between two MS-SQL server DBs

Diff_Columns.sql

-- SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS 
-- WHERE TABLE_NAME LIKE 'T_Checklist%' 
-- GROUP BY TABLE_SCHEMA, TABLE_NAME 

-- SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
-- WHERE (1=1) 
-- AND TABLE_NAME LIKE 'T_Checklist%' 
-- -- AND TABLE_NAME <> 'T_Checklist_Ref_EditRights' 
-- FOR JSON AUTO 


-- Put your two JSON strings here
DECLARE @db1JSON nvarchar(MAX) = N'[ ... ]';
DECLARE @db2JSON nvarchar(MAX) = N'[ ... ]';

-- Parse both into temp tables
SELECT *
INTO #db1
FROM OPENJSON(@db1JSON)
WITH 
(
     TABLE_SCHEMA             nvarchar(128)  '$.TABLE_SCHEMA'
    ,TABLE_NAME               nvarchar(128)  '$.TABLE_NAME'
    ,COLUMN_NAME              nvarchar(128)  '$.COLUMN_NAME'
    ,ORDINAL_POSITION         int            '$.ORDINAL_POSITION'
    ,COLUMN_DEFAULT           nvarchar(MAX)  '$.COLUMN_DEFAULT'
    ,IS_NULLABLE              nvarchar(3)    '$.IS_NULLABLE'
    ,DATA_TYPE                nvarchar(128)  '$.DATA_TYPE'
     
	,CHARACTER_MAXIMUM_LENGTH int            '$.CHARACTER_MAXIMUM_LENGTH' 
	,NUMERIC_PRECISION        int            '$.NUMERIC_PRECISION' 
    ,NUMERIC_SCALE            int            '$.NUMERIC_SCALE' 
    ,DATETIME_PRECISION       int            '$.DATETIME_PRECISION' 
    ,CHARACTER_SET_NAME       nvarchar(128)  '$.CHARACTER_SET_NAME' 
    ,COLLATION_NAME           nvarchar(128)  '$.COLLATION_NAME' 
);

SELECT *
INTO #db2
FROM OPENJSON(@db2JSON)
WITH 
(
     TABLE_SCHEMA             nvarchar(128)  '$.TABLE_SCHEMA'
    ,TABLE_NAME               nvarchar(128)  '$.TABLE_NAME'
    ,COLUMN_NAME              nvarchar(128)  '$.COLUMN_NAME'
    ,ORDINAL_POSITION         int            '$.ORDINAL_POSITION'
    ,COLUMN_DEFAULT           nvarchar(MAX)  '$.COLUMN_DEFAULT'
    ,IS_NULLABLE              nvarchar(3)    '$.IS_NULLABLE'
    ,DATA_TYPE                nvarchar(128)  '$.DATA_TYPE'
     
	,CHARACTER_MAXIMUM_LENGTH int            '$.CHARACTER_MAXIMUM_LENGTH' 
	,NUMERIC_PRECISION        int            '$.NUMERIC_PRECISION' 
    ,NUMERIC_SCALE            int            '$.NUMERIC_SCALE' 
    ,DATETIME_PRECISION       int            '$.DATETIME_PRECISION' 
    ,CHARACTER_SET_NAME       nvarchar(128)  '$.CHARACTER_SET_NAME' 
    ,COLLATION_NAME           nvarchar(128)  '$.COLLATION_NAME' 
);

-- Compare
SELECT
	 COALESCE(d1.TABLE_NAME,  d2.TABLE_NAME)  AS TABLE_NAME
    ,COALESCE(d1.COLUMN_NAME, d2.COLUMN_NAME) AS COLUMN_NAME
    ,CASE
        WHEN d1.COLUMN_NAME IS NULL THEN '➕ Only in DB2'
        WHEN d2.COLUMN_NAME IS NULL THEN '➖ Only in DB1'
        ELSE                              '⚠️ Different'
     END AS diff_type

    -- DB1 values
    ,d1.ORDINAL_POSITION         AS db1_ORDINAL_POSITION
    ,d1.IS_NULLABLE              AS db1_IS_NULLABLE
    ,d1.DATA_TYPE                AS db1_DATA_TYPE
    ,d1.CHARACTER_MAXIMUM_LENGTH AS db1_MAX_LENGTH
    ,d1.COLUMN_DEFAULT           AS db1_DEFAULT
	 
	,d1.NUMERIC_PRECISION        AS db1_NUMERIC_PRECISION
    ,d1.NUMERIC_SCALE            AS db1_NUMERIC_SCALE
    ,d1.DATETIME_PRECISION       AS db1_DATETIME_PRECISION
    ,d1.CHARACTER_SET_NAME       AS db1_CHARACTER_SET_NAME
    ,d1.COLLATION_NAME           AS db1_COLLATION_NAME

    -- DB2 values
    ,d2.ORDINAL_POSITION         AS db2_ORDINAL_POSITION
    ,d2.IS_NULLABLE              AS db2_IS_NULLABLE
    ,d2.DATA_TYPE                AS db2_DATA_TYPE
    ,d2.CHARACTER_MAXIMUM_LENGTH AS db2_MAX_LENGTH
    ,d2.COLUMN_DEFAULT           AS db2_DEFAULT

	,d2.NUMERIC_PRECISION        AS db2_NUMERIC_PRECISION
    ,d2.NUMERIC_SCALE            AS db2_NUMERIC_SCALE
    ,d2.DATETIME_PRECISION       AS db2_DATETIME_PRECISION
    ,d2.CHARACTER_SET_NAME       AS db2_CHARACTER_SET_NAME
    ,d2.COLLATION_NAME           AS db2_COLLATION_NAME
FROM #db1 AS d1  
FULL JOIN #db2 AS d2 
    ON  d1.TABLE_NAME = d2.TABLE_NAME 
    AND d1.COLUMN_NAME = d2.COLUMN_NAME 
WHERE (1=2) 
-- Only show rows that actually differ 

OR d1.COLUMN_NAME IS NULL  -- missing from DB1 
OR d2.COLUMN_NAME IS NULL  -- missing from DB2 

OR d1.DATA_TYPE <> d2.DATA_TYPE 
OR d1.IS_NULLABLE <> d2.IS_NULLABLE 

OR ISNULL(d1.COLUMN_DEFAULT,     '~~NULL~~') <> ISNULL(d2.COLUMN_DEFAULT,     '~~NULL~~') 
OR ISNULL(d1.CHARACTER_SET_NAME, '~~NULL~~') <> ISNULL(d2.CHARACTER_SET_NAME, '~~NULL~~') 
OR ISNULL(d1.COLLATION_NAME,     '~~NULL~~') <> ISNULL(d2.COLLATION_NAME,     '~~NULL~~') 

OR ISNULL(d1.CHARACTER_MAXIMUM_LENGTH, -999) <> ISNULL(d2.CHARACTER_MAXIMUM_LENGTH, -999) 
OR ISNULL(d1.NUMERIC_PRECISION,        -999) <> ISNULL(d2.NUMERIC_PRECISION,        -999) 
OR ISNULL(d1.NUMERIC_SCALE,            -999) <> ISNULL(d2.NUMERIC_SCALE,            -999) 
OR ISNULL(d1.DATETIME_PRECISION,       -999) <> ISNULL(d2.DATETIME_PRECISION,       -999) 

ORDER BY 
     COALESCE(d1.TABLE_NAME,  d2.TABLE_NAME) 
    ,COALESCE(d1.COLUMN_NAME, d2.COLUMN_NAME) 
    ,diff_type 
; 

DROP TABLE IF EXISTS #db1, #db2;

-- Drop #db1 if it exists
-- DROP TABLE IF EXISTS #db1;

-- Drop #db2 if it exists
-- DROP TABLE IF EXISTS #db2;