Search code examples
sql-serverreferential-integrity

How to find a referential integrity constraint via sp_fkeys?

I am trying to drop a table:

drop table if exists bronze.LawAggregatedPipelineSummary;
go

But there is an FK constraint on it:

Could not drop object 'bronze.LawAggregatedPipelineSummary' because it is referenced by a FOREIGN KEY constraint.

I have tried to identify the constraint but have not been able to find it via sp_fkeys. Each of the following four invocations on sp_fkeys was attempted: but turned up empty:

exec sp_fkeys 'bronze.LawAggregatedPipelineSummary'
exec sp_fkeys 'LawAggregatedPipelineSummary'
exec sp_fkeys @pktable_name = 'bronze.LawAggregatedPipelineSummary'
exec sp_fkeys @pktable_name = 'LawAggregatedPipelineSummary'
Solution

  • Use the proper schema views, eg

    select name ForeignKeyName, object_schema_name(parent_object_id) SchemaName, object_name(parent_object_id) TableName
from sys.foreign_keys
where referenced_object_id = object_id('bronze.LawAggregatedPipelineSummary')