SyBase: List Constraints to Foreign Key

select
fko.name    "Foreign key name",
par.name    "Referenced table name",
fk1.name || ' -> ' || pk1.name "Reference 1",
fk2.name || ' -> ' || pk2.name "Reference 2",
fk3.name || ' -> ' || pk3.name "Reference 3",
fk4.name || ' -> ' || pk4.name "Reference 4"
from
sysobjects      tab                                       join
sysconstraints  con on tab.id        = con.tableid        join
sysobjects      fko on con.constrid  = fko.id             join
sysreferences   ref on con.constrid  = ref.constrid       join
sysobjects      par on par.id        = ref.reftabid  left join
---- 1. Column
syscolumns      fk1 on ref.fokey1    = fk1.colid and
ref.tableid   = fk1.id        left join
syscolumns      pk1 on ref.refkey1   = pk1.colid and
ref.reftabid  = pk1.id        left join
---- 2. Column
syscolumns      fk2 on ref.fokey2    = fk2.colid and
ref.tableid   = fk2.id        left join
syscolumns      pk2 on ref.refkey2   = pk2.colid and
ref.reftabid  = pk2.id        left join
---- 3. Column
syscolumns      fk3 on ref.fokey3    = fk3.colid and
ref.tableid   = fk3.id        left join
syscolumns      pk3 on ref.refkey3   = pk3.colid and
ref.reftabid  = pk3.id        left join
---- 4. Column
syscolumns      fk4 on ref.fokey4    = fk4.colid and
ref.tableid   = fk4.id        left join
syscolumns      pk4 on ref.refkey4   = pk4.colid and
ref.reftabid  = pk4.id        -- Et cetera...
where
tab.type = 'U'      and
fko.name = 'FOREIGN_KEY_NAME' and
fko.type = 'RI'

no comment ;)

Advertisements
This entry was posted in howtos, technik, work and tagged , . Bookmark the permalink.

One Response to SyBase: List Constraints to Foreign Key

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s