Click here to Skip to main content
15,921,113 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

SQL
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:
SQL
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:
SQL
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??
Posted
Comments
Sergey Alexandrovich Kryukov 22-Apr-13 13:33pm    
Basically, you are right, but this is not a complete picture. You could drop some constraints and forget about others. How could we check it up based on your information?
As to the permission to access some table, it's easy to check up. And if the table is already removed, is also easy to check up.
—SA
jfabus09 22-Apr-13 13:43pm    
I guess I'm not sure what you mean by check up? Do you literally mean to check it, as in the status of it? If so, I know for a fact that the table that the foreign key belonged to no longer exists (well according to the error message and the fact that I don't see it in the database it no longer exists). This has become quite a frustrating ordeal, I need to delete these tables but I cannot because these constraints exist, but I can't delete the constraints because the tables they belong to no longer exist either...
Sergey Alexandrovich Kryukov 22-Apr-13 15:39pm    
I mean you could check it up as a part of investigation of your problem...
—SA
Aner Bautista 22-Apr-13 14:42pm    
Wow!! that's a weird issue. SQL Server is supposed to prevent you to drop tables even if the Foreign key constraints are disabled.

Do you have a backup where these tables exists? if you recreate the tables maybe you would be able to drop these constraints and then drop the tables (again).

I am trying to recreate your scenario on SQL Server 2005 and SQL Server 2008 R2 to give you a satisfactory answer but... What is your SQL Server version?
jfabus09 22-Apr-13 14:52pm    
Well I am using SQL Server Management Studio but it would be SQL Server 2008 R2.

I do have a backup but I am leaving that as a last resort as it would require disconnecting the Server from the application that utilizes it. This issue is part of an endeavor that involves clearing out this Database and replacing it with the structure and data from another.

Another minor update, upon querying Sys.Objects for objects LIKE table_blah, I have received a list which has several default constraint objects, foreign key constraint objects, a primary key constraint object, and still the table itself despite no longer being visible in the tree structure and despite my not being able to view it using a select statement

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900