Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
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??
Posted 22-Apr-13 8:15am
Comments
Sergey Alexandrovich Kryukov at 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 at 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 at 22-Apr-13 15:39pm
   
I mean you could check it up as a part of investigation of your problem...
—SA
Aner Bautista at 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 at 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
jfabus09 at 22-Apr-13 15:46pm
   
Well you could say that I went with that final resort as I have been stuck on this issue for far too long, but I appreciate any interest/assistance anyway and I'll keep an eye out here because I do understand that his was quite the odd issue
Aner Bautista at 3-May-13 13:50pm
   
Ok, I could not recreate this issue, I was using SQL Server 2005 SP3 and SQL Server 2008 RTM and SP2.
 
I Disabled the constraint, then I deleted the data and then dropped the table.. the constraints were dropped when I dropped the table.
 
You solved the problem? What did you do?
jfabus09 at 13-May-13 16:50pm
   
Sorry for not responding. I just did a backup after disconnecting the server from the application it was connected to. I didn't want to but that was my last resort.

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



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 22 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100