How to: Compare two database structures

A few days ago, I was asked for a task that can seem pretty simple in most cases.  Comparing 2 instances of “the same” database.  When your DB holds a few tables, this can be done manually, using some simple tools, using SQL commands like DESC or DEFINE as needed.  You can even use the Oracle Data Modeler an read the Data Dictionary from the 2 databases and compare them through it.
The problem comes when you so many tables that making an effective comparison would be a highly time-consuming effort.
For the effects of this post, I will state that what I need to compare is table names, column names and data types.  That was the challenge.  Of course, a procedure would pop-up to solve it.
Prerequisites for this procedure are:

1. 1 schema must be local and be the current schema from which he procedure is called
2. 1 schema must be reachable through a database link called “templink”

1: CREATE PROCEDURE COMPARE_DB_STRUCTURE IS
2:  ---------------
3:  -- VARIABLES --
4:  ---------------
5:   v_localtable user_tables.table_name%TYPE;
6:   v_localcol  user_tab_cols.column_name%TYPE;
7:   v_localtype  user_tab_cols.data_type%TYPE;
8:   v_localtabnum NUMBER;
9:   v_localcolnum NUMBER;
10:   
11:   v_remotetable user_tables.table_name%TYPE;
12:   v_remotecol  user_tab_cols.column_name%TYPE;
13:   v_remotetype user_tab_cols.data_type%TYPE;
14:   v_remotetabnum NUMBER;
15:   v_remotecolnum NUMBER;
16:  
17:  -------------
18:  -- CURSORS -- 
19:  -------------
20:   CURSOR c_localtable IS
21:   SELECT table_name
22:   FROM user_tables
23:   WHERE table_name NOT IN (
24:    SELECT table_name
25:    FROM user_tab_cols
26:    WHERE column_name = 'STATIC'
27:   )
28:   ORDER BY table_name;
29:  
30:   CURSOR c_remotetable IS
31:   SELECT table_name
32:   FROM user_tables@templink
33:   WHERE table_name NOT IN (
34:    SELECT table_name
35:    FROM user_tab_cols@templink
36:    WHERE column_name = 'STATIC'
37:   )
38:   ORDER BY table_name;
39: 
40:   CURSOR c_localcols IS
41:   SELECT column_name, data_type
42:   FROM user_tab_cols
43:   WHERE table_name = v_localtable
44:   ORDER BY column_name;
45:   
46:   CURSOR c_remotecols IS
47:   SELECT column_name, data_type
48:   FROM user_tab_cols@templink
49:   WHERE table_name = v_remotetable
50:   ORDER BY column_name;
51:  
52:  -------------------------
53:  -- MAIN PROCEDURE BODY --
54:  -------------------------
55:  BEGIN
56:  
57:      dbms_output.enable(1000000);
58:         dbms_output.put_line('Starting procedure: COMPARE DATABASE STRUCTURE...');
59:         dbms_output.put_line('');
60:   -- OPEN TABLE CURSORS
61:   
62:   SELECT COUNT(table_name) INTO v_localtabnum
63:   FROM user_tables
64:   WHERE table_name NOT IN (
65:    SELECT table_name
66:    FROM user_tab_cols
67:    WHERE column_name = 'STATIC'
68:   );
69:   
70:   SELECT COUNT(table_name) INTO v_remotetabnum
71:   FROM user_tables@templink
72:   WHERE table_name NOT IN (
73:    SELECT table_name
74:    FROM user_tab_cols@templink
75:    WHERE column_name = 'STATIC'
76:   );
77: 
78:         dbms_output.put_line('Number of static tables');
79:         dbms_output.put_line('-----------------------');
80:         dbms_output.put_line('');
81:      dbms_output.put_line('Local tables:  '|| v_localtabnum);
82:          dbms_output.put_line('Remote tables: '|| v_remotetabnum);
83:         dbms_output.put_line('');
84:   
85:   OPEN c_localtable;
86:   OPEN c_remotetable;
87:   -- GET TABLES
88:   FETCH c_localtable INTO v_localtable;
89:   FETCH c_remotetable INTO v_remotetable;
90:   
91:   WHILE c_localtable%FOUND LOOP
92:         IF v_localtable = v_remotetable THEN
93:           -- OPEN COLUMN CURSORS
94:           OPEN c_localcols;
95:           OPEN c_remotecols;
96:           
97:           WHILE c_localcols%FOUND LOOP
98:             
99:             IF v_localcol != v_remotecol THEN
100:               dbms_output.put_line('Found problem in table '|| v_localtable ||' - Inconsistency in column names.');
101:             ELSE
102:               IF v_localtype != v_remotetype THEN
103:                 dbms_output.put_line('Found problem in table '|| v_localtable ||' Column '|| v_localcol ||' data types are inconsistent');
104:               END IF; -- DATA TYPE
105:             END IF; -- COLUMN NAME
106:           END LOOP; -- COLUMN LOOP
107:           -- dbms_output.put_line('Finished local table '||v_localtable);
108:           -- dbms_output.put_line('Finished remote table '||v_remotetable);
109:           -- CLOSE COLUMN CURSORS
110:           CLOSE c_localcols;
111:           CLOSE c_remotecols;
112:           -- NEXT TABLE
113:           dbms_output.put_line('Table '|| v_localtable ||' checked');
114:           FETCH c_localtable INTO v_localtable;
115:           FETCH c_remotetable INTO v_remotetable;
116:         ELSE
117:           IF v_localtable > v_remotetable THEN
118:             dbms_output.put_line('Local table '|| v_localtable);
119:             dbms_output.put_line('Table '|| v_remotetable ||' not found in local DB');
120:             FETCH c_remotetable INTO v_remotetable;
121:           ELSE
122:             dbms_output.put_line('Remote table '|| v_remotetable);
123:             dbms_output.put_line('Table '|| v_localtable ||' not found in remote DB');
124:             FETCH c_localtable INTO v_localtable;
125:           END IF; -- TABLE COMPARE
126:         END IF;
127:  END LOOP; -- TABLES LOOP
128:   
129:  -- CLOSE TABLE CURSORS
130:  CLOSE c_localtable;
131:  CLOSE c_remotetable;
132:   
133:         dbms_output.put_line('');
134:         dbms_output.put_line('Process finished');
135:         dbms_output.put_line('----------------');
136:         dbms_output.put_line('');
137: END COMPARE_DB_STRUCTURE;

So, this procedure will alert of any difference found at table, column or data type level through the messages shown through the dbms_output.


This procedure can be easily edited and customized to work on 2 local DBs or even in 2 remote DBs.


Any comments, enhancement proposals and such are welcome.


Hope this can help any of you out there. Enjoy.