Learn more about SQL Server tools


Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories


Getting started with SQL Server database diagrams

By:   |   Read Comments (23)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Design


Trying to visualize a database model is sometimes quite difficult.  When the model is small it is pretty easy to have an idea what tables reference other tables.  But as the data model gets larger it is often difficult to see exactly how the tables relate.  In this tip I show how you can use the built-in SQL Server database diagram tool.  I go through some of the messages you may get when getting started and then look at various tasks that you can perform using this free tool.


SQL Server database diagrams is a powerful tool, before illustrating some of the tasks and issues it would be better to present a list of major concepts.

  • You can only use tables in SQL Server database diagrams. No other SQL Server objects are allowed to be accessed from the diagram editor.
  • Tables in the database diagram are not independent. Any modification in tables through this tool will directly affect the table architecture in the database.
  • If you modify any table outside the diagram editor, the changes will automatically be updated in any saved diagram.
  • Any operation that may be implemented on a table by opening it in table designer can also be implemented using database diagrams and these updates would be reflected in the table's architecture. Examples of such tasks are index creation, constraints and relationships.
  • Users that are an owner of a database or member of the db_owner database role can view all of the diagrams. Other users can only view their own diagrams and they can create new diagrams with certain limitations according to permissions they have in the database.
  • There is no undo or redo facility provided in the SSMS diagram editor.
  • Currently there is no method provided by Microsoft for migration of SQL Server database diagrams from one instance to another or from one database to another. However you may copy diagrams to some other file format and save it as a backup.
  • Diagrams can directly be printed using the print option in the file menu of SSMS. You can use the arrange tables and page break options in the diagram editor for better alignment of diagrams on a page for printing..
  • You can change the owner of  a database diagram just like any other SQL Server object. You may have to perform such operations if the owner of a diagram is deleted for any reason.
  • You can change NULL values setting or the data type of any column through database diagrams.
  • After an upgrade of a database, database diagrams are usable in the new version.
  • It will not be discussed in this tutorial, but along with accessing options through right clicks, you may also access these through the Database Diagrams menu in SSMS.

Where do database diagrams reside in SSMS?

SQL Server database diagrams reside in the first folder under any database in SSMS as shown below.

Before discussing features of SQL Server database diagrams, it would be better to clarify a couple of messages that you may face while working with SQL Server database diagrams.

Confirmation message while working with database diagrams in SSMS

The first time you click on the Database Diagrams folder of any user database in SSMS, you may get the following dialog.

It is not an error, but just a permission seeking dialog to create some system stored procedures that are necessary to work with database diagrams. If you choose "No" then no procedures will be created and you will not be able to work with SQL Server database diagrams. If you choose "Yes" then the following system stored procedures will be created in your database and you will be able to work with SQL Server database diagrams.

  • sp_alterdiagram
  • sp_dropdiagram
  • sp_creatediagram
  • sp_renamediagram
  • sp_helpdiagramdefinition
  • sp_helpdigarms
  • sp_upgradediagrams

Messages while starting with database diagrams of an attached/restored database

If you have just attached/restored a database from another SQL Server instance, when accessing the database diagrams folder you may get the following message.

This is an informational message and prevents you from working on database diagrams. The message says that your database has no valid owner currently. The reason for this error is that database owner on a previous SQL Server instance does not exist on this instance. To overcome this error just add the valid database owner using T-SQL or SSMS. Also verify the compatibility level of your database is correct for your SQL Server instance.

Warning while starting with database diagrams in SSMS using a non db_owner user

If you are not currently logged in as member of the db_owner database role, then when creating a new database diagram you may get following warning.

This warning is related to objects not being accessible due to lack of permissions. So when working with database diagrams you will be able to add only tables for which you have permissions. If you lack DDL permissions in a database then your changes for DDL operations may not be saved. Although you may start working with database diagrams after this warning there may be problems due to restrictions and the full features of SQL Server database diagrams may not be exploited if you are not a database owner or member of the db_owner database role.

Example Uses of SQL Server Database Diagrams

Now it is time to go through some practical uses of SQL Server database diagrams. Several options used in SSMS for database diagrams are simple, self explanatory and accessible with one click. However it is reasonable to go through some practical tasks to reveal the power of database diagrams in SSMS. We will be using the AdventureWorks database for this purpose.

Creating new SQL Server database diagram in SSMS

To create a new SQL Server database diagram

  • Go to Database Diagrams folder
  • Right click on the folder
  • Click on New Database Diagram

A new pane will appear with all tables in a frame for selection in a new diagram. Select single or multiple tables that are required to be added in the diagram and click the "Add" button.

The selected tables will be added to the diagram editor by clicking the Add button. After selection and addition is finished click the "Close" button to close the frame.

Add existing table to SQL Server database diagrams in SSMS

At any point we have the option to add an existing table or create a new table in SQL Server database diagrams. For example we may add the existing table HumanResources.Shift to our database diagram in the following way. Right click anywhere in the SSMS database diagram editor and choose option "Add Table"

A frame with a list of accessible user tables will appear and you may choose the required table or tables. Close the tables frame and you will have the selected tables in the diagram editor. As in our case we choose HumanResources.Shift.

Creating and Adding new table to SQL Server database diagrams in SSMS

A powerful feature of SQL Server database diagrams is that you can create a new table using database diagrams. You can create a table in the diagram editor, work on it and as you save the diagram the table will be created in your database.

Here we will create a new table named lib_groups and note that this table will also be created in the database.

  • Right click anywhere in diagram editor
  • Choose option "New Table"
  • A frame will appear for the table name, provide the name for the new table as lib_groups
  • A new table will be created in the diagram editor and we can add columns for the new table

Currently this table exists in the diagram editor and you will not find it listed outside the diagram editor. Once the diagram is saved the table will be created and will be accessible for all operations.

Create Primary Key through SQL Server database diagrams in SSMS

SQL Server database diagrams provide you the capability to create and manage keys on your tables. Let us create a primary key on our newly created table Lib_Groups. Right click on column GroupCode on which the primary key is required to be created. Choose option "Set Primary Key" as shown in the diagram.

Now the primary key has been created on the GroupCode using database diagrams in SSMS.

Create unique key constraint through database diagrams in SSMS

To progress with our tasks in our tutorial for working with database diagrams, we are required to create a unique key constraint on column GroupName in table lib_Groups. Right click on the required column in the database diagram and choose option "Indexes/Keys" as shown below.

A frame will appear like in SSMS table designer. Add the new key configuration parameters for the key the same way as for the table designer and click "Close".  Now our table lib_Groups has primary and unique keys.

Create relations between tables through database diagrams in SSMS

Like table designer in SSMS you can create all types of relationships between tables through database diagrams. Right click on a table where a foreign key will reside. In our case, to ensure valid and uniform group names in department table, we have to create a primary and foreign key relationship among lib_groups and department tables respectively. So right click on the Department table and click on "Relationships..."

A relationships frame will appear same as in the case of SSMS table designer. There you can configure parameters and relationships.

Is there any change script available?

As part of best practices, most DBAs save every script that is used for DDL operations in their databases. SSMS also provides an option to create scripts based on the steps you take through the GUI.  See this tip for more information - Script for the changes carried out through SSMS GUI. Luckily you also have an option to get a script for changes performed through the diagram editor.

Right click on a table for which a change script is required to be generated. The last option in the menu is to generate a change script for DDL operations on the table. This option will only be enabled if there are any changes. Click the menu item and a dialog box will pop-up to save the script.

Get more detailed view of tables

In the default format, database diagrams do not provide any information other than names for columns of tables. We can not see the data types or NULL options of columns in these tables. To further utilize many features of SQL Server database diagrams, we are required to have detailed information about the columns of each table.

To display detailed information, select all or required tables in the diagram editor. Then right click on any selected table and choose standard view as shown in the following image

Now you can see more column information in all tables. In this menu there is also an option for a customized table view.

Removing a table from SSMS database diagram editor or from database

Using the delete button on any number of selected tables will just remove the tables from the diagram. However you can delete any table permanently from the database along with removing it from the diagram. For this task you have to right click on the table and select "Delete Tables from Database".

If you choose to delete tables from a database then a confirmation dialog will appear for confirmation of the delete operation.

Deleting a relationship in SSMS database diagram editor

The delete button will not work for deleting relationships in diagram editor. Relationships may be deleted by right clicking on the relation or you can select a relationship and then delete it through the Database Diagrams menu.

A confirmation dialog will appear to confirm the delete operation for the relationship. To delete multiple relations at one time you may select multiple and use either the Database Diagrams menu or right click on any of the selected relationships to perform the delete operation.

A little formatting in SSMS database diagrams editor

As more tables are added, these may not be arranged for easy viewing. To change the arrangement of the diagrams you can right click in the database diagram editor or use the Database Diagrams menu of SSMS as shown below.

1. Add text to diagrams

To make the diagrams more readable you can add text. To add text in your diagram, right click anywhere in the diagram and choose "New Text Annotation". A text box will appear, you can write text and to format the text, right click inside the text box and select the formatting options.

