As part of this months  T-SQL Tuesday on “relationships” I thought I’d post about a handy feature in SQL Server Management Studio.

As the title of the article implies I’m going to discuss how database diagrams in SSMS can be handy to visually see the relationship’s in a database. But I think I could have also named the article “T-SQL Tuesday: Spot Relationship’s with Database Diagrams, or the Lack of Them”.

Out of the box and with a deployed database you may get a warning about trying to use a database diagram and that they’re not accessible. Quite often this is because the database does not have a valid owner. As always Books Online is our hero, you can use sp_changedbowner to change the owner. While you’re at BoL read this too, it explains database diagram ownership.

With that out of the way we can move on to the point of this article. Relationships!

SSMS’s database diagram’s when first made will ask you what tables you want to add to the diagram. Go ahead and add them all, you’ll end up with a nicely formatted diagram showing you your tables all lined up nicely with each other. Your results will vary but will likely be a mixture of the tables neatly arrayed with lines drawn between them … or … tables neatly arrayed one after another after another with nary a line to be seen. As they say, “Hope for the best but prepare for the worst.”

If you’re lucky and you have lines between tables rest assured that at least some tables have foreign keys between tables. But don’t rest too long because just because there are lines doesn’t mean there are enough of them (or there might be too many). It’s now time to look at each table neatly arrayed and familiarize yourself with the schema. I find that visually it is quite easy this way to spot likely candidates for FK’s. You’re also likely to spot other things, such as PK candidates, better normalization strategies, moments of sheer database schema genius, or pure database depravity. If you find the latter then…

Database diagrams are also great for spotting everything that’s missing as well. Orphaned tables are quite obvious sitting all by themselves, and again I find it visually easy to quickly tell if that’s the way it should be or shouldn’t be. If you find everything is missing you can start adding FK’s right there in your diagram. Simply right click on a table and you’re given quite a few options to start updating your schema. (See the warning below!)

Anyhow, I hope everyone gives SSMS’s diagrams a try. It does not replace sitting down and verifying your database’s relationship’s one by one. But it does make a great aid in doing so.

A couple of additional points to keep in mind when looking using database diagrams:

  • Remember there are times you might not want a foreign key. FK’s do imply overhead and in a performance consideration there may be a valid reason to not have them.
  • The diagram resizing isn’t really all that great, 75% means that the text is 25% less legible. Not very handy unless you want to get a REALLY high level view.
  • Go ahead and drag things around. I do all the time.
  • Try printing these out! Print to a PDF with something like CutePDF or if you’re lucky like I am with a large format printer print it out on a 3′ x 3′ piece of paper and start drawing all over it.

And finally a word of warning!

The changes you make in a database diagram can be committed! So don’t go trying to drop database objects in a production environment. I think it’s best practice to just not use them in production. Unless you’ve practiced your disaster recovery recently and feel quite confident in it.