SQL Server 2005 Database Diagrams

Trying to Make Sense of the Mess

So database diagrams are supposed to help you ensure that proper indexing and referential integrity is enforced where it should be in your database. Oh yeah, and it should help to visualize the relationships in your database.

On the first account, it does, and I definately see its usefulness when implemented successfully. On the second, it only helps visualize the most trivial of databases. Anything beyond 10 related tables and you can forget about *any* single useful view of your database; databases are not meant to be visualized from a 2D surface.

However, there are some caveats to database diagrams.

Copying the Diagram

For one, copying the diagram to another database is, er um, not as straight forward as one would expect.

If you want to copy the *visual* depiction as a graphic, say for print-out, all you have to do is open the diagram up, wait for it to load completely, then right-click the surface and select the 'Copy to Clipboard' option. But again, this is really only useful for small database diagrams, unless you like the idea of single-handedly killing the rainforest with all those reams of paper you intend to print the diagram out on...just so that you have a nice, pretty picture up on your wall of the chaos that is your enterprise database schema.

If you want to transfer the diagram to another database though, you have some options:

• Restore the database from a backup of the source database where the diagram is in-tact.
  
  CON: This doesn't take into account differences in data between the source and destination databases.
  
• Script the diagram out to T-SQL from within the source database, then execute it on the destination database.
  
  PRO: While this option isn't available from Management Studio as you would expect it to be, considering that you can do this with just about every other database artifact, there's already a solution for how to do this on codeproject.
  
  CON: This method requires you to add artifacts to your source database, which not all of us have the luxury of doing (e.g. a closed vendor product, production audit processes, etc.)

I ended up using the second approach, since export of a database diagram into the SVN repository is explainable in a SQL audit. However, I wish Microsoft had implemented this in Management Studio to begin with. It was probably something that didn't make the final cut of the tool set before RTM.

A real shame it didn't though.