119 lines
4.9 KiB
SQL
119 lines
4.9 KiB
SQL
@@header
|
|
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display column differences between two tables
|
|
* Parameters : 1 - Source Object (In OWNER.OBJECT_NAME format)
|
|
* 2 - Target Object (In OWNER.OBJECT_NAME format)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-May-15 Vishal Gupta Converted INNER JOIN to FULL OUTER JOIN
|
|
* 21-Dec-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
set term on
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
|
|
UNDEFINE source
|
|
UNDEFINE target
|
|
|
|
DEFINE source="&&1"
|
|
DEFINE target="&&2"
|
|
|
|
UNDEFINE source_owner
|
|
UNDEFINE source_object
|
|
UNDEFINE target_owner
|
|
UNDEFINE target_object
|
|
|
|
set term off
|
|
COLUMN _source_owner NEW_VALUE source_owner NOPRINT
|
|
COLUMN _source_object NEW_VALUE source_object NOPRINT
|
|
COLUMN _target_owner NEW_VALUE target_owner NOPRINT
|
|
COLUMN _target_object NEW_VALUE target_object NOPRINT
|
|
|
|
SELECT SUBSTR(UPPER('&&source'), 1 , INSTR('&&source','.') - 1 ) "_source_owner"
|
|
, SUBSTR(UPPER('&&source'), INSTR('&&source','.') + 1 ) "_source_object"
|
|
, SUBSTR(UPPER('&&target'), 1 , INSTR('&&target','.') - 1 ) "_target_owner"
|
|
, SUBSTR(UPPER('&&target'), INSTR('&&target','.') + 1 ) "_target_object"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * COLUMNS DIFFERENCES
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Source = '&&source_owner..&&source_object'
|
|
PROMPT * - Target = '&&target_owner..&&target_object'
|
|
PROMPT *****************************************************************
|
|
|
|
|
|
COLUMN source_object HEADING "SourceObject" FORMAT a40
|
|
COLUMN Target_object HEADING "TargetObject" FORMAT a40
|
|
COLUMN Source_Type HEADING "Source DataType" FORMAT a20
|
|
COLUMN Target_Type HEADING "Target DataType" FORMAT a20
|
|
COLUMN source_nullable HEADING "Source|Null?" FORMAT a8
|
|
COLUMN target_nullable HEADING "Target|Null?" FORMAT a8
|
|
COLUMN source_column_id HEADING "Source|Id" FORMAT 99999
|
|
COLUMN target_column_id HEADING "Target|Id" FORMAT 99999
|
|
COLUMN source_column_name HEADING "Source Column Name" FORMAT A30
|
|
COLUMN target_column_name HEADING "Target Column Name" FORMAT A30
|
|
|
|
|
|
SELECT * FROM
|
|
(
|
|
SELECT --a.owner || '.' || a.table_name source_object
|
|
--, b.owner || '.' || b.table_name target_object
|
|
--,
|
|
a.column_id source_column_id
|
|
, a.column_name source_column_name
|
|
, DECODE(a.nullable,'N','NOT NULL','') source_nullable
|
|
, CASE
|
|
WHEN a.data_type = 'NUMBER'
|
|
THEN a.data_type || NVL2(a.data_precision,'(' || a.data_precision || ',' || a.data_scale || ')','')
|
|
WHEN a.data_type = 'FLOAT'
|
|
THEN a.data_type || NVL2(a.data_precision,'(' || a.data_precision || ')','')
|
|
WHEN a.data_type = 'VARCHAR2' OR a.data_type = 'CHAR'
|
|
THEN a.data_type || '(' || a.char_length || ' ' || DECODE(a.char_used,'B','BYTE','CHAR') || ')'
|
|
WHEN a.data_type = 'NVARCHAR2' OR a.data_type = 'NCHAR'
|
|
THEN a.data_type || '(' || a.char_length || ')'
|
|
WHEN a.data_type = 'RAW' OR a.data_type = 'UROWID'
|
|
THEN a.data_type || '(' || a.data_length || ')'
|
|
ELSE a.data_type
|
|
END source_Type
|
|
, b.column_id target_column_id
|
|
, b.column_name target_column_name
|
|
, DECODE(b.nullable,'N','NOT NULL','') target_nullable
|
|
, CASE
|
|
WHEN b.data_type = 'NUMBER'
|
|
THEN b.data_type || NVL2(b.data_precision,'(' || b.data_precision || ',' || b.data_scale || ')','')
|
|
WHEN b.data_type = 'FLOAT'
|
|
THEN b.data_type || NVL2(b.data_precision,'(' || b.data_precision || ')','')
|
|
WHEN b.data_type = 'VARCHAR2' OR b.data_type = 'CHAR'
|
|
THEN b.data_type || '(' || b.char_length || ' ' || DECODE(b.char_used,'B','BYTE','CHAR') || ')'
|
|
WHEN b.data_type = 'NVARCHAR2' OR b.data_type = 'NCHAR'
|
|
THEN b.data_type || '(' || b.char_length || ')'
|
|
WHEN b.data_type = 'RAW' OR b.data_type = 'UROWID'
|
|
THEN b.data_type || '(' || b.data_length || ')'
|
|
ELSE b.data_type
|
|
END target_type
|
|
FROM (SELECT * FROM dba_tab_columns WHERE owner= '&&source_owner' AND TABLE_NAME = '&&source_object') a
|
|
FULL OUTER JOIN (SELECT * FROM dba_tab_columns WHERE owner= '&&target_owner' AND TABLE_NAME = '&&target_object') B
|
|
ON a.column_name = b.column_name
|
|
)
|
|
WHERE NVL(source_Type,'x') <> NVL(target_type,'x')
|
|
OR NVL(source_nullable,'x') <> NVL(target_nullable,'x')
|
|
OR source_column_id <> target_column_id
|
|
ORDER BY NVL(source_column_id,target_column_id)
|
|
;
|
|
|
|
@@footer |