Get FK:PK relationships from a schema and from a result set

[Guest]

Hi;

Please, please, please tell me there is a way to do this.

For a foreign key, say Orders.CustomerId I need to know that it maps to
Customers.CustomerId. I need to be able to get this metadata both from when I
first read all the tables & views from a database and more critically, when I
do a select and get a result set back - get this info for each FK column in
the returned table.

How can I do this. Preferably vendor independently but if I have to, we can
write specific code for Sql Server, Oracle, DB2, and MySql.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Turkbear]

Hi;

Please, please, please tell me there is a way to do this.

For a foreign key, say Orders.CustomerId I need to know that it maps to
Customers.CustomerId. I need to be able to get this metadata both from when I
first read all the tables & views from a database and more critically, when I
do a select and get a result set back - get this info for each FK column in
the returned table.

How can I do this. Preferably vendor independently but if I have to, we can
write specific code for Sql Server, Oracle, DB2, and MySql.

For Oracle , at least, examine the contents of the ALL_CONSTRAINTS table - there may be a way to create a sql statement
that would return the FK's and which PK's they refer to..

 

[WenYuan Wang [MSFT]]

Hi Dave,

As far as I know, ADO.net doesn't provide such a way to Get FKK
relationships from a column. You may check system tables in SQL database.
(Such as sys.foreign_key_columns table.) But, I'm afraid there's a lot of
work to do here.....

For example: in SQL 2005
-- get constraint name
select Constraint_Name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where column_name='c2' and Table_Name='table_2'

-- get oject_id from constraintName
select object_id from sys.objects where name='ConstraintName'

-- get parent table, parent column, referenced table and column id.
select
parent_object_id,parent_column_id,referenced_object_id,referenced_column_id
from sys.foreign_key_columns where constraint_object_id='objectid'

-- get table and column name
select name from sys.objects where object_id='referenced_object_id'
select Column_Name from INFORMATION_SCHEMA.COLUMNS where table_name='Name'
and Ordinal_position='ReferencedColumnId'

Hope this helps. Please let me know if you still have anything unclear.
Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

This works. It's messy but it works. thank you.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Sudhakar]

In SQL server INFORMATION_SCHEMA views are there. That's the solution to
your problem.

With Regards
sudhakar

 

[Guest]

Dave Got one better for u
putting it all together for ya

works on sql server 2000 and 2005

SELECT OBJECT_NAME(f.constid) AS 'ForeignKey', OBJECT_NAME(f.fkeyid) AS
'FKTable', c1.[name] AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable',
c2.[name] AS 'PKColumnName'
FROM sysforeignkeys f
INNER JOIN syscolumns c1
ON f.fkeyid = c1.[id]
AND f.fkey = c1.colid
INNER JOIN syscolumns c2
ON f.rkeyid = c2.[id]
AND f.rkey = c2.colid
ORDER BY OBJECT_NAME(f.rkeyid)