Re: help with query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Why not use information_schema?
 
select prk.table_name AS PARENT_TABLE, prk.constraint_name AS PK, 
         tc.table_name AS CHILD_TABLE, refc.constraint_name AS FK
from information_schema.table_constraints prk,  
         information_schema.referential_constraints refc,
         information_schema.table_constraints tc
where prk.table_catalog = refc.unique_constraint_catalog
     and prk.constraint_type = 'PRIMARY KEY'
     and prk.constraint_name = refc.unique_constraint_name
     and tc.constraint_name = refc.constraint_name
     and tc.constraint_type = 'FOREIGN KEY'
     and tc.table_catalog = refc.constraint_catalog
order by prk.table_name , tc.table_name;
 
Igor


From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Chris Hoover
Sent: Thursday, August 16, 2007 11:19 AM
To: pgsql-admin@xxxxxxxxxxxxxx Admin
Subject: help with query

I need a little bit of help.  I need to use sql to pull any tables that have the a foreign key referencing a given tables primary key.

So far I have come up with the listed query.  It works great for single column primary keys, but if a table has a multi column primary key, it is returning to many rows.  How can I get it to work for tables with multi-column primary keys as well as single column primary keys?

Thanks,

Chris

select    a.relname as table_name,
    c.attname as column_name,
    w.typname as domain_name
from    pg_class a,
    pg_constraint b,
    pg_attribute c,
    pg_type w
where    a.oid = b.conrelid
and    c.atttypid = w.oid
and    c.attnum = any (b.conkey)
and    a.oid = c.attrelid
and    b.contype = 'f'
and    a.relkind = 'r'
and    c.attname in (    select    z.attname
            from    pg_class x,
                pg_constraint y,
                pg_attribute z
            where    x.oid = y.conrelid
            and    z.attnum = any (y.conkey)
            and    x.oid = z.attrelid
            and    y.contype = 'p'
            and    x.relname = 'table' ) ;

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux