In this tutorial, I will show you how to create an ER diagram with Microsoft SQL Server Management Studio (SSMS).
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:
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.
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.
This option shows column names and primary key only.
This option will include basic column attributes.
This option will include only columns that are part of a primary, unique or foreign key.
This option will show table names only.
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:
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.
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.
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.
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.
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.
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.
There is another tool you can use – Dataedo.