Oracle – SQL: Find table(s) having a particular column

Some time to solve problems, we need to check database, but unluckily we don’t the exact table name and/or column to look for.

Following are the handy queries which solves out these problems—

  • When you just know few words of table name, then below query list down all the tables having similar name-

SELECT * FROM TAB WHERE TNAME LIKE ‘%few_parts_of_name%’;

  • Some times from application code we can guess the table name but not able to find out exact table name. Following query shows how to list down all the tables having a column name –

SELECT table_name, column_name

FROM all_tab_columns

WHERE column_name LIKE '%PUT_HERE_COLUMN_NAME%'

ORDER BY table_name, column_name;

This query show all the tables and/or views having column name like <PUT_HERE_COLUMN_NAME

> in all the available schema.

If you are sure about the schema, following flavor will narrow down the result—


SELECT owner, table_name, column_name

FROM dba_tab_columns

WHERE column_name like '%PUT_HERE_COLUMN_NAME%'

and owner='APPS'

ORDER by table_name;

Additionally, you can use TABLE_NAME in where clause inside both queries to narrow down the result.

Keep this handy; it’s kind of utility queries which are always helpful.

Happy Reading,

Nirbhaya Bhava!!!

Advertisements

2 Comments

  1. can you tell why this, doesn’t work in oracle

    select owner, table_name from all_tab_columns where column_name like ‘FEED%’ and column_name like ‘BOD%’;

    1. Hi Sujeet,
      In your query- column name must start with FEED and again second expression tells column name must start with BOD.
      How a column name can start with two different string at given point of time.
      I don’t know what is exact requirement, but if you are looking for all such table having column name start with either FEED or BOD, you may rewrite query as follows–

      select owner, table_name
      from all_tab_columns
      where column_name like 'FEED%'
      or column_name like 'BOD%';

      I can help you more if you can provide your exact requirement. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s