Skip to main content
Solved

How to find out which tables contain a certain column

  • December 8, 2022
  • 3 replies
  • 322 views

Forum|alt.badge.img+5

Hi!

As I said in the title, I need to know which table contains a specific column. Would the following query work for that? What should I put in the from-clause?

 

SELECT      COLUMN_NAME AS 'ColumnName', TABLE_NAME AS  'TableName'

FROM        

WHERE       COLUMN_NAME LIKE '%storage_instructions%'

ORDER BY    TableName

            ,ColumnName;

Best answer by ashen_malaka_ranasinghe

To find all tables with a particular column if you know the column name accurately:

select owner, table_name from all_tab_columns where upper(column_name) = upper('<name_of_the_column>');

 To find all tables with a particular column if you don't know the accurate column:

select owner,table_name from all_tab_columns where upper(column_name) like upper('%keyword%');
View original
Did this topic help you find an answer to your question?

3 replies

  • Superhero (Employee)
  • 1432 replies
  • December 8, 2022

You could try to use the view DICTIONARY_SYS_TAB_COLUMNS. You won’t find a column called “storage_instructions”, unless you use UPPER(). 


ashen_malaka_ranasinghe
Hero (Employee)
Forum|alt.badge.img+11

To find all tables with a particular column if you know the column name accurately:

select owner, table_name from all_tab_columns where upper(column_name) = upper('<name_of_the_column>');

 To find all tables with a particular column if you don't know the accurate column:

select owner,table_name from all_tab_columns where upper(column_name) like upper('%keyword%');

Forum|alt.badge.img+5
  • Author
  • Sidekick (Customer)
  • 9 replies
  • December 8, 2022

Thank you, I found the right table 👍


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings