0

So here's an interesting one for you... I am working on copying one entire database (db1) and structure over from one database to another (db2), and before doing so I decided to try and drop all tables from the db2. I did the usual sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAING ALL' and then sp_msforeachtable 'DROP TABLE ?' and much to my dismay, it deleted all save for maybe 6 tables. These tables seemed to still have foreign key references to them. I did a search and found this SQL DROP TABLE foreign key constraint which showed me how to find and then try to delete those foreign key references.

This is the interesting part: upon attempting to delete them using that information, I have been told that ssms cannot find the object because it does not exist or I do not have permission. The foreign key reference is coming from a table that I have previously deleted.

How is that possible? And how on earth do I progress from here?

Community
  • 1
  • 1
Jfabs
  • 543
  • 4
  • 9
  • 23
  • 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 – Jfabs Apr 22 '13 at 18:52
  • Consider this issue closed! I just went ahead and took the easy route of deleting db1 and restoring a copy of db2 in place of db1. – Jfabs Apr 22 '13 at 19:46

1 Answers1

0

I don't know what you mean by them in "upon attempting to delete them". If you were trying to delete the foreign key from the system tables, that would definitely be a mistake.

My guess is you can just drop those last 6 tables now.

Suppose we have two tables A and B

create table A (a int) 
create table B(b int, foreign key (b) references (A.a))

and we try to drop the tables. drop table A will fail, because B references it with a declared foreign key. But we can freely drop table B, because A doesn't care if it's no longer being referenced.

So after the first pass, one DROP failed and one succeeded, leaving one table, now with no FK references. Try again and, voila!, drop table A now works.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31
  • The _them_ refers to the foreign keys that were the result of the query that I found in the link I posted up there. 'ALTER TABLE table1 DROP CONSTRAINT FK_whatever1' Obviously these aren't the real names... anyway, I'll try and explain better. That statement up there was the result of the query that I found in the link. The issue here is that FK_whatever1 still exists but table1 does not so now I get the following error upon trying to run that statement: "Cannot find the object table1 because it does not exist or you do not have permission". Table1 is deleted, how does that FK exist? – Jfabs Apr 22 '13 at 13:19