Tables to Find Objects/Table/Columns

Tables to Find Objects/Table/Columns

General Objects / Tables / Columns

PROMPT Find Object by Type, Name, Status
select object_name, object_type, owner, status
from dba_objects
where upper(object_name) like upper('%&object_name%')
and object_type like upper('%'|| NVL('&object_type', 'TABLE')|| '%')
and status like upper('%'|| NVL('&Status', '%')|| '%')
order by object_name, object_type;

PROMPT Find table indexes, keys, columns
select index_name,column_position,column_name
from all_ind_columns
where table_name = '&EnterTableName'
order by index_name, column_position;

PROMPT Find tables and columns that include a table and/or column name specified.
select table_name, column_name
from dba_tab_columns
where column_name like NVL(UPPER('&COLUMN_NAME'), column_name)
and table_name like NVL(UPPER('&TABLE_NAME'), table_name);