2. Select all objects in diagram editor

When you right click and bring up the menu you have the option to select all added tables and any text that is present in the database diagram editor. The same task can be achieved by using Cntrl+A or by dragging a clicked mouse.

3. Show and hide Relationship Labels for database diagrams in SSMS

Relations are shown among tables through bars/pipes by default. However if it is required to also display the name of the relationship key among tables in a text form, then choose "Show Relationship Labels". It will display names of all present relationships among the tables.

4. To arrange the tables in diagram editor

Right click anywhere in the diagram editor and select "Arrange Tables". All tables will get aligned through this option.

5. Zoom in or out

By right clicking anywhere in the diagram editor, you can get the Zoom option. To analyze the diagrams in any position, you can make use of the zoom option.

6. Keep track of page breaks

SQL Server database diagrams could rapidly consume multiple pages. So it is better to keep track of page boundaries through page breaks. Enable the page break view by right clicking anywhere in the diagram editor and choose this option. Similarly you can turn off the page break view by again clicking on same menu option.

7. Recalculate page breaks

With the addition of more tables your database diagram may span many pages. Before printing the diagram it may be appropriate to recalculate page breaks, so that printing may be performed optimally. Use this option to assist with setting up your diagram for printing.

8. Copy Diagram to Clipboard

For further customization, editing or just for backup, it may be required to save the diagram to some other file format. For such purposes use the menu item to copy and paste the diagram outside the diagram editor. Along with this menu option you can copy any number of selected tables from one diagram editor and paste it to another diagram. To do this you have to use Cntrl+C for copy, because the copy option is not provided by using your mouse and then you can paste it into another diagram.

Next Steps

Once a diagram has been created, save it and follow these next steps.

  • As SQL Server database diagrams are very powerful and require the user to be a member of the db_owner role, development servers are always the best option to work with database diagrams. Due to permissions issues, database diagrams are not suitable for production servers.
  • For a better view of a diagram you can switch to the full screen view of SSMS by pressing Shift+Alt+Enter keys. Or you may access the full screen option using the menus in SSMS.
  • You may rename a diagram by right clicking on it in Database Diagrams folder.
  • Almost all features of SSMS database diagrams are same for SSMS 2005 and SSMS 2008. Even most of these features are applicable for database diagrams in Enterprise Manager
  • Click here to read about changing owner of database.
  • Click here to read about changing compatibility level of a database.

Last Update:

About the author


More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Get free SQL tips:

*Enter Code refresh code     

Wednesday, March 09, 2016 - 11:10:00 AM - Dave Back To Top

Overall a rather poor list of features for such a mature product. No undo or publish but instead tied tightly to the database? No multiple views of the model? We need a decent inexpensive SQL Server database designer that can spit out and maintain EF C# Code First!

Thursday, March 26, 2015 - 10:38:00 PM - Don Morgan Back To Top

Yes, Thank you.

Saturday, June 14, 2014 - 2:44:50 AM - Alireza Back To Top

Thank you for sharing your experiences with SSMS database diagrams.

Saturday, November 16, 2013 - 12:33:19 AM - Atif Back To Top

Feature to export the database diagrams is not provided in SQL Server however third party tools may be used. Table names ina diagram can not be retreived through diagram systems tables. Diagram definition there is stored in varbinary format.

Friday, November 15, 2013 - 10:43:44 AM - Mårten Back To Top

Can you export content of a diagram?

I like to get a list of all tables in the diagram in text

Tuesday, September 10, 2013 - 6:11:05 AM - Atif Back To Top

@Atul. You may use ERWin for diagrams.


Sunday, September 08, 2013 - 6:16:29 AM - RAJ Back To Top


I just started to learn basi sql and this swebsite is realy owsome and most of all given tutorial with examples are realy easy to understand and also in simple format.

Thursday, August 22, 2013 - 1:53:21 PM - Atul Vaducha Back To Top

Is it possible to draw relationships myself in the diagram without affecting the actual relationships? Is there a tool you'd recommend?

Thursday, July 11, 2013 - 9:12:55 AM - Atif Shehzad Back To Top

@Ali. You should be able to view the Database Diagrams folder under the database. After that SQL Server self creates the necessary objects to work with the diagrams provided you do not lack permissions on the server.

Wednesday, July 10, 2013 - 9:29:52 PM - Jeremy Kadlec Back To Top


Here is Alfonso's message...

Hi Atif, sorry I have a question regarding creating relationships between tables, already pueedo only link fields of the same type of data but in my diagram editor qm me a box appears asking to add tables and columns specification, I have a table "catalogoclientes "with the" cveclient "and I relate it to my table" Inventory "field" line "and right click on it is that I vote the mensage.Utilizo express sql Server2005


