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.