GGriffith
asked on
Foreign Keys
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Anyway I can get the example using Delphi....I still don't seem to be getting the foreign key field names....Thanks
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
ORDER BY t1.table_name
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
ORDER BY t1.table_name
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