SQL Table Dependency Diagram Generator (SSMS 18,19 & 20)

Contents

• Introduction
  • Purpose
  • Scope
• Prerequisites
• SSMS Extension Installation
  • Copy VSIX File to SSMS Extension Folder
  • Windows Folder Permission
• Creating a Diagram
  • Select Database
  • Select Table
  • Compact View
  • Example of a Compact Layout Diagram
  • Extended View
  • Example of an Extended Layout Diagram (showing datatypes)
  • Rearrange Table Nodes
• Exporting a Diagram
• Printing a Diagram

Introduction

This document provides an overview on how to use the Auto Generate Table Dependency Diagram SSMS (SQL Server Management Studio) extension to quickly generate an Entity Relationship Diagram from a starting table and bring together all the directly related tables into one diagram.

Purpose

You may find that when you start a new project, the database schema can be overwhelming, due to the volume of tables, and you are currently working on a subset of tables. But you want to encapsulate only that subset of tables into a diagram, so that you can understand their relationship without all the other entities. You could create your own diagram, but the naming convention can be misleading, and you may not include all the related table, by mistake!

Scope

The scope of this document is to convey the steps needed to start auto generating subset table diagrams from within SSMS. NB: Currently the extension is only designed to integrate with Microsoft’s SQL Server database.

Prerequisites

• An understanding of SQL table relationships (1:1, 1:M, M:M. self-reference)
• An understand of SSMS
• You have the permissions to install this extension into the SQL Server Extensions folder.
• You have the following SQL permissions granted:
  1. Grant VIEW ANY DEFINITION to YourUserName;
  2. Grant CREATE TABLE to YourUserName;
  3. SELECT and VIEW permissions on these tables:
     1. Sys.Foreign_Keys
     2. Sys.Tables
     3. Sys.Foreign_Key_Columns
     4. Sys.Columns
     5. Information_Schema.Tables
     6. Information_Schema.Columns

Perform a simple query against these tables and you will know if you need to be granted extra permissions or not, from your DBA.

• Select * From Information_Schema.Tables
• Select * From Sys.Tables

SSMS Extension Installation

Download the VSIX file directly from Microsoft's Marketplace (this will always the updated version).

NB: Only download it at this stage, do not double click the VSIX file once downloaded, as this will try to install the extension into Visual Studio.

VSIX SSMS Version - Marketplace Download

VXIS SSMS 18 Download

VXIS SSMS 19 Download

VXIS SSMS 20 Download

Copy VSIX File to Your SSMS Extension Folder

Before copying the VSIX file into your environment, close and save any work you have in SSMS.

Copy the VSIX extension into your SSMS's Extension folder (I'll demonstrate what to do for SSMS 19, but for the other versions of SSMS, the same actions apply - your paths may differ slightly).

SSMS 18 Extensions folder:

C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Extensions

SMS 19 Extensions folder:

C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Common7\IDE\Extensions\

SMS 20 Extensions folder:

C:\Program Files (x86)\Microsoft SQL Server Management Studio 20\Common7\IDE\Extensions\

VSIX File Copied to SSMS 19 Extension Folder

Windows Folder Permission

To use the extension within SSMS, you will need to unzip the VSIX file, but to do this you will need Write permissions on the Extension folder:

Unzip the VSIX file to the default folder TableDiagramExtension within the Extensions folder:

This will create the new folder, like below:

NB: Restart SSMS to pick up the new extension.

Creating a Diagram

When connecting to SSMS 20, Microsoft have made major changes to the login screen, you now have to select the Encryption option - select Optional to ensure the extension works as expected. Connect as normal for SSMS 18 & 19.

Connect to the SQL Server Instance you wish to generate diagrams against. The SQL Server Instance should be selected by default, in the SQL Server Management Objects (SMO) tab.

Then click on the menu option Tools → Generate Table Relationship Diagram, to bring up the diagram dialogue.

From here you can select your SQL database, which will then populate the Tables dropdown based on your database selection.

Select Database

All the non-system related databases will be displayed.

Select Table

All the non-system related tables will be displayed.

Compact View

Once you have selected a table to base your diagram for, the button Diagram Type will be enabled. From here you can select to generate a simple (compact) layout or an extended layout (with datatypes).

Example of a Compact Layout Diagram

NB: Note the self-join on the ProductCategory table (looping connector).

 

Extended View

The user can select the extended layout from the split button.

Example of an Extended Layout Diagram (showing datatypes)

In the Extended view, you will also get the datatype (and length if appropriate) of each entity.

Rearrange Table Nodes

You are able to move\drag table nodes around the diagram, to give the diagram a greater visually aesthetic lookup. Simply click on a table and hold your right mouse button down, and move using your table, the connection lines will follow your table.

You can also drag the whole diagram around, when the Hand icon appears (over the white space on the diagram).

Exporting a Diagram

If you wish to save your diagram with the team or keep it for future references, simply click on the Export split button and select the format you wish to save the diagram in, you will be prompted for a location to save your file (with the extension .edd).

Printing a Diagram

If you wish to have a physical copy of your diagram, click on the print button and a Print review dialogue will be displayed, from here you can the print as normal.

Switch Between Multiple Server Instances

You can easily create diagrams for multiple SQL Server instances, by (closing Dependency Diagram modal, if open) selecting an object in the node tree of that instance, then re-open Generate Table Relationship Diagram from the menu again.

Enhanced ER Diagram

In Microsoft's generated diagram, you will see that the FK icon is not displayed, this can be a hinderance when trying to grasp the sub-set of tables relationship's. Below is an e.g. of Microsoft ERD (which you need to know what tables are related to each other before creating - not always the case when joining a new team\project) - you can see that the ShipToAddressId & BillToAddressId FK's don't portray this in the diagram - in the extension you can see these are portrayed correctly (see diagram in section Rearrange Table Nodes above).