111 lines
4.1 KiB
SQL
111 lines
4.1 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Search for column_name
|
|
* Parameters : 1 - OWNER (% - wildchar, \ - escape char, Default value '%')
|
|
* 2 - table_name (% - wildchar, \ - escape char, Default value '%')
|
|
* 3 - column_name (% - wildchar, \ - escape char, Default value '%')
|
|
* 3 - ObjectType (% - wildchar, \ - escape char, Default value '%')
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-Aug-04 Vishal Gupta First Draft
|
|
* 02-May-12 Vishal Gupta change input parameters
|
|
* 19-Jul-12 Vishal Gupta Added object type as input parameter
|
|
*
|
|
*/
|
|
set term on
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
DEFINE column_name="&&3"
|
|
DEFINE object_type="&&4"
|
|
|
|
|
|
set term off
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
COLUMN _column_name NEW_VALUE column_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
|
|
SELECT SUBSTR(UPPER('&&owner'), 1 , CASE INSTR('&&owner','.') WHEN 0 THEN LENGTH ('&&owner') ELSE INSTR('&&owner','.') - 1 END) "_owner"
|
|
, CASE WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1) ELSE UPPER('&&table_name') END "_table_name"
|
|
, CASE INSTR('&&owner','.') WHEN 0 THEN UPPER('&&column_name') ELSE UPPER('&&table_name') END "_column_name"
|
|
, CASE INSTR('&&owner','.') WHEN 0 THEN UPPER('&&object_type') ELSE UPPER('&&column_name') END "_object_type"
|
|
FROM DUAL
|
|
;
|
|
|
|
SELECT DECODE(UPPER('&&column_name'),'','%',UPPER('&&column_name')) "_column_name"
|
|
, DECODE(UPPER('&&object_type'),'','%',UPPER('&&object_type')) "_object_type"
|
|
FROM DUAL
|
|
;
|
|
|
|
set term on
|
|
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * COLUMNS LIST
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Object Name = '&&table_name'
|
|
PROMPT * - Column Name = '&&column_name'
|
|
PROMPT *****************************************************************
|
|
|
|
|
|
|
|
|
|
COLUMN object_name HEADING "ObjectName" FORMAT a45
|
|
COLUMN object_type HEADING "ObjectType" FORMAT a17
|
|
COLUMN column_name HEADING "ColumnName" FORMAT a30
|
|
COLUMN Type HEADING "ColumnType" FORMAT a30
|
|
COLUMN nullable HEADING "Null?" FORMAT a8
|
|
COLUMN data_default HEADING "Default|Value" FORMAT a30
|
|
|
|
|
|
|
|
SELECT c.owner || '.' || c.table_name object_name
|
|
, o.object_type
|
|
, c.column_name
|
|
, DECODE(c.nullable,'N','NOT NULL','') nullable
|
|
, CASE
|
|
WHEN c.data_type = 'NUMBER'
|
|
THEN c.data_type || NVL2(c.data_precision||c.data_scale, '(' || NVL(c.data_precision,'38') || ',' || NVL(c.data_scale,'127') || ')','')
|
|
WHEN c.data_type = 'FLOAT'
|
|
THEN c.data_type || NVL2(c.data_precision,'(' || c.data_precision || ')','')
|
|
WHEN c.data_type = 'VARCHAR2' OR c.data_type = 'CHAR'
|
|
THEN c.data_type || '(' || c.char_length || ' ' || DECODE(c.char_used,'B','BYTE','CHAR') || ')'
|
|
WHEN c.data_type = 'NVARCHAR2' OR c.data_type = 'NCHAR'
|
|
THEN c.data_type || '(' || c.char_length || ')'
|
|
WHEN c.data_type = 'RAW' OR c.data_type = 'UROWID'
|
|
THEN c.data_type || '(' || c.data_length || ')'
|
|
ELSE c.data_type
|
|
END Type
|
|
FROM dba_tab_columns c
|
|
, dba_objects o
|
|
WHERE o.owner = c.owner
|
|
AND o.object_name = c.table_name
|
|
AND o.SUBOBJECT_NAME IS NULL --Exclude paritions and sub-paritions
|
|
AND UPPER(c.owner) like UPPER('&&owner') ESCAPE '\'
|
|
AND UPPER(c.table_name) like UPPER('&&table_name') ESCAPE '\'
|
|
AND UPPER(c.column_name) like UPPER('&&column_name') ESCAPE '\'
|
|
AND UPPER(o.object_type) like UPPER('&&object_type') ESCAPE '\'
|
|
AND c.table_name NOT LIKE 'BIN$%'
|
|
ORDER BY c.column_name,c.owner, c.table_name ;
|
|
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
UNDEFINE column_name
|
|
UNDEFINE object_type
|
|
|
|
@@footer |