How To Find The Tables And Columns Referenced By A Foreign Key Using T-SQL



TODO:

Have you ever wanted to find the table and column names referenced in a Foreign Key Constraint using T-SQL?

 

SOLUTION:

SELECT 
   OBJECT_NAME(fk.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS fk
INNER JOIN 
   sys.foreign_key_columns AS fkc 
      ON fk.OBJECT_ID = fkc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fkc.referenced_object_id
WHERE 
   OBJECT_NAME (fk.referenced_object_id) = 'YourTableGoesHere'

 

NOTES:

There are no notes on this topic.

How To Delete Rows From Tow Tables When They Have Foreign Keys To Each Other



TODO:

You have table A, and Table B.  Table A has a foreign key to table B, and table B has a foreign key to table A.  You need to delete data from table A, but cannot because of the foreign key on Table B.

 

SOLUTION:

ALTER TABLE employee NOCHECK CONSTRAINT ALL
delete employee where id=100
ALTER TABLE employee WITH CHECK CHECK CONSTRAINT ALL


NOTES:

This will allow you to delete data when there arecircular foreign keys.