CODEWITHSUNDEEP
sqloracle-database
Caffeinated
Caffeinated

Reputation: 12484

In Oracle, how do you search a tables' column-names?

I have a large table with over 50 columns. And I would like to search those columns for a particular word. How do I do this in Oracle ?

thanks

Upvotes: 2

Views: 83

Answers (1)

Nikhil Batra
Nikhil Batra

Reputation: 3148

You can use the following:

select COLUMN_NAME from ALL_TAB_COLUMNS 
where TABLE_NAME='your_table_name' and COLUMN_NAME like '%whatever_word_required%';

Explanation: ALL_TAB_COLUMNS describes the columns of the tables, views, and clusters accessible to the current user. So from all the tables, since you want to search in a particular table so give your table-name in the query and specify the criteria for columns using the LIKE.

Say if your table name is "Table1" and column-name you are trying to search is "Employee", so the query becomes:

select COLUMN_NAME from ALL_TAB_COLUMNS 
where TABLE_NAME='Table1' and COLUMN_NAME like '%Employee%';

For reference see the Oracle Docs

Upvotes: 4

Related Questions