Find All Tables In An Oracle Database By Column Name

I work with very large Oracle enterprise databases all the time. Consistently, I find myself trying to sift through schemas in the database to find relationships between tables. When tables aren’t named appropriately and you have a lot of them, this can be a long and painful process if you do it manually.

Lucky for us, there is a convenient way to search for the tables you’re looking for based on column information.

In your SQL tool of choice take the following command:

select table_name from all_tab_columns where column_name = 'PICK_COLUMN';

If you’ve got DBA privileges, you can try this command instead:

select table_name from dba_tab_columns where column_name = 'PICK_COLUMN';

Now if you’re like me, you may not even know what the column you’re searching for is really named. One can only hope the name is similar, which is why you can use the like clause and throw in a few wild cards.

When describing the structure of the all_tab_columns view, you get the following:

The column_name, table_name, and owner are the only fields I use, but you may find others of use. If you want to search for tables in a particular schema based on column information, you would set the owner to the name of your schema in the where clause.

Nic Raboy

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in C#, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Unity. Nic writes about his development experiences related to making web and mobile development easier to understand.