Compare Two Tables Structures


SELECT
    A.COLUMN_NAME,
    A.DATA_TYPE,
    A.DATA_LENGTH,
    B.COLUMN_NAME,
    B.DATA_TYPE,
    B.DATA_LENGTH
FROM
    (SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME  = 'TABLE1') A,
    (SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME  = 'TABLE2') B
WHERE
    A.COLUMN_NAME = B.COLUMN_NAME (+)
UNION
SELECT
    A.COLUMN_NAME,
    A.DATA_TYPE,
    A.DATA_LENGTH,
    B.COLUMN_NAME,
    B.DATA_TYPE,
    B.DATA_LENGTH
FROM
    (SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME  = 'TABLE1') A,
    (SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME  = 'TABLE2') B
WHERE
    A.COLUMN_NAME (+) = B.COLUMN_NAME.





Related Links
How to Compare & Sync Structures of Two Tables in Oracle

5 comments: