Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
are you sure you use "Delete Tables From Database" option and not "Remove From Diagram" ? and that you checking against right Database ? –  Anup Shah Oct 16 '13 at 17:19
1  
just for a note the diagrams are saved in SELECT * FROM sysdiagrams –  Anup Shah Oct 16 '13 at 17:21
1  
Everything about the diagram is encapsulated in a binary field that only Management Studio knows how to serialize (see this) -- have you tried closing and reopening the diagram? –  Jon Seigel Oct 16 '13 at 17:27
    
@JonSeigel - thanks, this resolved my issue! My question is possibly a duplicate of your link, despite the difference in details. I will edit so this can be answer... –  Thronk Oct 16 '13 at 18:03
    
I am working on a database design using the SQL Server Database Diagram tool. Maybe stop right there. I think the diagram tool is much better at reverse engineering than designing. YMMV. –  Aaron Bertrand Oct 16 '13 at 22:36

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.