Find a table 4

[rboscia]

I am VERY new at Oracle and have not yet been at any classes except SQL/SQL Plus & PL/SQL so please bear with me.
( I did not build the database or tables and unfortunately no one that still works here did, so all the documentation is not there.)
I am looking for a field in a table. Is there an easy way to get all the table names and field names? I know how to describe, but I don't have all the table names...
Also once I find this field, is there an easy way to see if it is joined to any field in another table? Am I looking for a needle in a haystack? Or is this an easy step that I am making difficult?
Thanks for your help

 

[Nebuchednezzar]

You can query several areas that may help you find what you're looking for...
Log in as the user who owns the tables you will be looking through...
Select * from dba_tables; (this will give you a list of all the table names that user owns).

Select * from user_cons_columns; (this will give you a list of all tables and all columns in each table.

If you know what the column name is that you are looking for, you can run...
Select * from user_cons_columns where column_name = '...'; (just put the column name in the single quotes, in all upper-case).

If/when you find the table, you can go in through schema manager and drill down to the table...under the table drill down will be an option for indexes. Expand the indexes item...if there are any indexes listed, one of these may help point you to any links between tables. If the person(s) who created the database used basic naming techniques, any foreign key indexes will start with FK_.

Good luck.

 

[MikeLacey]

Neb,

Sorry to disagree -- but I thought that user_cons_columns held details of constraints?

The tables I use to ask this sort of question are (I think):

user_tables and user_tab_columns Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[THoey]

Depending on the version of Oracle you are using, Oracle 8.1.6 has DBA Studio that will show you all of the tables, indexes, etc for each user. I use it all the time now that I figured it out.

The following code (off the cuff, not tested) should create a file called DESC.SQL,that when run through SQL*PLUS, should create a file called RESULTS.TXT, which is a report of all tables and their fields for the logged in user.

set heading off;
set pagesize 0;
set lines 80;
set feedback off;
set echo off;
spool desc.sql;

SELECT 'set heading off;' FROM DUAL;
SELECT 'set pagesize 0;' FROM DUAL;
SELECT 'set lines 80;' FROM DUAL;
SELECT 'set feedback off;' FROM DUAL;
SELECT 'set echo off;' FROM DUAL;
SELECT 'spool results.txt; FROM DUAL;

select 'DESC ' || TABLE_NAME || ';' 
from user_tables
where table_name not like 'SM%' and
      table_name not like 'EVT_%';

SELECT 'spool off;' FROM DUAL;
SELECT 'set heading on;' FROM DUAL;
SELECT 'set feedback on; FROM DUAL;
SELECT 'set echo on; FROM DUAL;

spool off;
set heading on; 
set feedback on;
set echo on;
quit;

Hope that helps...
Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[carp]

For all of the table names and column names in your schema, you could use

SELECT table_name, column_name
FROM user_tab_columns
ORDER BY 1,2;

To find all tables that are joined by a foreign key constraint, you can use

select cc1.table_name||' is linked to '||cc2.table_name
from dba_cons_columns cc1,
dba_cons_columns cc2,
dba_constraints c
where cc1.constraint_name = c.constraint_name
and cc1.owner = c.owner
and c.r_constraint_name = cc2.constraint_name
and c.r_owner = cc2.owner;