SQL Server – Creating a Database Diagram – Building Data Dictionary Part 1

A Data Dictionary, in simple terms is a repository of metadata and/or information about various objects within a a database. Typically the objects included in the Data Dictionary are Tables and their Columns along with their metadata, descriptions etc.

A typical Data Dictionary includes the following information:

  • A Database Diagram/Data Model with relationships between various Tables.
  • List of Schemas and additional information about each Schema.
  • List of Tables and additional information/description about each Table.
  • List of Columns in each Table with following information about each Column:
    • Column Name
    • Data Type
    • Null Option
    • Primary Key Indicator (Is PK)
    • Foreign Key Indicator (Is FK)
    • Description about the Column
    • Any other additional information
  • An Optional Appendix containing following objects and their descriptions
    • Views
    • User Defined Types
    • User Defined Functions
    • Stored Procedures
    • Any other objects as needed

In the first part of this 3 part series on Building a Data Dictionary for a SQL Server Database, let’s look at how to generate a Database Diagram/Data Model to display the relationships between various tables. For this entire series, we will be using AdventureWorks (2008 version) database as reference and we will be building the data dictionary for this database. If you don’t have it already installed on your computer, you can get it from CodePlex.

In simple terms, a Data Model is a visual representation of tables required in a database (to support the application/system) and is used to express/convey the business requirements.

There are different types of data models depending on the level of details contained in each one, the purpose, and other factors. For this article, we will be creating a conceptual data model and discussing the other types of data models is not in scope of this article. To understand the data modeling concepts and different types of data models, take a look at this website www.learndatamodeling.com.

To create a data model, follow these steps:

  • Open SQL Server Management Studio (SSMS)
  • Connect to the server which has AdventureWorks database installed
  • Expand the AdventureWorks database
  • Right-Click on the Database Diagrams folder under AdventureWorks folder and select New Database Diagram from the context menu. When you do this, you might encounter an error something as shown below:

Error Message

To fix this issue, run the following command.

EXEC AdventureWorks.dbo.sp_changedbowner
    @loginame = N’Domain\User’
    , @map = false

In the above query, change <<Domain\User>> with a valid local/domain account which has a valid login on the database server.

  • Again right-click on the Database Diagrams folder and select New Database Diagram from the context menu and you might see the following message:

Error Message

Click on Yes in the above dialog box and it will open up the New Diagram window with Add Table dialog box as shown below.

Add Table dialog box

Notice that, in the above dialog Add Table dialog box, the text inside the brackets represents the schema of the table. For example, in the above screenshot, “Address (Person)” represents a table by name Address in the Person schema.

  • To start with, hold Ctrl key and select all the tables and click on Add and then click on Close to close the dialog box.
  • Right-click anywhere in the Create Diagram window and click on Select All from the context menu.
  • Once all the tables are selected, right click on one of the tables, go to Table View in the context menu and and then select Name Only as shown below.

Changing the Table View/Display

  • After the previous step, you will notice that all the tables are collapsed. Right-click in the Create Diagram and select Arrange Tables from the context menu, which will arrange the tables.
  • Again right-click in the Create Diagram window and select Copy Diagram to Clipboard and paste it into a Microsoft Paint application and save it or paste it into any other application of your choice. The finished diagram looks as follows:

AdventureWorks Database Diagram/Data Model

As you can see from the above diagram it is hard to make out which table is located where and this is quite obvious in case of huge databases. To address this issue, you can create multiple diagrams by choosing/adding only one table as primary for each diagram say SalesOrderDetail for Diagram1 table from Sales schema and then right click on the SalesOrderDetail table and select Add Related Tables from the context menu. You can keep on adding as many related tables as possible keeping in mind the available real estate/space to display the tables and their relationships clearly. One sample diagram is as shown below.

Database Diagram with Select Tables from AdventureWorks Database

In this fashion, you can create multiple diagram by grouping the tables logically and ensure that at the end of this exercise, all the tables present in the diagram are covered in one of the diagrams.

Copy all the diagrams created into the Data Dictionary Document (Typically a Word Document in a standard template as per your organization’s standards).

Note that, after creating the diagram in SSMS, you can save this diagram by simply clicking on the Save Diagram button (floppy symbol) in the toolbar or by pressing Ctrl+S and the diagram will be saved under the Database Diagrams folder under the respective database from where we initially started.

This is one of the easiest ways to create the Database Diagram/Data Model using SSMS. However, there are many advanced tools that are available in the market for doing data modeling like ERwin Data Modeler, ER/Studio Data Modeler, Toad Data Modeler, and many other tools available in the market with each one having it’s own set of capabilities to perform various types of data modeling.

In the next part of this series, we will add the table and column related information to the Data Dictionary Document. So stay tuned!


About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on April 12, 2012, in Documentation, SQL Server and tagged , , , . Bookmark the permalink. 2 Comments.

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s