So I'm having the strangest issue here... I'm attempting to completely clean out db1 so I can copy over the structure and data from db2 into db1 and in the process of it I have ran a few statements:
sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
sp_MSForEachTable 'DROP TABLE ? ';
After running those, I still had a few tables left due to errors:
Could not drop object 'table_blah' because it is referenced by a foreign key constraint
Interesting, I thought I disabled all of those...
Upon further investigation I found this command which would generate the alter table statements for whatever foreign keys existed for my specified table:
SELECT
'ALTER TABLE ' + OBJECT_NAME(parent_object_id) +
' DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('table_blah')
It generated two Alter table statements for me which I tried to run:
ALTER TABLE table_blah DROP CONSTRAINT FK_table_other_contact
And I received this error:
Cannot find the object "table_other" because it does not exist or you do not have permission
Ummm.... That's because table_other was deleted with the majority of the rest of db1 when I ran the first two statements. How can a foreign key constraint exist from a table which no longer exists? More importantly, how can I drop that foreign key constraint when the table it belongs to no longer exists??