Re: Find all tables with a particular field

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 19 Sep 2006 11:02:35 -0700
Message-ID: <1158688955.672609.198070@i3g2000cwc.googlegroups.com>

DA Morgan wrote:
> Mark D Powell wrote:
> > 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 --

>

> I am sure it is a typo Mark but all the tables with a particular column
> name are likely not going to show up in tab_comments ... rather tab_columns.

>

> In most databases no one will put anything in tab_comments. ;-) C'est
> dommage.
> --
> Daniel Morgan
> Puget Sound Oracle Users Group

Yep, just finished fixing a couple of table comment statements so naturally my fingers and brain were following different paths.

HTH -- Mark D Powell -- Received on Tue Sep 19 2006 - 13:02:35 CDT