Solved

Foreign Keys

Posted on 1999-01-29
4
2,175 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Configuring Remote Assistance for use with SCCM
Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 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