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

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

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 --

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

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 --

On Mon, 18 Sep 2006 20:14:00 -0700, DA Morgan <damorgan@psoug.org> wrote:

>In most databases no one will put anything in tab_comments. ;-) C'est
>dommage.

I think we may actuallly agree on something here ;-) IMHO the comprehensive
use of (at least) "comment on table <table>" and "comment on column
<table.column>" should be a compulsory part of any organisation's coding
standards.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Mark D Powell wrote:
> DA Morgan wrote:
>> Mark D Powell wrote:

>>> 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 --

Trying to earn the "good programmer" award? ;-)

You're going to make the rest of us look like what we are. :-(
--
Puget Sound Oracle Users Group

On Mon, 18 Sep 2006 17:01:47 -0700, DA Morgan <damorgan@psoug.org>
wrote:


>The one you are looking for is: xxx_tab_columns.
>--

This view gives the columns of all tables * and views *. The OP only
wanted columns of tables so he has to eliminate the views from this
view.

Jaap.

Jaap W. van Dijk wrote:
> On Mon, 18 Sep 2006 17:01:47 -0700, DA Morgan <damorgan@psoug.org>
> wrote:
>
>
>> The one you are looking for is: xxx_tab_columns.
>> --
>
> This view gives the columns of all tables * and views *. The OP only
> wanted columns of tables so he has to eliminate the views from this
> view.
>
> Jaap.

"so he has to eliminate the views from this view."

We are not communicating well. I thought the OP wanted to know which
objects contained a specific column name such as:

SELECT table_name
FROM user_tab_columns
WHERE column_name = 'EMPID';
--
Daniel Morgan
Puget Sound Oracle Users Group

On Wed, 20 Sep 2006 11:05:29 -0700, DA Morgan <damorgan@psoug.org>
wrote:

>Jaap W. van Dijk wrote:
>> On Mon, 18 Sep 2006 17:01:47 -0700, DA Morgan <damorgan@psoug.org>
>> wrote:
>>
>>
>>> The one you are looking for is: xxx_tab_columns.
>>> --
>>
>> This view gives the columns of all tables * and views *. The OP only
>> wanted columns of tables so he has to eliminate the views from this
>> view.
>>
>> Jaap.
>
>"so he has to eliminate the views from this view."
>
>We are not communicating well. I thought the OP wanted to know which
>objects contained a specific column name such as:
>
>SELECT table_name
>FROM user_tab_columns
>WHERE column_name = 'EMPID';
>--
>Daniel Morgan
>Puget Sound Oracle Users Group

Your SELECT will also find views (and clusters) with the column_name
EMPID. I thought that the OP might not know this and I wanted to warn
him, because he specifically asked for *tables* containing a column
with a certain name.

Jaap.