How to find tables in a database with Foreign key constraints
Many a times we end up with situations where certain tables in a database have to be trunctated. Here starts the aginomy of finding related tables referenced through foreign keys.
The all famous message—
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table ‘Item’ because it is being referenced by a FOREIGN KEY constraint.
Does not really help!!! (Please tell me the tables and columns that are being referenced.)
A typical situation when you are working in a shared environment or trying to reverse engineer an existing database structure. Well no more digging the well…… look here this will help.
Run this query on your selected database and see the wonder that it works.
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
Happy hunting the foreign key….. 
An addon to the article…. realised that MS SQL will throw errors inspite of referenced tables not containing data. So to be sure just run the “Delete from tablename” query to check the genuinity of the error.
Zaheed
June 10, 2010 at 11:34 pm