How To Create A Database Schema Diagram

Please note that SSMS 18 no longer supports diagrams.
Check out an alternative.

In this tutorial, I will show you how to create an ER diagram with Microsoft SQL Server Management Studio (SSMS) 16.

1. Creating new diagram

To create the new database diagram, you will need to right click on Database Diagrams folder and click on New Database Diagram.

If you crate diagram for the first time you may get the following message:

SSMS requires some system procedures and a table that are not created with the database. You need to confirm to create them. This will create following system procedures in your database:

dbo.sp_alterdiagram
dbo.sp_dropdiagram
dbo.sp_creatediagram
dbo.sp_renamediagram
dbo.sp_helpdiagramdefinition
dbo.sp_helpdigarms
dbo.sp_upgradediagrams

and table:

dbo.sysdiagrams

If the above message appears, you need to right click on Database Diagrams folder again and choose New Database Diagram option.

A window will appear with list of all the tables in your database. To add the tables to the diagram select them (use Control or Shift keys to select multiple at once) and click Add button or double click on them. When you add all required tables click Close button.

You can add tables later on. Just right click on diagram pane and choose Add Table....

This will create a diagram with the tables columns, primary keys, and relationships that were read from the schema.

2. Tuning diagram

For now you have diagram with all the tables but it might not look like the way you want it. SSMS has a very useful function - Autosize. Select all tables (Ctrl + A), right click on one of them and click Autosize Selected Tables.

Tables will be arranged on the diagram. If you are not happy with the result, you can drag & drop them for better alignment.

You can also decide how to display the tables. Right click on the table and choose one of the options in Table View.

Column names

This option shows column names and primary key only.

Standard

This option will include basic column attributes.

Keys

This option will include only columns that are part of a primary, unique or foreign key.

Name only

This option will show table names only.

Custom

You can also create your custom view, where you can choose which column attributes you want to include.

After small updated my diagram looks like below:

3. Adding related tables

SSMS editor comes with one useful function. You can automatically add all the tables related to a particular table (with a foreign key relationship) to the diagram. To do it, right click on the table and choose Add Related Tables.

4. Adding relation labels

SSMS enables you to add labels to the relationships. This is always a name of the foreign key constraint. Nothing particularly useful, if you ask me.

To add labels right click on diagram pane and choose Show Relationship Labels.

5. Adding annotations

One useful option is the ability to add annotations to your diagram. To add the annotation right click on the pane and choose New Text Annotation.

This will create a blank text field where you can provide your notes and comments. This will always be visible and export with your diagram.

6. Saving diagram

You can save your diagram in the database (it will be saved in the dbo.sysdiagrams table you created earlier). To save diagram go to File -> Save Diagram_0 (this is default name for first diagram) or close the editor. You will be prompted with the diagram name. Provide a name and press OK.

7. Exporting diagram

Management Studio enables you to export diagram to image. To convert it to the image, right click on the diagram pane and choose Copy Diagram to Clipboard. You can now paste it into graphic software or into a document.

8. Opening diagram

All the diagrams saved in the database are visible under Database Diagrams folder. To open a diagram, double click it or right click on it and choose Modify option.

Pros & cons of diagrams in SSMS

Pros

Ability to add multiple diagrams into a database
Keeping diagrams with database schema
Schema changes are automatically reflected on the diagram
Ability to add annotations
Ability to customize table display
In already tool that is used by DBAs and developers

Cons

Limited formatting capabilities
Unable to add views into diagram
Unable to show relationships that are not defined in as foreign key constraints
Requires access to the database

Another way: Dataedo

There is a better way to create and share diagrams of existing databases - Dataedo. Here is a sample export of complete database documentation:

See live HTML database documentaion sample

A few of the benefits:

Easy and convenient sharing in interactive HTML
Draw diagrams for databases with no FK constraints
Build diagrams that span across databases
Attach complete data dicionary

Try for free now