Blog Pages

SAP ASE (Sybase) - Foreign key list in the database

USE [DATABASENAME]
GO

-- Quick script
select * from sysobjects where type = 'RI'


-- Detailed script
select 
  tab.name    "table name",
  fko.name    "Foreign key name",
  par.name    "Referenced table name",
--  fk1.name  "FK column name",
--  pk1.name  "Ref column name",
  *
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
--  ---- 1. Column
--  left join syscolumns  fk1 on ref.fokey1 = fk1.colid and ref.tableid = fk1.id
--  left joinsyscolumns   pk1 on ref.refkey1 = pk1.colid and ref.reftabid = pk1.id
-- etc. to more columns
where tab.type = 'U'
and   fko.type = 'RI'
--and   tab.name = 'TABLENAME'

No comments:

Post a Comment