How to Compare Table Structure in Oracle (Basic 1 - 30)

Comparing table structure in Oracle can be useful for a variety of tasks, such as identifying differences between tables, verifying that a table has been migrated correctly, or auditing a table's structure over time.

There are a few different ways to compare table structure in Oracle. One way is to use the WITH clause, which allows you to create temporary tables that can be used in your query. Another way to compare table structure is to use the MINUS operator, which returns the rows that are in the first table but not in the second table.

Using the WITH clause

The following query uses the WITH clause to compare the table structure of the EMP_TEST and DEPT_TEST tables:

SQL

WITH
T1 AS (
  SELECT USER_TAB_COLUMNS.COLUMN_NAME, USER_TAB_COLUMNS.DATA_TYPE
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = 'EMP_TEST'
),
T2 AS (
  SELECT USER_TAB_COLUMNS.COLUMN_NAME, USER_TAB_COLUMNS.DATA_TYPE
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = 'DEPT_TEST'
)
SELECT
  T1.COLUMN_NAME AS EMP,
  T1.DATA_TYPE AS EMP_TYPE,
  T2.COLUMN_NAME AS DEPT,
  T2.DATA_TYPE AS DEPT_TYPE
FROM T1
FULL JOIN T2
ON (T1.COLUMN_NAME = T2.COLUMN_NAME);

This query will return a table that shows all of the columns in both tables, as well as the data type for each column. If a column exists in one table but not the other, it will be displayed as null in the corresponding column of the output table.

Using the MINUS operator

The following query uses the MINUS operator to compare the table structure of the EMP_TEST and DEPT_TEST tables:

SQL

SELECT
  'TABLE' AS TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE
FROM
(
  SELECT COLUMN_NAME, DATA_TYPE
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = 'EMP_TEST'
  MINUS
  SELECT COLUMN_NAME, DATA_TYPE
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = 'DEPT_TEST'
)
UNION ALL
SELECT
  'DEPT' AS TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE
FROM
(
  SELECT COLUMN_NAME, DATA_TYPE
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = 'DEPT_TEST'
  MINUS
  SELECT COLUMN_NAME, DATA_TYPE
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = 'EMP_TEST'
);

This query will return a table that shows all of the columns that exist in one table but not the other. The output table will also include a column that indicates which table the column exists in.

Conclusion

Comparing table structure in Oracle can be a useful tool for a variety of tasks. There are a few different ways to compare table structure, and the best method to use will depend on your specific needs.