Re: Find all tables with a particular field

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 18 Sep 2006 20:14:00 -0700
Message-ID: <1158635637.979606@bubbleator.drizzle.com>

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

Received on Mon Sep 18 2006 - 22:14:00 CDT