Re: how can i do to retrieve a particular table?

In article <8seqbc$91g$1_at_fe1.cs.interbusiness.it>,   "Maru" <marucanc_at_tin.it> wrote:
> Hi everyone!
> Please
> could you tell me how to do the next thing:
> I have an oracle database. I have a table called "CODICI" and a field
 of
> CODICI called "GE_CODICE".
> I need to retrieve all names of related tables to CODICI which
 contain a
> field called exactly like that, "GE_CODICE" .
> Which SQL?
>
> Thank you.
> Maru
>
1) A query that is specifically on how do something in a particul rdbms system should for best results be posted to that verdor's product boards and not on this a general theory board.

2) In Oracle you would query all_constraits and look for the FK's defined against the CODICI table, and from CODICI columns to other tables. Tables are only related to each other at the database level if FK's are definded to or from them [constraint_type = 'R'].

3) If is possible that the constraints are maintained only in application logic. You have to read the code to find the related tables in this case unless and ERD or other documentation is available.

You can query all_tab_columns for all columns with the same name to find other tables with the same column name. The results may be related, but the same name may not be used in all tables to hold the same data so you may not find all related columns, and some of those you do find may only be distant relatives at best.

set echo off
rem
rem Filename: col_search.sql
rem SQL*Plus script to search for all tables with an identically named rem column.
rem
rem 19960506 m d powell New Script to help locate data useage. rem
set pagesize 55
set verify off
column owner format a12
rem
select owner, table_name, column_name
from sys.dba_tab_columns
where column_name = upper('&colname') order by owner, table_name
/

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.