Re: Find all tables with a particular field

DA Morgan wrote:
> Fred Wilson wrote:
> > Hello,
> >
> > How does one go about finding all the tables that have a particular
> > field name? For example a field name "EVENT_CODE"
> >
> > I would like to find all the places in the database this is located.
> > Ultimately I want to further find in that field the code "0214" and
> > change it to "0200".
> >
> > Thank you,
> > Fred
>
> All relational databases have data dictionaries. Are you familiar with
> the concept? If not then I suggest the following query:
>
> SELECT view_name
> FROM all_views
> WHERE (view_name LIKE 'USER%' OR view_name LIKE 'ALL%')
> ORDER BY 1;
>
> The one you are looking for is: xxx_tab_columns.
> --
> Daniel Morgan
> University of Washington
> Puget Sound Oracle Users Group

The following short article offers a brief introduction to the Oracle rdbms dictionary views:

How do I find information about a database object: table, index, constraint, view, etc... in Oracle ?
http://www.jlcomp.demon.co.uk/faq/object_info.html

The view you want is all_tab_comments or the dba_ or user_ versions of it.

You can find documentation on all the Oracle provided dictionary views in the Oracle Reference Manual along with sections on the database parameters and the dynamic performance views.

HTH -- Mark D Powell -- Received on Mon Sep 18 2006 - 19:24:04 CDT