Hi I am just getting into retrieving schema info from a SQL Sever 2000 using the GetSchema method on the SQLConnection. My problem is that although I can retrieve the fact that a particular table has a foreign Key of a particular name I can not find away to retrieve the name of the foreign table. Can someone guide me in the right direction.
Thanks in anticipation that there is a solution:-)
Unfortunately, none of the GetSchema schema collections provided by SqlClient will provide this information. However, I do see that the sys.foreign_keys catalog view will give you both the parent object of the constraint, as well as the referenced object. You can join that with sys.objects to get the name of the referenced object. You would just have to execute the schema queries directly instead of SqlClient doing it for you, but this would be one way to get the information. The catalog views are new in SQL 2005, but you should be able to get the same information out of the system tables in SQL 2000 (table names are sysforeignkeys and sysobjects).
SQL 2005 also provides the catalog view sys.foreign_key_columns, which provides similar data to what I describe above, but it's simplified. However, I don't see a mapping of this catalog view to a system table in SQL2K.
Thanks,
Sarah
Please Mark as Answer if this answers your question, or Unmark as Answer if it is marked and you feel it is not answered.
