7

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'
1
  • Cheers for adding on your solved sql! and glad to help point you in the right direction :-) May 24, 2011 at 10:12

4 Answers 4

4

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)

4
  • As René needs the tables/columns for foreign keys, won't he need sysconstraint in there too? May 20, 2011 at 12:03
  • No I don't think it's sysconstraint, I think it's sysreferences, then looking fokey1 to fokey16 May 20, 2011 at 12:33
  • I see :) May 20, 2011 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 :-) May 20, 2011 at 12:51
2

Another aproach is "sp_depends"

Sybase URL: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs68.htm

2

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
1

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

SELECT o.name
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
        WHERE o2.id=C.tableid
        AND o2.name ='tableName' )  
AND R.reftabid=o.id )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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