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

Table of Contents

• Introduction
  • Purpose
  • Scope
• Prerequisites
• Visual Studio Extension Installation
  • Integrate Through Managed Extensions
  • Manual Install
• Creating a Diagram
  • Connect to a SQL Server Instance
  • Select Database
  • Select Table
  • Compact View
  • Example of a Compact Layout Diagram
  • Extended View
  • Example of an Extended Layout Diagram (showing data-types)
  • Rearranging Tables
• Exporting a Diagram
• Printing a Diagram
• Revision History

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.

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).

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

Creating a Diagram

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

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.

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 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).

Example of a Compact Layout Diagram

Extended View

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

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

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

 

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).

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.

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.

Revision History

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