How to find tables and columns in Oracle Databases

Very often is necessary to find tables in a database, that corresponds to a specific pattern and there are specific Oracle Database tables which describe that information:

ALL_TABLES – list of all tables in the current database that are accessible to the current user;
ALL_TAB_COLUMNS – list of all columns in the database that are accessible to the current user;

• Find all Tables that have PATTERN in the table name:

SELECT TABLE_NAME FROM  ALL_TABLES
WHERE TABLE_NAME LIKE '%PATTERN%';

• Find all tables that have at least one column that matches a specific PATTERN in the column name:

SELECT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE '%PATTERN%';

Advertisement