Learn more about SQL Server tools

   
   















































Getting started with SQL Server database diagrams

MSSQLTips author Atif Shehzad By:   |   Read Comments (21)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Design

Problem
Trying to visual 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 at the data model gets larger it is often difficult to see exactly how the tables relate.  In this tip I show you how you can use the built-in SQL Server database diagram tool.  I go through some of them messages you may get when getting started and then look at various tasks that you would perform using this free tool.

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

  • You can use only 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.
  • Also if you modify any table outside the diagram editor, the changes will automatically be updated in any saved diagrams.
  • Any operation that may be implemented on a table by opening it in table designer of SSMS can also be implemented through using database diagrams. And such updates would be reflected in the tables architecture. Examples of such tasks are index creation, constraints and relationships.
  • Users that are owner of a database or member of the db_owner database role can view all of the diagrams. Other users can view only their own diagrams and they can create new diagrams with certain limitation according to permissions they have in the database
  • There is no undo or redo facility provided in 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 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 SQL Server 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 may change NULL values setting or 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 mentioned throughout this tutorial, but along with accessing options through right clicks, you may also access these through the Database Diagrams menu in SSMS.

Where 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 starting 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 would 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

Message while starting with database diagrams of a attached/restored database

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

It is an informational message and would prevent you from working on database diagrams. The message says that your database has no valid owner currently. Reason for this error is that database owner on a previous SQL Server instance does not exist here. To overcome this error just add the valid database owner through 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 through non db_owner user

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

This warning, that several objects may not be accessible is due to lack of permissions. So while working with database diagrams you will be able to add only tables for which you have permissions. And if you lack to DDL permissions in a database then your changes for DDL operations may not be saved. Although you may start working with database diagram 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 database owner or member of the db_owner database role.


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 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 SSMS database diagram editor and choose option "Add Table"

A frame with 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 table name, provide the name for the new table as lib_groups
  • A new table will be created in diagram editor and we can add columns for the new table here

Currently this table exists in 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 primary key on our newly created table Lib_Groups. Right click on column record on which the primary key is required to be created. Choose option "Set Primary Key" as shown in diagram.

Now the primary key has been created on GroupCode through 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 unique key constraint on column GroupName in table lib_Groups. Right click on required column in database diagram and choose option "Indexes/Keys" as shown below.

A frame will appear same as that in SSMS table designer. Add new key configuration parameters for the key in the same way as in case of 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 relations among 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 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 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 get detailed information displayed, select all or required tables in diagram editor. Then right click on any selected table and choose standard view as shown in 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 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 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 will be 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 would rapidly consume multiplie 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 other 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: 8/14/2009


About the author
MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Saturday, June 14, 2014 - 2:44:50 AM - Alireza Read The Tip

Thank you for sharing your experiences with SSMS database diagrams.


Saturday, November 16, 2013 - 12:33:19 AM - Atif Read The Tip

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 Read The Tip

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 Read The Tip

@Atul. You may use ERWin for diagrams.

Thanks


Sunday, September 08, 2013 - 6:16:29 AM - RAJ Read The Tip

 

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 Read The Tip

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 Read The Tip

@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 Read The Tip

Atif,

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


HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader


Tuesday, July 09, 2013 - 3:39:53 PM - Alfonso Read The Tip

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 Read The Tip

 

Godd and useful


Monday, April 08, 2013 - 10:09:11 AM - Ali Read The Tip

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 Read The Tip

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.

Thanks

 


Wednesday, February 13, 2013 - 12:19:27 PM - AJC001 Read The Tip

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 Read The Tip

Thanks For your Good Article !!!!~


Friday, May 11, 2012 - 12:43:57 AM - Atif Read The Tip

@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 Read The Tip

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

 

laxman


Wednesday, February 22, 2012 - 10:59:07 AM - Mitzanu Read The Tip

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 Read The Tip

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

http://www.dotnetboss.com/2010/12/31/set-unique-key-in-sql-server-using-visual-editor/


Thursday, August 27, 2009 - 3:26:08 PM - cdunn Read The Tip

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 Read The Tip

 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.

Thanks 


Monday, August 17, 2009 - 5:16:53 AM - ffalcon1961 Read The Tip

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




 
Sponsor Information