[SQL]直接比對2個資料庫，共有的同名TABLE，有那些同名欄位型態長度不同

 

只要有DBlink 可以直接比對2個資料庫，共有的同名TABLE

 

有那些同名欄位型態長度不同

 

SELECT 

T1.TABLE_NAME AS TABLE_NAME

    ,T1.COLUMN_NAME AS COLUMN_NAME,

    T1.DATA_TYPE AS TABLE1_DATA_TYPE,

    T1.CHARACTER_MAXIMUM_LENGTH AS TABLE1_MAX_LENGTH,

    T2.DATA_TYPE AS TABLE2_DATA_TYPE,

    T2.CHARACTER_MAXIMUM_LENGTH AS TABLE2_MAX_LENGTH

                  , T1.NUMERIC_PRECISION as T1_NUMERIC_PRECISION

        ,     T2.NUMERIC_PRECISION as T2_NUMERIC_PRECISION

FROM

    FI2LH.INFORMATION_SCHEMA.COLUMNS T1

JOIN

    [XXXXXX].FI2LH.INFORMATION_SCHEMA.COLUMNS T2

ON

    T1.COLUMN_NAME = T2.COLUMN_NAME

WHERE

    T1.TABLE_NAME = T2.TABLE_NAME

    AND (T1.DATA_TYPE <> T2.DATA_TYPE

         OR COALESCE(T1.CHARACTER_MAXIMUM_LENGTH, -1) <> COALESCE(T2.CHARACTER_MAXIMUM_LENGTH, -1)

                or T1.NUMERIC_PRECISION <> T2.NUMERIC_PRECISION

                )             order by T1.TABLE_NAME , T1.COLUMN_NAME