I need to get the foreign keys of each table in my database and the tables and primary keys related to each one.
I used to get the foreign keys using the OleDbConnection GetSchema method, but SqlConnection GetSchema does not get the related tables and primary keys of each foreign key.
What I need to do is: given a primary key value, find out if exist any related row in the others tables, so I can physical delete or delete in a logical way (marking a deleted column) denpending if the row has child rows in others tables.
If someone can help me I'll be thankful.
How I get foreign keys info from a SQL 2005 Database?
SheRa
You can check-out the 'sys.foreign_keys' system view; I think it has the information you're looking for (you may need to do a couple of joins in order to walk from your table id and column id (for the key) all the way to the table/column in the other end of the relationship).
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation
Dave9999
Thank you very much Pablo Castrado, but that wasn’t very useful to me. Anyway, your post gave me a clue and I found how to do it by using the ‘sys.foreign_key_columns’ system view.
Here is my way to do what I was looking for:
SELECT tablesA.name AS ParentTable, columnsA.name AS ParentPK, tablesB.name AS ReferencedTable, columnsB.name AS ReferencedFK
FROM sys.foreign_key_columns
INNER JOIN sys.columns AS columnsA
ON sys.foreign_key_columns.parent_object_id = columnsA.object_id
AND sys.foreign_key_columns.parent_column_id = columnsA.column_id
INNER JOIN sys.columns AS columnsB
ON sys.foreign_key_columns.referenced_object_id = columnsB.object_id
AND sys.foreign_key_columns.referenced_column_id = columnsB.column_id
INNER JOIN sys.tables AS tablesA
ON sys.foreign_key_columns.parent_object_id = tablesA.object_id
INNER JOIN sys.tables AS tablesB
ON sys.foreign_key_columns.referenced_object_id = tablesB.object_id
I hope this will be useful to someone else.