--Msg 3726, Level 16, State 1, Line 11
--Could not drop object 'xxxxx' because it is referenced by a FOREIGN KEY constraint.

EXEC sp_fkeys 'xxxxx'


IF (OBJECT_ID('[FK_xxxxx]', 'F') IS NOT NULL)
BEGIN
    ALTER TABLE dbo.xxxxx DROP CONSTRAINT [FK_xxxxx]
END

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table',
OBJECT_NAME(FK.parent_object_id) 'Referring Table',
FK.name 'Foreign Key',
COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
INNER JOIN sys.foreign_key_columns AS FKC
ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'tbTableName'

Resolution:

  1. Find Constraints
  2. Drop Constraints
  3. TRUNCATE Table Data
  4. Recreate Constraints

Sources:

Last modified: December 21, 2022

Author

Comments

Write a Reply or Comment