SQL Server Database Diagram Tool in Management Studio

By:   |   Updated: 2022-04-06   |   Comments (7)   |   Related: 1 | 2 | 3 | 4 | > Database Design


Problem

Often sophisticated tools such as ER/Studio or ERWIN are used to create database diagrams, but these are not necessary to create simple database diagrams, print them or convert to a Microsoft Word document or to PDF to save and share with your team.  In this tip we will walk through how to create a database diagram using SQL Server Management Studio (SSMS).

Solution

Using Microsoft’s sample database WideWorldImporters, I will demonstrate creating a simple database diagram of a limited number of tables focusing on Invoice related tables in the database.  I’ll then copy the Database Diagram to MS Word so it can be saved and printed.

Step 1 – New Database Diagram using SQL Server Management Studio

In SSMS under the database WideWorldImporters, right click "Database Diagrams" and select "New Database Diagram".

Example for SSMS Database Diagram Menu

Step 2 – From Database Diagram – Select a table

From the Database Diagrams tool dialog scroll and select Invoice table. Press Add and then Close.

SSMS Database Diagram - Add Table

Step 3 – From Database Diagram – Add Related tables

Right click the Invoices table and select "Add Related Tables".  This automatically adds tables linked to the Invoices table by Foreign Keys of which the Invoices table is a foreign key.

SSMS Database Diagram - Add Related Tables

Step 4 – Eliminate Unwanted Tables from the Diagram

Right click on any table that might clutter the diagram and that you feel is not important and select "Remove From Diagram".

SSMS Database Diagram - Remove Tables

Step 5 – From Database Diagram – Auto Arrange Tables

Right click the Invoices table and select "Arrange Tables". This organizes the tables with out overlapping.

SSMS Database Diagram - Arrange Tables

Step 6 – From Database Diagram – Adjust the Layout

You can do the following:

  • Right click to select a Zoom level to see your tables. 
  • Drag a table to adjust the positioning. 
  • Click a table and put the cursor on the edge to resize the table layout.
SSMS Database Diagram - Zoom

Step 7 – From Database Diagram – Auto Size All Tables

Use CTRL + A to select all tables. Right click any highlighted table and select "Autosize Selected Tables". Select and drag the connector lines to rearrange.

SSMS Database Diagram - Auto Size

Step 8 – From Database Diagram – Copy to Clipboard

Once the Diagram is sized and arranged as desired, hit Save and give your Diagram a name.  Then right click any white space in the Diagram and select "Copy Diagram to Clipboard".

SSMS Database Diagram - Copy Diagram

Step 9 – Paste the Diagram to MS Word

Open Microsoft Word.  Insert a single cell Table and paste the Database Diagram in the cell.

MS WORD Table

Step 10 – In Word Adjust and Maximize the Database Diagram Image

Adjust the Word document to maximize the database diagram image.  Change the margins to .5 inch, enter the image and resize it to enlarge. Below is a snippet from a Word document Database Diagram. 

MS Word - Snippet

Wrap Up

Once the diagram is in Word you can print it, share it, or save as a Word Doc or PDF file.  This is an effective way to create simple limited Database Diagrams that can be leveraged when documenting your database.  I find creating partial database diagrams around logical groups of tables a good way to document a database.  There are other options that can be done in SSMS Database Diagrams such as only showing the Table Names and Relationships or only displaying the Key Columns which may allow more tables to be added to the Database Diagram and still fit in a Word document.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-04-06

Comments For This Article




Thursday, May 16, 2024 - 10:56:19 AM - Lorinda DesRoches Back To Top (92244)
This was very helpful to quickly build a table diagram to understand my DB.

Thursday, August 11, 2022 - 4:07:55 PM - Izhar Azati Back To Top (90371)
But there is a problem with printing from SSMS when there is also the "IS NULL" column. The problem has existed for several years.
The checkbox print very large.

Sunday, October 18, 2020 - 5:32:34 PM - Amira Ali Back To Top (86655)
Thank you so much, please keep up the good work!

Sunday, May 17, 2020 - 11:29:44 AM - Jim Evans Back To Top (85681)

Hello Ali - Thanks for reviewing the article.  

When in SQL Server Management Studio with your database diagram opened and created, right click the link between two tables and go to Properties.  Expand "Tables and Columns Specifications" to see the Primary Key and Foreign Key column names.  

Hope this helps,

Jim


Wednesday, April 29, 2020 - 2:14:26 AM - Ali Back To Top (85497)

hello

I need to know which columns from two tables are in relation, i.e. which column from master is private key and which column from detail is foreign key, as sql server data diagram does not automatically point to these columns. how can I do that?

Thanks in advance


Tuesday, December 24, 2019 - 12:17:53 PM - Jim Evans Back To Top (83510)

Thanks for the post Ray. It is good to point out that you can modify database schema using the Database Diagram tool. Personally I only use the Database Diagram tool to get a picture of what is there and not to design the database. Note, to create Database Diagram via SSMS you must be a member of the db_owner role. Being a member of the db_owner roles comes with great responsibilities and understanding and the need to be cautious to avoid unintended mistakes.


Tuesday, December 17, 2019 - 12:19:26 PM - Ray Herring Back To Top (83453)

I have avoided the diagram feature for years, and discouraged my developers from using it also.  Early on we had some bad experiences with folks making unintential (and unrecorded) changes to data bases. :).















get free sql tips
agree to terms