By: Atif Shehzad | Comments (23) | Related: 1 | 2 | 3 | 4 | > Database Design
Problem
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.
Solution
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.
About the author
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
