?
Solved

Foreign Keys

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
There's never been a better time to become a computer scientist. Employment growth in the field is expected to reach 22% overall by 2020, and if you want to get in on the action, it’s a good idea to think about at least minoring in computer science …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

621 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