SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Foreign Keys exist from dropped tables?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jfabs09
Starting Member

3 Posts

Posted - 04/22/2013 :  10:38:09  Show Profile  Reply with Quote
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??

jfabs09
Starting Member

3 Posts

Posted - 04/22/2013 :  14:52:44  Show Profile  Reply with Quote
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
Go to Top of Page

jfabs09
Starting Member

3 Posts

Posted - 04/22/2013 :  15:48:27  Show Profile  Reply with Quote
Consider my issue closed here because I ended up taking the easier route of simply deleting db1 and replacing it with a restored copy of db2
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000