Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Find all tables with a particular field

1 view
Skip to first unread message

Fred Wilson

unread,
Sep 18, 2006, 7:58:54 PM9/18/06
to
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

DA Morgan

unread,
Sep 18, 2006, 8:01:47 PM9/18/06
to

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

Mark D Powell

unread,
Sep 18, 2006, 8:24:04 PM9/18/06
to

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

DA Morgan

unread,
Sep 18, 2006, 11:14:00 PM9/18/06
to

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

Mark D Powell

unread,
Sep 19, 2006, 2:02:35 PM9/19/06
to

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

Andy Hassall

unread,
Sep 19, 2006, 4:03:58 PM9/19/06
to
On Mon, 18 Sep 2006 20:14:00 -0700, DA Morgan <damo...@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 :: an...@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

DA Morgan

unread,
Sep 19, 2006, 5:01:30 PM9/19/06
to
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. :-(
--

Jaap W. van Dijk

unread,
Sep 20, 2006, 1:12:24 PM9/20/06
to
On Mon, 18 Sep 2006 17:01:47 -0700, DA Morgan <damo...@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.

DA Morgan

unread,
Sep 20, 2006, 2:05:29 PM9/20/06
to

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

Jaap W. van Dijk

unread,
Sep 20, 2006, 4:43:00 PM9/20/06
to
On Wed, 20 Sep 2006 11:05:29 -0700, DA Morgan <damo...@psoug.org>
wrote:

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.

DA Morgan

unread,
Sep 20, 2006, 5:53:28 PM9/20/06
to

Please don't get me started on how Oracle continues to treat everything
like it was a table in the data dictionary. The worst being EXECUTE
privilege on a package where you have to use "AND TABLE_NAME =
<package_name>; I cringe every time I teach this.

I understand your point and your clarification of what I wrote is
appreciated.

Note to Oracle:

CREATE VIEW user_obj_privs AS
SELECT grantee, owner, table_name AS OBJ_NAME,
grantor, privilege, grantable, hierarchy
FROM user_tab_privs;

No license fee required for the use of this DDL if incorporated
into 11g.


--
Daniel Morgan
University of Washington

Fred Wilson

unread,
Sep 20, 2006, 7:08:27 PM9/20/06
to
Not to sound too stupid, but do I type the select statement directly in
SQLPlus?

I can hold my own in Access but this Oracle is a whole new world for me.

Thanks,

Fred


Jaap W. van Dijk wrote:

DA Morgan

unread,
Sep 20, 2006, 11:56:32 PM9/20/06
to

Yes. But not the one above as it is intended as a model. 'xxx' is
intended to stand for dba as in dba_tab_columns, all as in
all_tab_columns, or user, as in user_tab_columns.
--

0 new messages