DBA Table Columns

Description

Categories
Enginatics
Repository
GitHub
Report with all table column names based on dba_tab_columns, as finding tables by column names is a frequent task during SQL development

SQL 15 lines

select
dtc.owner,
dtc.table_name,
dtc.column_name,
dtc.data_type||case when dtc.data_type like '%CHAR%' then ' ('||dtc.data_length||')' end data_type,
decode(dtc.nullable,'N',xxen_util.meaning(dtc.nullable,'YES_NO',0)) nullable,
dtc.column_id
from
dba_tab_columns dtc
where
1=1
order by
dtc.owner,
dtc.table_name,
dtc.column_id

Parameters 3

ParameterTypeList of values SQL
Column Name containsChar
lower(dtc.column_name) like '%'||lower(:column_name)||'%'
Table Name starts withLOV
lower(dtc.table_name) like lower(:table_name)||'%'
Exclude ViewsLOV
(dtc.owner, dtc.table_name) in (select dt.owner, dt.table_name from dba_tables dt)