Monday, December 27, 2010

Reflection with Oracle DB: getting the list of all columns with a given name

SQL> select unique object_type from user_objects order by object_type;

OBJECT_TYPE
-------------------
INDEX
INDEX PARTITION
LOB
PACKAGE
PACKAGE BODY
PROCEDURE
QUEUE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
VIEW

with USER_OBJECTS you have information only on TABLES, not on COLUMNS.

Also
Select * From User_Tables;

Select * From all_Tables;

doesn't mention COLUMNS.

To find out about COLUMNS, use ALL_TAB_COLUMNS:

SELECT OWNER, TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%INSTANCE%' order by OWNER;


See http://en.wikipedia.org/wiki/Oracle_metadata for more on Oracle Metadata and useful SQL.

No comments: