Quick compare of two tables

T

This script can be used for quick compare of two similar tables (like audit/historic or DML error tables) so you can easily check if columns and their data types matches.

WITH z AS (
    SELECT
        'UPLOADERS'             AS first_name,		-- base table
        'UPLOADERS' || '_U$'    AS second_name		-- audit/hist/error table
    FROM DUAL
),
c AS (
    SELECT
        c.table_name            AS table_name,
        c.column_name           AS column_name,
        c.column_id             AS column_id,
        --
        CASE
            WHEN c.data_type = 'NUMBER' THEN
                c.data_type ||
                CASE WHEN c.data_precision IS NOT NULL THEN '(' || c.data_precision || DECODE(c.data_scale, 0, '', ', ' || c.data_scale) || ')' END
            WHEN c.data_type IN ('CHAR', 'VARCHAR', 'VARCHAR2') THEN
                c.data_type || '(' ||
                DECODE(c.char_used, 'C', c.char_length || ' CHAR', c.data_length) || ')'
            ELSE c.data_type
            END AS data_type
    FROM user_tab_cols c
    WHERE c.table_name IN (
        SELECT z.first_name     FROM z UNION ALL
        SELECT z.second_name    FROM z
    )
),
a AS (
    SELECT
        a.column_id             AS first_pos,
        a.table_name            AS first_table,
        a.column_name           AS first_column,
        a.data_type             AS first_data_type,
        --
        b.column_id             AS second_pos,
        b.table_name            AS second_table,
        b.column_name           AS second_column,
        b.data_type             AS second_data_type
    FROM c a
    JOIN z
        ON z.first_name         = a.table_name
    LEFT JOIN c b
        ON b.table_name         = z.second_name
        AND b.column_name       = a.column_name
),
b AS (
    SELECT
        a.column_id             AS first_pos,
        a.table_name            AS first_table,
        a.column_name           AS first_column,
        a.data_type             AS first_data_type,
        --
        b.column_id             AS second_pos,
        b.table_name            AS second_table,
        b.column_name           AS second_column,
        b.data_type             AS second_data_type
    FROM c b
    JOIN z
        ON z.second_name        = b.table_name
    LEFT JOIN c a
        ON a.table_name         = z.first_name
        AND a.column_name       = b.column_name
    WHERE a.table_name          IS NULL
)
--
SELECT first_table, first_column, second_column, first_data_type, second_data_type
FROM (
    SELECT * FROM a UNION ALL
    SELECT * FROM b
)
ORDER BY first_pos NULLS LAST, second_pos;