I am looking for (preferably) an SQL statement that selects the table/and column names for any table with a foreign key to a given table in Sybase. I think it should be somehow possible with the sys...
tables but being completely new to sybase, I cannot make head nor toe of it. So any help into the right direction is highly appreciated.
Edit: For completness' sake: SELECT @@VERSION
returns
'Adaptive Server Enterprise/15.0.3/EBF 17156 ESD#3/P/Sun_svr4/OS 5.8/ase1503/2726/64-bit/FBO/Fri Feb 5 05:26:23 2010'
Edit 2
Thanks a lot for your suggestions, especially Andrew Bickerton's comment. That allowed me to construct a rudimentary SQL select statement for a starting point to go further.
In case someone else is interested in it, here it is:
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
tab.name = 'tq84_f' and
fko.type = 'RI'
Best Answer
Been a while since I've worked in sybase but (from memory) the following sql should point you in the right direction:
you can also check out the sybase books online (system tables)