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

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 2

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';

Answer 3

View All columns of a particular table user use "ALL_TAB_COLUMNS"

If you wants to describe a particular table user use "DESC Table_name;"

Kindly Try this..

Answer 4

You can use the USER_TAB_COLUMNS tables

Answer 5

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.