How To Draw Er Diagram In Sql 2016

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

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

i. Creating new diagram

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

If yous crate diagram for the first time y'all may get the following message:

SSMS requires some system procedures and a table that are not created with the database. You demand 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 tabular array:

dbo.sysdiagrams

If the above message appears, y'all need to right click on Database Diagrams folder again and choose New Database Diagram selection.

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

Yous tin add tables subsequently. Simply right click on diagram pane and cull Add Table....

This volition 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 mode 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 bundled on the diagram. If you are not happy with the result, you tin can drag & drib them for better alignment.

Yous can also decide how to display the tables. Right click on the tabular array and cull one of the options in Tabular array View.

Column names

This choice shows column names and primary cardinal simply.

Standard

This option will include bones column attributes.

Keys

This choice will include merely columns that are function of a principal, unique or foreign key.

Proper noun only

This option will evidence table names only.

Custom

You tin can too create your custom view, where you can cull which column attributes you want to include.

After small updated my diagram looks like beneath:

iii. Calculation related tables

SSMS editor comes with one useful function. You can automatically add all the tables related to a particular table (with a strange 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 together labels to the relationships. This is always a name of the foreign key constraint. Nothing peculiarly useful, if you ask me.

To add labels right click on diagram pane and cull Show Human relationship Labels.

5. Adding annotations

I useful option is the ability to add annotations to your diagram. To add the annotation correct click on the pane and cull New Text Annotation.

This will create a blank text field where y'all can provide your notes and comments. This will ever be visible and export with your diagram.

half-dozen. Saving diagram

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

7. Exporting diagram

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

eight. Opening diagram

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