Query to pull all the table names and column names.

Question

I need a query (ORACLE) to pull all the table names and column names in a database for a given value?

Example: If I give a value as "TEST", I need a query which pulls all the TABLE_NAMES and COLUMN_NAMES which has the value "TEST".

Answer 1

You can use the USER_TAB_COLUMNS tables

Answer 2

select table_name, null column_name from all_tables where table_name like '%TEST%'
union all
select null, column_name from all_tab_columns where column_name like '%TEST%';

Answer 3

This should get you columns and tables and views:

SELECT 'Column: '||owner||'.'||table_name||'.'||column_name 
  FROM dba_tab_columns 
 WHERE column_name = 'TEST'
UNION ALL 
SELECT 'Table: '||owner||'.'||table_name 
  FROM dba_tables 
 WHERE table_name = 'TEST'
UNION ALL 
SELECT 'View: '||owner||'.'||view_name
  FROM dba_views
 WHERE view_name = 'TEST';

Note you can also use the ALL_* dictionary views if you don't have access to the DBA_ views, but you'll only see objects you have access to.

Answer 4

Another way is using bind variables within a procedure such

DEFINE vSearch = '%TEST%'

ACCEPT vSearch char PROMPT 'Enter a search value: '

SELECT * FROM USER_TAB_COLS WHERE column_name LIKE '&&vSearch' OR table_name LIKE '&&vSearch';