123 lines
5.0 KiB
SQL
123 lines
5.0 KiB
SQL
@@header
|
|
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Show column dependency
|
|
* Versions : 11.1 and above
|
|
* Parameters : 1 - Owner (% - wildchar, \ - escape char)
|
|
* 2 - Object Name (% - wildchar, \ - escape char)
|
|
* 3 - Column Name (% - wildchar, \ - escape char)
|
|
* 4 - Dependent Owner (% - wildchar, \ - escape char)
|
|
* 5 - Dependent Name (% - wildchar, \ - escape char)
|
|
* 6 - Dependent Type (% - wildchar, \ - escape char)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 12-Nov-15 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
UNDEFINE referenced_owner
|
|
UNDEFINE referenced_name
|
|
UNDEFINE referenced_column_name
|
|
UNDEFINE dependent_owner
|
|
UNDEFINE dependent_name
|
|
UNDEFINE dependent_type
|
|
|
|
DEFINE referenced_owner="&&1"
|
|
DEFINE referenced_name="&&2"
|
|
DEFINE referenced_column_name="&&3"
|
|
DEFINE dependent_owner="&&4"
|
|
DEFINE dependent_name="&&5"
|
|
DEFINE dependent_type="&&6"
|
|
|
|
|
|
|
|
COLUMN _referenced_owner NEW_VALUE referenced_owner NOPRINT
|
|
COLUMN _referenced_name NEW_VALUE referenced_name NOPRINT
|
|
COLUMN _referenced_column_name NEW_VALUE referenced_column_name NOPRINT
|
|
COLUMN _dependent_owner NEW_VALUE dependent_owner NOPRINT
|
|
COLUMN _dependent_name NEW_VALUE dependent_name NOPRINT
|
|
COLUMN _dependent_type NEW_VALUE dependent_type NOPRINT
|
|
|
|
set term off
|
|
|
|
SELECT DECODE(UPPER('&&referenced_owner'),'','%','&&referenced_owner') "_referenced_owner"
|
|
, DECODE(UPPER('&&referenced_name'),'','%','&&referenced_name') "_referenced_name"
|
|
, DECODE(UPPER('&&referenced_column_name'),'','%','&&referenced_column_name') "_referenced_column_name"
|
|
, DECODE(UPPER('&&dependent_owner'),'','%','&&dependent_owner') "_dependent_owner"
|
|
, DECODE(UPPER('&&dependent_name'),'','%','&&dependent_name') "_dependent_name"
|
|
, DECODE(UPPER('&&dependent_type'),'','%','&&dependent_type') "_dependent_type"
|
|
FROM DUAL
|
|
;
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***************************************************************************************************
|
|
PROMPT * D E P E N D E N C Y
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&referenced_owner'
|
|
PROMPT * - Object Name = '&&referenced_name'
|
|
PROMPT * - Column Name = '&&referenced_column_name'
|
|
PROMPT * - Dependent Owner = '&&dependent_owner'
|
|
PROMPT * - Dependent Name = '&&dependent_name'
|
|
PROMPT * - Dependent Type = '&&dependent_type'
|
|
PROMPT ***************************************************************************************************
|
|
|
|
|
|
|
|
|
|
COLUMN referenced_object_name HEADING "Object Name" FORMAT a40
|
|
COLUMN referenced_column_name HEADING "Column Name" FORMAT a20
|
|
COLUMN referenced_type HEADING "Object Type" FORMAT a20 TRUNCATE
|
|
COLUMN object_name HEADING "Dep Object Name" FORMAT a50
|
|
COLUMN type HEADING "DepObject Type" FORMAT a20 TRUNCATE
|
|
COLUMN referenced_link_name HEADING "Ref Link Name" FORMAT a20
|
|
COLUMN dependency_type HEADING "Depend|Type" FORMAT a10
|
|
|
|
BREAK ON referenced_object_name ON referenced_column_name
|
|
|
|
SELECT d.referenced_owner || '.' || d.referenced_name referenced_object_name
|
|
, c.name referenced_column_name
|
|
--, d.referenced_type
|
|
, d.owner || '.' || d.name object_name
|
|
, d.type
|
|
, d.referenced_link_name
|
|
, d.dependency_type
|
|
FROM dba_dependencies d
|
|
JOIN dba_objects o ON o.owner = d.owner AND o.object_name = d.name AND o.object_type = d.type
|
|
JOIN dba_objects ro ON ro.owner = d.referenced_owner AND ro.object_name = d.referenced_name AND ro.object_type = d.referenced_type
|
|
JOIN sys.dependency$ d2 ON d2.d_obj# = o.object_id AND d2.p_obj# = ro.object_id AND d2.d_attrs IS NOT NULL
|
|
JOIN sys.col$ c ON c.obj# = d2.p_obj#
|
|
/*
|
|
- D_ATTRS is raw column containing HEX value.
|
|
- First 8 characters are something like 00010000 or 00030000 or 00030100, they can be ignored.
|
|
- Digits from 9th character onwards represent the BITAND flag for column in bytes for 8 column pairs
|
|
- 9-10 digit represent first 8 columns
|
|
- 11-12 digit represent next 8 columns
|
|
- and so on.
|
|
*/
|
|
AND power(2,mod(c.col#,8)) = BITAND( TO_NUMBER(SUBSTR(SUBSTR(d2.d_attrs,9), 1 + 2*TRUNC(c.col#/8) ,2) ,'XX' ) , power(2,mod(c.col#,8)) )
|
|
WHERE 1=1
|
|
AND d.referenced_owner LIKE '&&referenced_owner'
|
|
AND d.referenced_name LIKE '&&referenced_name'
|
|
AND c.name LIKE '&&referenced_column_name'
|
|
AND d.owner LIKE '&&dependent_owner'
|
|
AND d.name LIKE '&&dependent_name'
|
|
AND d.type LIKE '&&dependent_type'
|
|
ORDER BY d.referenced_owner
|
|
, d.referenced_name
|
|
, c.name
|
|
, d.owner
|
|
, d.name
|
|
;
|
|
|
|
@@footer
|