Click here to Skip to main content
15,941,615 members
Articles / ERD

Visual Studio - Auto Generate SQL Server Table Dependency Diagram - User Guide

Rate me:
Please Sign up or sign in to vote.
5.00/5 (6 votes)
30 Dec 2023CPOL4 min read 6.6K   16   6
Visual Studio extension that auto-generates SQL Server ER diagrams based on a single table
This document outlines the usage of the SQL Server Auto-Generate Table Dependency Diagram extension in Visual Studio, detailing the steps to generate an ERD from a specific table (retrieving the related top level tables), discussing prerequisites, installation methods, diagram creation, layout options, table rearrangement, exporting or printing the diagram for sharing with team members.

Table of Contents

Introduction

This document provides an overview on how to use the Auto Generate SQL Server Table Dependency Diagram (Visual Studio) extension to quickly generate an Entity Relationship Diagram from a starting table, and bring together all 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 tables. You could create your own diagram, but the naming convention can be misleading and you don’t include all the related table!

Scope

The scope of this document is to convey the steps needed to start auto generating subset table diagrams from within Visual Studio.

Prerequisites

An understanding of SQL table relationships (1:1, 1:M, M:M. Linked List)

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

Simply 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

Visual Studio Extension Installation

There are a couple of ways to integrate the extension into Visual Studio:

Integrate through Managed Extensions

Use Visual Studio’s Manage Extensions option, and search for the extension there using the keywords generate table dependency and select to Download – restart Visual Studio.

Image 1

Image 2

Manual Install

Navigate to Microsoft’s Marketplace and download the VSIX file, close Visual Studio, then double click the VSIX file to install it (restarting Visual Studio to complete the installation).

Image 3

You will be prompted to install the extension for Visual Studio 22.

Image 4

Creating a Diagram

Then click on the menu option ToolsAuto Generate SQL Table Dependency Diagram, to bring up the diagram dialogue.

Image 5

Connect to a SQL Server Instance

Enter the connection string to your SQL Server instance (for e.g. Data Source=localhost\MSSQLSERVER01;Integrated Security=True;) and click on the Connect to Server button, to populate the database dropdown with the public databases.

Image 6

Select Database

All the non-system related databases will be displayed.

Image 7

Select Table

All the non-system related tables will be displayed.

Image 8

Compact View

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

Image 9

Example of a Compact Layout Diagram

Image 10

Extended View

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

Image 11

Example of an Extended Layout Diagram (showing data-types)

Image 12

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

Image 13

 

Rearranging Tables

You are able to move\drag tables around the diagram, to space out the tables. 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).

Image 14

Exporting a Diagram

If you wish to share your diagram with team members or keep it for future references, you can do so by exporting the ERD as an image. 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 image.

Image 15

Printing a Diagram

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

Image 16

Revision History

  • 18th December, 2024: Version 1.0 - Initial draft
  • 1st January, 2024: Version 1.1 - Adding extended diagram (data types)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Ireland Ireland
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionLocalDB Pin
GµårÐïåñ1-Jan-24 10:01
professionalGµårÐïåñ1-Jan-24 10:01 
AnswerRe: LocalDB Pin
Bert O Neill1-Jan-24 10:04
mvaBert O Neill1-Jan-24 10:04 
GeneralRe: LocalDB Pin
Bert O Neill1-Jan-24 10:53
mvaBert O Neill1-Jan-24 10:53 
GeneralRe: LocalDB Pin
GµårÐïåñ1-Jan-24 11:24
professionalGµårÐïåñ1-Jan-24 11:24 
GeneralRe: LocalDB Pin
GµårÐïåñ1-Jan-24 11:23
professionalGµårÐïåñ1-Jan-24 11:23 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA30-Dec-23 19:22
professionalȘtefan-Mihai MOGA30-Dec-23 19:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.