Thursday, October 29, 2009

ORACLE query which returns all table names which contains a particular field.

The following ORACLE query returns all table names which contains the column 'EMPLOYEE_ID'.
SELECT * FROM ALL_TAB_COLUMNS C, ALL_OBJECTS O
WHERE C.TABLE_NAME = O.OBJECT_NAME
AND C.OWNER = O.OWNER
AND O.OBJECT_TYPE = 'TABLE'
AND COLUMN_NAME LIKE 'EMPLOYEE_ID'
AND C.OWNER = 'YOUR_SCHEMA_NAME'
AND TABLE_NAME NOT LIKE 'BIN%'
PS: Replace YOUR_SCHEMA_NAME with the name of your DB schema.