Some time to solve problems, we need to check database, but unluckily we don’t the exact table name and/or column to look for.
Following are the handy queries which solves out these problems—
- When you just know few words of table name, then below query list down all the tables having similar name-
SELECT * FROM TAB WHERE TNAME LIKE ‘%few_parts_of_name%’;
- Some times from application code we can guess the table name but not able to find out exact table name. Following query shows how to list down all the tables having a column name –
SELECT table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%PUT_HERE_COLUMN_NAME%' ORDER BY table_name, column_name;
This query show all the tables and/or views having column name like <PUT_HERE_COLUMN_NAME
> in all the available schema.
If you are sure about the schema, following flavor will narrow down the result—
SELECT owner, table_name, column_name FROM dba_tab_columns WHERE column_name like '%PUT_HERE_COLUMN_NAME%' and owner='APPS' ORDER by table_name;
Additionally, you can use TABLE_NAME in where clause inside both queries to narrow down the result.
Keep this handy; it’s kind of utility queries which are always helpful.