CODEWITHSUNDEEP
oracle-database
Kostas75
Kostas75

Reputation: 341

How to search a column name within all tables of a database at specific schema

select table_name, column_name
from all_tab_columns 
where column_name = '<YOUR_COLUMN_HERE_IN_CAPITAL_CASE>'

How can I use the above columns search query for a specific schema at Oracle DB?

I am asking because I have tried the query and it also returned many tables outside of the schema I am interested into, so they are of no interest to me.

Upvotes: 12

Views: 51585

Answers (3)

Du-Lacoste
Du-Lacoste

Reputation: 12787

Use the following.

You can try this via SQL tool that is used by you

select table_name from all_tab_columns where column_name = 'PICK_COLUMN';

Or if you have DBA privileges, 

select table_name from dba_tab_columns where column_name = 'PICK_COLUMN';

But if you are not sure about the column names you can add LIKE statements to current query.

Eg:

select table_name from all_tab_columns where column_name LIKE '%PICK_COLUMN%';

Upvotes: 3

tvCa
tvCa

Reputation: 816

You can use the LIKE function to refine a search. I don't know the exact names of the columns, but you could add the owner and the table name to this one:

select table_name, column_name
from all_tab_columns 
where column_name = '<YOUR_COLUMN_HERE_IN_CAPITAL_CASE>'

in a way similar to this:

select column1,column2,column3,more_columns
from all_tables_you_need
where column1 like '%&column1%'
and column2 like '%&column2%'
and column3 like '%&column3%';

Either of those 3 variables can also be empty, but you can also put a very specific one in there, so that the result will vary a lot. Behold use of the UPPER function, if you are not sure of the case, or you want to ignore case sensitivity.

Upvotes: 2

Multisync
Multisync

Reputation: 8797

select table_name, column_name
FROM all_tab_columns 
where column_name = '<YOUR_COLUMN_HERE_IN_CAPITAL_CASE>' 
  and owner = '<schema>';

all_tab_columns contains all the columns on which the current user has privileges. So it may not return all the data.

dba_tab_columns contains information about all columns, but you may need some special privileges to query this dictionary view.

And finally, if you're interested only in the columns of all tables owned by the current user you can use:

select table_name, column_name
FROM user_tab_columns 
where column_name = '<YOUR_COLUMN_HERE_IN_CAPITAL_CASE>';

But this view doesn't have an OWNER column (it only contains all the columns owned by the current user)

Upvotes: 15

Related Questions