The query below shows all the tables that have the 
column named -> table_name.

In real time database/code enhancement analysis, ETL 
processes and other architectural analysis, the 
required column_name input will list all tables 
that have the named column, its datatype and
size.

Insert Into Table -> base query template

Tables With Datatype -> XMLType

All_Tab_Columns Query

SELECT 
  owner||'.'||table_name||'['||LOWER(column_name)||']' 
    "TABLE_NAME[column_name]",
  SUBSTR(DECODE(data_type,
  'VARCHAR2',data_type||'('||data_length||')',
  'CHAR',data_type||'('||data_length||')',
  'NUMBER',data_type||
   DECODE(data_precision,NULL,'',
    '('||data_precision||
    DECODE(Nvl(Data_Scale,0),0,'',','||Data_Scale)||')'),
  Data_Type),1,20) As "Type"
FROM  all_tab_columns
WHERE column_name = UPPER('&i_col')
-- AND  owner = USER
;

  All_Tab_Columns Query

Oracle registered trademark of Oracle Corporation.

Last Revised on: March 01, 2012

  1404