?
Solved

Foreign Keys

Posted on 1999-01-29
4
Medium Priority
?
2,182 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 100 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
We are witnesses that everyone is saying that our children shouldn't "play" with a technology because it is dangerous. This article is going to prove that they are wrong.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

762 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