Sql-server – How to resolve issue with deleted table in SQL Server Database Diagrams

database-designsql-server-2008

I am working on a database design using the SQL Server Database Diagram tool. I created a table with the name dbo.Example and before defining any columns, attempted to save.

Got an error that SQL can't save a table without any columns and as I wanted to save the other work I had done before completing this table, I deleted it and saved successfully.

I then make many more changes and save the diagram. All is good.

UNTIL I am ready to correctly define my dbo.Example table and get the 

'Table 'dbo.Example' already exists' error.

The table is not among the tables returned from

select name from sys.tables;

and does not appear in the Database->Tables GUI view.

I can't find where I need to clean up and drop this table.

Where does SQL Server keep the metadata about the database diagrams?

Best Answer

This is a bug in Management Studio.

I was able to reproduce it three ways, on both 2008 R2 and 2012 tools:

  1. Create a new diagram; create a new empty table (any name); delete the table; in the same diagram, try to create a new table with the same name.
  2. Create a new diagram; create a new empty table; delete the table; create a new diagram in the same database, and while the first one is still open, create a new table with the same name.
  3. Create a new diagram; create a new empty table; delete the table; create a new diagram in the same database; close the first diagram; in the second diagram, create a new table with the same name.

From this behaviour, I'm guessing it keeps a list of tables per database outside the context of a single database diagram, and when an empty table is deleted, the reference isn't being cleaned up properly. I believe the fix is to close all diagrams for that database, and re-open. I think it's lucky you were able to save the changes you made with no errors!

I would suggest reporting this on Connect if you feel it's really important. IMO, it seems unlikely it will be fixed because it's so minor, but you never know.

Related Question