Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
share|improve this question
    
Cheers for adding on your solved sql! and glad to help point you in the right direction :-) –  Andrew Bickerton May 24 '11 at 10:12

3 Answers 3

up vote 2 down vote accepted

Been a while since I've worked in sybase but (from memory) the following sql should point you in the right direction:

select *
from sysobjects so inner join syscolumns sc on so.id = sc.id 
where sc.name = 'field name'

you can also check out the sybase books online (system tables)

share|improve this answer
    
As René needs the tables/columns for foreign keys, won't he need sysconstraint in there too? –  Jack Douglas May 20 '11 at 12:03
    
No I don't think it's sysconstraint, I think it's sysreferences, then looking fokey1 to fokey16 –  Andrew Bickerton May 20 '11 at 12:33
    
I see :) –  Jack Douglas May 20 '11 at 12:46
    
@JackPDouglas It has been a few years since I've properly played in sybase, couldn't remember which system tables to begin with :-) –  Andrew Bickerton May 20 '11 at 12:51

Note that the approach shown above (using sysconstraints and sysreferences) will only work if you have referential constraint. If you have just a foreign key defined but without constraint, you will not see it there. To get all foreign keys related to a given table, you can use syskeys:

select  rtrim(object_name(k.id)),
    rtrim(substring(col_name(k.depid, depkey1),sign(keycnt),30))
    +rtrim(substring(', '+col_name(k.depid, depkey2),sign(keycnt-1),30))
    +rtrim(substring(', '+col_name(k.depid, depkey3),sign(keycnt-2),30))
    +rtrim(substring(', '+col_name(k.depid, depkey4),sign(keycnt-3),30))
    +rtrim(substring(', '+col_name(k.depid, depkey5),sign(keycnt-4),30))
    +rtrim(substring(', '+col_name(k.depid, depkey6),sign(keycnt-5),30))
    +rtrim(substring(', '+col_name(k.depid, depkey7),sign(keycnt-6),30))
    +rtrim(substring(', '+col_name(k.depid, depkey8),sign(keycnt-7),30)),
    rtrim(substring(col_name(k.id, key1),sign(keycnt),30))
    +rtrim(substring(', '+col_name(k.id, key2),sign(keycnt-1),30))
    +rtrim(substring(', '+col_name(k.id, key3),sign(keycnt-2),30))
    +rtrim(substring(', '+col_name(k.id, key4),sign(keycnt-3),30))
    +rtrim(substring(', '+col_name(k.id, key5),sign(keycnt-4),30))
    +rtrim(substring(', '+col_name(k.id, key6),sign(keycnt-5),30))
    +rtrim(substring(', '+col_name(k.id, key7),sign(keycnt-6),30))
    +rtrim(substring(', '+col_name(k.id, key8),sign(keycnt-7),30))
from    syskeys k
where   k.type = 2
and     k.depid = object_id('report')

(replace report by the name of your table).

The first column return is the name of the table with a foreign key pointing to this table. The second column is the list of columns in this table. And the third columns is the list column names in the referencing table.

So if you have a table people_report with a column rep_key which is a foreign key to report.key, you'd get something like this:

people_report        key       rep_key
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.