Thank you,
Jeremy Kadlec
Community Co-Leader

Tuesday, July 09, 2013 - 3:39:53 PM - Alfonso Back To Top

Hola Atif, disculpe tengo una duda respecto a crear relaciones entre tablas, ya se que solo pueedo vincular campos del mismo tipo de dato pero en mi editor de diagramas me aparece un recuadro q m pide agregar tablas y especificacion de columnas,tengo una tabla "catalogoclientes" con el campo "cveclient" y quiero relacionarla con mi tabla "Inventario" campo "linea" y al dar click derecho en ésta es que me vota el mensage.Utilizo sql server2005 express

Friday, June 07, 2013 - 9:24:16 PM - nathan Back To Top


Godd and useful

Monday, April 08, 2013 - 10:09:11 AM - Ali Back To Top

Hi Atif-

I don't find option of "Database Diagrams" as you had illustrated in the screen-shots. I am using MS SQL Server 2008 R2

What might be the issue. Do we need to install any tool for database designing first to have this option enabled? Thanks - Ali!

Wednesday, February 13, 2013 - 10:28:00 PM - Atif Shehzad Back To Top

Relationships will be shown if these are defined properly. Check the relationships in table designer or DMV and verify their proper definition. There may be any permission problem that may cause not to display the relationships.



Wednesday, February 13, 2013 - 12:19:27 PM - AJC001 Back To Top

When I create the diagram via the usual method ie.

  • Go to Database Diagrams folder
  • Right click on the folder
  • Click on New Database Diagram
  • add all the tables in my database

it adds all the tables and displays what the primary keys are BUT it does not display the relationships between tables which is the most important aspect that I want to capture in my diagram.

For instance, Relations are NOT shown among tables through bars/pipes even when I try to show relationships.

Does anybody know what I am doing wrong?


Friday, May 11, 2012 - 5:46:58 AM - Syyed Abdul Rehman Back To Top

Thanks For your Good Article !!!!~

Friday, May 11, 2012 - 12:43:57 AM - Atif Back To Top

@laxman. Do you want to migrate the database diagrams from one database to another through SSIS. This may be done by using "Transfer SQL Server Objects Task" in control flow. There you may operate dtproperties table to migrate the diagrams.


Thursday, May 10, 2012 - 7:19:04 AM - laxman Back To Top

hi,i need help from u r end,  how to migration projects from diffrent database into ssis,ssrs    can u provide with examples



Wednesday, February 22, 2012 - 10:59:07 AM - Mitzanu Back To Top

Try DbSchema.

Reverse engineer from many popular DBMS systems, including Oracle 9i and 10g, SQL Server, My SQL, Access, PostgreSQL and others

Sunday, April 24, 2011 - 11:53:47 PM - emubd Back To Top

What a nice solution it is! I like this solution.but i have gotten a better solution in other site.this site below.

Right click on the column which you want to set unique key(See figure 1)...........more


Thursday, August 27, 2009 - 3:26:08 PM - cdunn Back To Top

The article says "Currently there is no method provided by Microsoft for migration of SQL Server database diagrams from one instance to another or from one database to another" which is true, however it is possible to 'serialize' database diagrams and re-apply them to another database instance. This is particularly useful in continuous-integration environments where you are constantly building your database from scratch/via scripts... you can keep the serialized diagrams in source control (VSS,TFS,SVN,etc) and you can have your diagrams restored as well as your schema -- making them much more useful as a 'permanent' form of documentation.

You can 'save/restore' diagrams in:

SQL Server 2008 using the 'new' sys.fn_varbintohexstr function

SQL Server 2005 with a custom Tool_VarbinaryToVarcharHex function

and even SQL Server 2000 (courtesy of Clay Beatty)

The serialized diagram data from SQL 2008 and 2005 is interchangeable, however the data from SQL 2000 is different and can only be 'restored' on SQL 2000.

Monday, August 17, 2009 - 9:35:33 PM - @tif Back To Top

 Database diagrams are meant to be used in data modeling. You may use these to display relationships among the tables. Developers and DBAs may coordinate effectively by using database diagrams. There is no role of database diagrams in troubleshooting a problem. You may consider these as a part of database doucumentation.


Monday, August 17, 2009 - 5:16:53 AM - ffalcon1961 Back To Top

Sorry, still a little new to SQL, How will database diagrams help me troubleshoot a problem? Or is the diagram used more for building a database?

Thank you

Learn more about SQL Server tools