sql/ctc.sql at master · JAC2703/sql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
REM *************************************************************************
REM AUTHOR:  Jeff Moss
REM NAME:    ctc.sql
REM
REM *************************************************************************
REM
REM Purpose:
REM   This script compares table columns for differences.
REM
REM Change History
REM
REM Date         Author             Description
REM ===========  =================  ================================================
REM 04-AUG-2014  Jeff Moss          Initial Version
REM *************************************************************************
UNDEFINE srcowner
UNDEFINE srctabname
UNDEFINE tgtowner
UNDEFINE tgttabname

ACCEPT srcowner PROMPT 'Please enter Name of Source Owner: '
ACCEPT srctabname PROMPT 'Please enter Name of Source Table: '
ACCEPT tgtowner PROMPT 'Please enter Name of Target Owner: '
ACCEPT tgttabname PROMPT 'Please enter Name of Target Table: '

SET WRAP OFF
WITH stg AS
(
SELECT table_name
,      column_name
,      column_id
FROM   dba_tab_columns
WHERE  owner = '&&srcowner'
AND    table_name = '&&srctabname'
)
, tgt AS
(
SELECT table_name
,      column_name
,      column_id
FROM   dba_tab_columns
WHERE  owner = '&&tgtowner'
AND    table_name = '&&tgttabname'
)
SELECT COALESCE(stg.column_id,tgt.column_id) column_id
,      stg.column_name
,      tgt.column_name
,      (CASE WHEN stg.column_name = tgt.column_name THEN 'MATCH' ELSE 'PROBLEM' END) indicator
FROM   stg
FULL OUTER JOIN tgt
ON  stg.column_id = tgt.column_id
ORDER BY 1
/