Find all tables in db with column name of a particular string? — oracle-tech

    Forum Stats

  • 3,715,871 Users
  • 2,242,896 Discussions
  • 7,845,669 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Find all tables in db with column name of a particular string?

308675
308675 Member Posts: 162
edited December 2010 in SQL Developer
I'm looking for all tables in a db that have a certain column name. How can I find this?
TenLeftFingersTahsin_can

Best Answer

  • dhalek
    dhalek Member Posts: 186 Bronze Badge
    Accepted Answer
    Hello Atlas77,
    you are posting into the wrong forum. This one is for the tool called SQL Developer.
    Having said this, you can try the following:
    select table_name from dba_tab_columns where column_name='THE_COLUMN_YOU_LOOK_FOR';
    If you don't have DBA privileges, you can try this:
    select table_name from all_tab_columns where column_name='THE_COLUMN_YOU_LOOK_FOR';
    HTH,
    dhalek
    Tahsin_can

Answers

  • dhalek
    dhalek Member Posts: 186 Bronze Badge
    Accepted Answer
    Hello Atlas77,
    you are posting into the wrong forum. This one is for the tool called SQL Developer.
    Having said this, you can try the following:
    select table_name from dba_tab_columns where column_name='THE_COLUMN_YOU_LOOK_FOR';
    If you don't have DBA privileges, you can try this:
    select table_name from all_tab_columns where column_name='THE_COLUMN_YOU_LOOK_FOR';
    HTH,
    dhalek
    Tahsin_can
  • 308675
    308675 Member Posts: 162
    I was just hoping there might be a button on SQL Developer that would give me the same answer.
    TenLeftFingers
  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,215 Employee
    Find Db object -> More -> "Type = Column" & Name = <colname>
  • DanyC
    DanyC Member Posts: 368
    Hey Vadim,

    Would be possible to do this from the Schema browser too? AFAIK Schema browser allows you to search only for tables names but not for tables with a specific column.

    Thanks,
    Dani
  • John McGinnis-Oracle
    John McGinnis-Oracle Member Posts: 239 Employee
    The quick search field in the schema browser is doing client-side filtering of the list of objects. This means we can only filter on things that we already have fetched from the database, like the object name. We have no current plans to extend the types of information that can filter on, although it is possible we might add the ability to filter based on a few other types of information that we generally fetch with the object name. However, filtering by column name would require pre-fetching the lists of columns for all tables and as such is not likely to be added to the schema browsers search field.

    - John McGinnis
    SQL Developer Team
  • DanyC
    DanyC Member Posts: 368
    John,

    thanks for your answer.
    John Mcginnis wrote:
    The quick search field in the schema browser is doing client-side filtering of the list of objects. This means we can only filter on things that we already have fetched from the database, like the object name. We have no current plans to extend the types of information that can filter on, although it is possible we might add the ability to filter based on a few other types of information that we generally fetch with the object name. However, filtering by column name would require pre-fetching the lists of columns for all tables and as such is not likely to be added to the schema browsers search field.
    I'm sure you guys had a Toad review to understand how things are done there.....so

    My 2 cents are: instead of pre-fetching the columns of all tables why don't you add a button below the table drop-down list which should fetch the tables based on the filter columns condition - exactly like Toad.

    Thanks,
    Dani
This discussion has been closed.