| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
|
A script that shows differences in table structures [Oracle] | ||
|
This script asks for two table names. Enter them, then it will tell you how the tables differ in their table structure (not in their content!).
comp_table_structure.sql
set verify off set heading off column data_type format a20 accept table1 prompt 'Table 1: ' accept table2 prompt 'Table 2: ' prompt prompt The table &table1 and &table2 have columns with the same name but differ: prompt ------------------------------------------------------------------------- column data_precision fold_after select a.column_name, a.data_type, a.data_length, a.data_scale,a.data_precision, b.column_name, b.data_type, b.data_length, b.data_scale,b.data_precision from user_tab_columns a, user_tab_columns b where a.table_name = upper('&table1') and b.table_name = upper('&table2') and a.column_name = b.column_name and ( a.data_type <> b.data_type or a.data_length <> b.data_length or a.data_scale <> b.data_scale or a.data_precision <> b.data_precision ); prompt columns in &table1 but not in &table2 prompt ------------------------------------- select column_name from user_tab_columns where table_name = upper('&table1') minus select column_name --, data_type, data_length, data_scale, data_precision from user_tab_columns where table_name = upper('&table2'); prompt columns in &table2 but not in &table1 prompt ------------------------------------- select column_name --, data_type, data_length, data_scale, data_precision from user_tab_columns where table_name = upper('&table2') minus select column_name from user_tab_columns where table_name = upper('&table1'); A little demonstrationcreate table table_one ( col_1 varchar2(10), col_2 number(5,2), col_3 number, col_4 date ); create table table_two as select * from table_one; alter table table_one add col_added integer; alter table table_one drop column col_3; alter table table_two modify col_2 number(9,8); SQL> @comp_table_structure Table 1: table_one Table 2: table_two The table table_one and table_two have columns with the same name but differ: ------------------------------------------------------------------------ COL_2 NUMBER 22 2 5 COL_2 NUMBER 22 8 9 columns in table_one but not in table_two ------------------------------------ COL_ADDED columns in table_two but not in table_one ------------------------------------ COL_3 Links
This script is also used to
compare v$sql and v$sqlarea.
|