Query

How to Compare Two Tables Definition

Compare Two Tables’ Column Names

Whenever you concern the differences between two tables’ definition, you can use queries to compare the columns of two tables.

For example, a table named EMPLOYEES_2 which is very similar with the original table EMPLOYEES. And we’d like to know the differences in columns. We can compare both tables like this:

SQL> conn hr/hr
Connected.
SQL> set pagesize 100;
SQL> select nvl(source.column_name, 'N/A') source, nvl(target.column_name, 'N/A') target from (select * from user_tab_columns where table_name = 'EMPLOYEES') source full outer join (select * from user_tab_columns where table_name = 'EMPLOYEES_2') target on source.column_name = target.column_name order by source.column_id;

SOURCE                         TARGET
------------------------------ ------------------------------
EMPLOYEE_ID                    EMPLOYEE_ID
FIRST_NAME                     FIRST_NAME
LAST_NAME                      LAST_NAME
EMAIL                          EMAIL
PHONE_NUMBER                   PHONE_NUMBER
HIRE_DATE                      HIRE_DATE
JOB_ID                         JOB_ID
SALARY                         SALARY
COMMISSION_PCT                 COMMISSION_PCT
MANAGER_ID                     MANAGER_ID
DEPARTMENT_ID                  DEPARTMENT_ID
N/A                            GENDER

12 rows selected.

In the above query, both tables belong to the same owner HR, so we can leverage USER_TAB_COLS, a dictionary view about column specifications to compare both tables.

From the perspective of names, two tables have little difference in columns. Let’s go deeper to the definition comparison.

Compare Two Tables’ Column Definitions

A more advanced query that can show every details about the definitions of tables. Moreover, it highlights the columns which have differences in specifications.

In the following example, we assume both tables come from different owners, so we use ALL_TAB_COLS to retrieve the information we need.

SQL> column no format 99;
SQL> column source format a30;
SQL> column target format a30;
SQL> column difference format a4;
SQL> select rownum no, source, target, case source when target then ' ' else '*' end difference from (select nvl2(source.column_name, source.column_name || ', ' || source.data_type || nvl2(source.data_precision || source.data_length, '(' || nvl(source.data_precision, source.data_length) || nvl2(source.data_scale, ',' || source.data_scale, '') || ')', ''), 'N/A') source, nvl2(target.column_name, target.column_name || ', ' || target.data_type || nvl2(target.data_precision || target.data_length, '(' || nvl(target.data_precision, target.data_length) || nvl2(target.data_scale, ',' || target.data_scale, '') || ')', ''), 'N/A') target from (select * from all_tab_columns where owner = 'HR' and table_name = 'EMPLOYEES') source full outer join (select * from all_tab_columns where owner = 'OE' and table_name = 'EMPLOYEES_2') target on source.column_name = target.column_name order by source.column_id);

The query result will look like this:

 NO SOURCE                         TARGET                         DIFF
--- ------------------------------ ------------------------------ ----
  1 EMPLOYEE_ID, NUMBER(6,0)       EMPLOYEE_ID, NUMBER(6,0)
  2 FIRST_NAME, VARCHAR2(20)       FIRST_NAME, VARCHAR2(20)
  3 LAST_NAME, VARCHAR2(25)        LAST_NAME, VARCHAR2(25)
  4 EMAIL, VARCHAR2(25)            EMAIL, VARCHAR2(100)           *
  5 PHONE_NUMBER, VARCHAR2(20)     PHONE_NUMBER, VARCHAR2(20)
  6 HIRE_DATE, DATE(7)             HIRE_DATE, DATE(7)
  7 JOB_ID, VARCHAR2(10)           JOB_ID, VARCHAR2(10)
  8 SALARY, NUMBER(8,2)            SALARY, NUMBER(8,2)
  9 COMMISSION_PCT, NUMBER(2,2)    COMMISSION_PCT, NUMBER(2,2)
 10 MANAGER_ID, NUMBER(6,0)        MANAGER_ID, NUMBER(6,0)
 11 DEPARTMENT_ID, NUMBER(4,0)     DEPARTMENT_ID, NUMBER(4,0)
 12 N/A                            GENDER, CHAR(1)                *

12 rows selected.

We can easily focus on the lines marked with * in DIFF column. As we can see, line 4 and 12 are different in some ways.

The comparison methods introduced in this post can be very useful if you were trying to figure out why your complex queries threw ORA-00913 or ORA-00947.

Leave a Reply

Your email address will not be published. Required fields are marked *