Problem with sp_foreignkeys
Hi,
I am working with Sql Server 2005 SP2.
I have a Linked Server (to an Access database) from which I want to get the foreign keys of its tables.
To do that I use the store procedure sp_foreignkeys.
The problem is that in a table that I have two foreign keys to the same table, which are multicolumn keys, I can’t identify the keys with the result of the stored procedure because it returns first the records with KEY_SEQ=1 and then the records with KEY_SEQ=2, and in this records is impossible to solve to which relations they belong because there is not field identifying the relation.
For example:
TableA
IdCol1
IdCol2
TableB
FK1TableA_IdCol1
FK1TableA_IdCol2
FK2TableA_IdCol1
FK2ItableA_IdCol2
The relations are:
Foreign KeyPrimary Key
TableBTableA
Relation1:
FK1TableA_IdCol1->IdCol1
FK1TableA_IdCol2->IdCol2
Relation2:
FK2TableA_IdCol1->IdCol1
FK2TableA_IdCol2->IdCol2
When i get the records from the stored procedure, it gives the following result:
PKTABLE_NAMEPKCOLUMN_NAMEFKTABLE_NAMEFKCOLUMN_NAMEKEY_SEQ
TableA IdCol1TableBFK1TableA_IdCol11
TableA IdCol1TableBFK2TableA_IdCol11
TableA IdCol2TableBFK1TableA_IdCol22
TableA IdCol2TableBFK2TableA_IdCol22
The other fields are returned with NULL value.
When I try to identify the keys, I can’t differentiate to which key the third record belongs, becouse there is no field identifying the relation, so I can’t know if it belongs to Relation1 or to Relation2. The same with the fourth record.
Am I missing something that allow me to identify the relation?
Is it a bug in the store procedure?
Is there any other way to get the relations?
Thanks in advance!
Pablo
