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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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    "Foreign key name",    "Referenced table name", || ' -> ' || "Reference 1", || ' -> ' || "Reference 2", || ' -> ' || "Reference 3", || ' -> ' || "Reference 4"
  sysobjects      tab                                       join
  sysconstraints  con on        = con.tableid        join
  sysobjects      fko on con.constrid  =             join
  sysreferences   ref on con.constrid  = ref.constrid       join
  sysobjects      par on        = ref.reftabid  left join
  ---- 1. Column
  syscolumns      fk1 on ref.fokey1    = fk1.colid and
                         ref.tableid   =        left join
  syscolumns      pk1 on ref.refkey1   = pk1.colid and
                         ref.reftabid  =        left join
  ---- 2. Column
  syscolumns      fk2 on ref.fokey2    = fk2.colid and
                         ref.tableid   =        left join
  syscolumns      pk2 on ref.refkey2   = pk2.colid and
                         ref.reftabid  =        left join
  ---- 3. Column
  syscolumns      fk3 on ref.fokey3    = fk3.colid and
                         ref.tableid   =        left join
  syscolumns      pk3 on ref.refkey3   = pk3.colid and
                         ref.reftabid  =        left join
  ---- 4. Column
  syscolumns      fk4 on ref.fokey4    = fk4.colid and
                         ref.tableid   =        left join
  syscolumns      pk4 on ref.refkey4   = pk4.colid and
                         ref.reftabid  =        -- Et cetera...
  tab.type = 'U'      and = '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
up vote 4 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 = 
where = '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(,
    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(, key1),sign(keycnt),30))
    +rtrim(substring(', '+col_name(, key2),sign(keycnt-1),30))
    +rtrim(substring(', '+col_name(, key3),sign(keycnt-2),30))
    +rtrim(substring(', '+col_name(, key4),sign(keycnt-3),30))
    +rtrim(substring(', '+col_name(, key5),sign(keycnt-4),30))
    +rtrim(substring(', '+col_name(, key6),sign(keycnt-5),30))
    +rtrim(substring(', '+col_name(, key7),sign(keycnt-6),30))
    +rtrim(substring(', '+col_name(, 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

If you are looking just for referenced tables name, this code will help

FROM sysobjects o
WHERE EXISTS (  SELECT 1 FROM sysconstraints C
    JOIN sysreferences R ON R.constrid=C.constrid
    WHERE EXISTS ( SELECT 1 FROM sysobjects o2
        AND ='tableName' )  
share|improve this answer

Your Answer


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.