is there a way to get listing of table relationships

20 Jan 2006 8:02 PM

Rich

Hello,

I am examining the relationships, indexes, constraints of tables in a DB
that I am supporting. There is no documentation, so I want to get a listing
of the relationships rather than just looking at the database diagram and
examining each table individually. I looked at
information_schema.table_constraints which gave me a listing of all the
contraints. But is there a way to get a listing of the relationships?

20 Jan 2006 8:27 PM

Mark Williams

Try

select t1.constraint_name, t2.table_name as "referencing table",
t2.column_name AS "referencing column",
t3.table_name AS "referenced table",
t3.column_name AS "referenced column"
from information_schema.referential_constraints t1
INNER JOIN information_schema.constraint_column_usage t2 ON
t1.constraint_name = t2.constraint_name
INNER JOIN information_schema.constraint_column_usage t3 ON
t1.unique_constraint_name = t3.constraint_name

You'll have to read the output carefully to see where composite keys are used.

--

Show quote

"Rich" wrote:

> Hello,
>
> I am examining the relationships, indexes, constraints of tables in a DB
> that I am supporting. There is no documentation, so I want to get a listing
> of the relationships rather than just looking at the database diagram and
> examining each table individually. I looked at
> information_schema.table_constraints which gave me a listing of all the
> contraints. But is there a way to get a listing of the relationships?

20 Jan 2006 8:34 PM

Rich

That was exactly what I was looking for! Thank you very much for your help.

Rich

Show quote

"Mark Williams" wrote:

> Try
>
> select t1.constraint_name, t2.table_name as "referencing table",
> t2.column_name AS "referencing column",
> t3.table_name AS "referenced table",
> t3.column_name AS "referenced column"
> from information_schema.referential_constraints t1
> INNER JOIN information_schema.constraint_column_usage t2 ON
> t1.constraint_name = t2.constraint_name
> INNER JOIN information_schema.constraint_column_usage t3 ON
> t1.unique_constraint_name = t3.constraint_name
>
> You'll have to read the output carefully to see where composite keys are used.
>
> --
>
>
>
> "Rich" wrote:
>
> > Hello,
> >
> > I am examining the relationships, indexes, constraints of tables in a DB
> > that I am supporting. There is no documentation, so I want to get a listing
> > of the relationships rather than just looking at the database diagram and
> > examining each table individually. I looked at
> > information_schema.table_constraints which gave me a listing of all the
> > contraints. But is there a way to get a listing of the relationships?