Diff the routine columns between two MS-SQL servers

Diff_Routine_Columns.sql

/*

SELECT 
	 TABLE_SCHEMA
	,TABLE_NAME
	,COLUMN_NAME	
	,ORDINAL_POSITION
	,COLUMN_DEFAULT
	,IS_NULLABLE
	,DATA_TYPE
	,CHARACTER_MAXIMUM_LENGTH
	,NUMERIC_PRECISION
	,NUMERIC_SCALE
	,DATETIME_PRECISION
	,CHARACTER_SET_NAME
	,COLLATION_NAME
FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS 
FOR JSON PATH; 

*/

DECLARE @db1JSON nvarchar(MAX) = N'[ ... ]';
DECLARE @db2JSON nvarchar(MAX) = N'[ ... ]';

SELECT *
INTO #rc1
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 #rc2
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'
);

SELECT
     COALESCE(d1.TABLE_NAME,  d2.TABLE_NAME)  AS ROUTINE_NAME
    ,COALESCE(d1.TABLE_SCHEMA,d2.TABLE_SCHEMA) AS ROUTINE_SCHEMA
    ,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
    ,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.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
    ,d1.COLUMN_DEFAULT           AS db1_DEFAULT

    -- DB2
    ,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.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
    ,d2.COLUMN_DEFAULT           AS db2_DEFAULT 
FROM #rc1 AS d1
FULL JOIN #rc2 AS d2
    ON  d1.TABLE_NAME  = d2.TABLE_NAME
    AND d1.TABLE_SCHEMA = d2.TABLE_SCHEMA
    AND d1.COLUMN_NAME = d2.COLUMN_NAME
WHERE (1=2) 
OR d1.COLUMN_NAME IS NULL
OR d2.COLUMN_NAME IS NULL
OR d1.DATA_TYPE                               <> d2.DATA_TYPE
OR d1.IS_NULLABLE                             <> d2.IS_NULLABLE
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)
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.COLUMN_DEFAULT,     '~~NULL~~')  <> ISNULL(d2.COLUMN_DEFAULT,     '~~NULL~~')
ORDER BY
     COALESCE(d1.TABLE_NAME,  d2.TABLE_NAME)
    ,COALESCE(d1.COLUMN_NAME, d2.COLUMN_NAME)
;

DROP TABLE IF EXISTS #rc1, #rc2;