Documenting the System with Database Diagrams

As a database consultant, it is not uncommon for me to walk into a new business and be handed a task of untangling some "spaghetti".

What do I mean by that? Well, when I used the term spaghetti in the context of my IT work, I refer to a database that is such a mess that it’s impossible to work out the relationships. And this is especially true as most of my clients never created foreign keys within the database and so the SQL Server Management Studio (SSMS) Database Diagram designer can’t map it out.

This is where a tool such as Quick Database Diagrams comes in. This online tool (available with a free option), allows you to map out and document the database as you go. This is something that I find really helps me to learn a new system quickly, as I am very visual and seeing the database relationships at a high level really helps.

There are a few of these free tools available (see this link here for a review on some of them, including QuickDBD -  https://chartio.com/learn/databases/7-free-database-diagramming-tools-for-busy-data-folks/ ) … but what I find makes QuickDBD the best one is that I can directly copy & paste the table fields from SQL Server into QuickDBD. 

With some of the other tools mentioned there, I had to do it by copying and pasting each field by field … and since tables can have hundreds of fields (and yes, I’ve seen that plenty of times unfortunately) … copying and pasting it one by one can be extremely time consuming!

Here is how to do it:

(Note: for this demo I am using the Northwind database which can be downloaded from here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases)

Firstly, go to https://app.quickdatabasediagrams.com/  and begin your first diagram.

On the left-hand side there is the schema as a text editor, and the map is on the right-hand side. If you’re experienced with databases (and I assume you are to be reading this article of mine in the first place 😊) then you would understand how the text schema creates the diagram - the capital PK and FK keywords being short for Primary Key and Foreign Key respectively. 

So you can type them in yourself, or …. 

• Open SSMS and go into the database that you would like to map
  
  
• Type the following into a New Query Editor:

USE Northwind

GO

sp_help Employees

 This will produce a result in the bottom tab. What you need to do now is grab the following section (two columns only):

And then copy/paste that into QuickDBD here like this:

You’ll see the red Xs. This is just because in SSMS there is a Tab character there and you need to replace it with a Space. You should now have this:

No go ahead and run sp_help on these two tables: 

sp_help Territories

sp_help EmployeeTerritories

Then like you did above, copy/paste those two columns (name and type) into QuickDBD. And again replace the Tabs with Spaces so that you have this: 

Finally, we need to do the relationships. 

In the sp_help results, you would have a list of Keys that will help you do this. For our demo we are only going to map these three tables (to save typing) but obviously you can replicate this for all foreign keys that this table relates to. 

Firstly, add the characters PK against each of the Primary Keys. If you have done this correctly, a little image of a Key will appear next to them in the diagram. 

And now to add the relationship, add in the FK characters, followed by a >- and then the target field. If you’ve done it correctly, then you should end up with the following: 

That’s it … you can just keep going now copying and pasting the fields from SSMS using the sp_help stored procedure and QuickDBD will do the rest for you!

Please reach out to me if you need any help!

Rodney 

www.rodneyellis.com.au