Solved

Foreign Keys

Posted on 1999-01-29
4
2,174 Views
Last Modified: 2009-12-16
I'm using Delphi and Sybase SQL and need to find out, the easiest way to identify whether a field is setup has a foreign key when looping thru all the fields of a given table....I don't see anything within Delphi that will allow me to do this...I've read info regarding Sys. tables but can't seem to get access to them...Is this the right way, if so how do I get to them...Better yet what is the best way to do this??? Thank you very much
0
Comment
Question by:GGriffith
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
ajith_29 earned 50 total points
ID: 1098598
this procedcure from powerbuilder which  lists the tables that reference this table

create procedure sp_fktable
  @@objname  varchar(61) = NULL
as
declare @@objid int

if (@@objname is NULL)
   return (1)

select  @@objid = object_id(@@objname)
select o.name, o.id, o.type, o.uid, user_name(o.uid)
  from   dbo.sysobjects o, dbo.sysreferences r
  where  r.reftabid = @@objid  and
         r.tableid  = o.id


0
 
LVL 2

Expert Comment

by:ajith_29
ID: 1098599
these procuders will do the needful..

this procedcure from powerbuilder which  lists the tables that reference this table

create procedure sp_fktable
  @@objname  varchar(61) = NULL
as
declare @@objid int

if (@@objname is NULL)
   return (1)

select  @@objid = object_id(@@objname)
select o.name, o.id, o.type, o.uid, user_name(o.uid)
  from   dbo.sysobjects o, dbo.sysreferences r
  where  r.reftabid = @@objid  and
         r.tableid  = o.id


/*  sp_pb50foreignkey lists all foreign keys associated with       */
/*  a table whose name is passed as arg1 (required).               */
/*-----------------------------------------------------------------*/
create proc sp_pb50foreignkey
@@objname varchar(92)
as
declare @@objid    int           /* the object id of the fk table       */
declare @@keyname  varchar(30)   /* name of foreign key                 */
declare @@constid  int           /* the constraint id in sysconstraints */
declare @@keycnt   smallint      /* number of columns in pk    */
declare @@stat     int

select  @@objid = object_id(@@objname)
if (@@objid is NULL)
begin
   return (1)
end
select  @@stat = sysstat2
   from  dbo.sysobjects
   where id = @@objid  and
         (sysstat2 & 2) = 2
if (@@stat is NULL)
begin
   return (1)
end
/*  Now I know this table has one or more foreign keys.  */
select  o1.name, r.keycnt, o2.name, user_name(o2.uid),
        r.fokey1,  r.fokey2,  r.fokey3,  r.fokey4,  r.fokey5,  r.fokey6,
        r.fokey7,  r.fokey8,  r.fokey9,  r.fokey10, r.fokey11, r.fokey12,
        r.fokey13, r.fokey14, r.fokey15, r.fokey16
  from  dbo.sysconstraints c,    dbo.sysobjects o1,
        dbo.sysreferences r,     dbo.sysobjects o2
  where c.tableid  =  @@objid    and
        c.status   =  64         and
        c.constrid =  o1.id      and
        o1.type    =  'RI'       and
        c.constrid =  r.constrid and
        r.reftabid =  o2.id



0
 

Author Comment

by:GGriffith
ID: 1098600
Anyway I can get the example using Delphi....I still don't seem to be getting the foreign key field names....Thanks
0
 

Expert Comment

by:Fernando_Diaz
ID: 25178947
I have solved with this:
SELECT fk.nulls, fk.role, fk.remarks, t1.table_name AS 'tabla_ajena', SYSCOLUMNPK.column_name AS 'ColumnaPrincipal', t2.table_name AS 'tabla_principal', SYSCOLUMNFK.column_name AS 'ColumnaAjena'
FROM SYS.sysforeignkey fk, SYS.SYSCOLUMN SYSCOLUMNFK, SYS.SYSCOLUMN SYSCOLUMNPK, SYS.SYSFKCOL SYSFKCOL, SYS.systable t1, SYS.systable t2
WHERE fk.foreign_table_id = t1.table_id AND fk.primary_table_id = t2.table_id AND fk.foreign_table_id = SYSFKCOL.foreign_table_id AND fk.foreign_key_id = SYSFKCOL.foreign_key_id AND SYSFKCOL.foreign_column_id = SYSCOLUMNFK.column_id AND SYSFKCOL.primary_column_id = SYSCOLUMNPK.column_id AND fk.primary_table_id = SYSCOLUMNPK.table_id AND fk.foreign_table_id = SYSCOLUMNFK.table_id
ORDER BY t1.table_name
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question