Foreign Keys exist from dropped tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Foreign Keys exist from dropped tables?

Author

Topic

jfabs09
Starting Member

3 Posts

Posted - 2013-04-22 : 10:38:09

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 - 2013-04-22 : 14:52:44

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

jfabs09
Starting Member

3 Posts

Posted - 2013-04-22 : 15:48:27

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources