RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Roll Your Own SQL Server 2005 Database Diagrams : Page 3

SQL Server 2005's out-of-the-box diagrams show only the relationships between tables—useful, but limited. Learn how to extend SQL Server 2005's database diagramming feature to produce full dependency diagrams.


Create Your Dependency Diagram Database

Download the script to create the dependency diagram database. Open the script file into a New Query window in SQL Server Management Studio, and connect to the database you want to diagram (see Figure 4).

Click to enlarge

Figure 4. Connect to the Database of Interest and Execute the Script, Which Creates a New Database

Executing the script creates a new database named AdventureWorks Diagram. You'll need to select the Databases nodes in the Object Explorer and click Refresh to see the new database. Navigate within the Object Explorer and right-click on Database Diagrams (see Figure 5).

Click to enlarge

Figure 5. Create a New Database Diagram

Since this is a new database, it doesn't yet have the objects for creating a database diagram. Click Yes on the dialog box to create the objects (see Figure 6).

Click to enlarge

Figure 6. Create Diagramming Support Objects

To create the diagram shown in Figure 2, right-click the diagram surface and select Add Table (see Figure 7). You can add as many tables as you wish initially.

Click to enlarge

Figure 7. Adding the Initial Table(s) to the Diagram

After adding the first table to the diagram, the next step is adding the dependencies to the diagram. Select the table, and right-click and choose the Add Related Tables option (see Figure 8).

Click to enlarge

Figure 8. Adding Related Tables (Dependencies) to the Diagram

Typically, when a diagram initially appears, objects in the diagram may be a bit jumbled. Use the Arrange Tables option to make the diagram more readable (see Figure 9). You should perform manual adjustments to the diagram to make it presentation ready.

Click to enlarge

Figure 9. Selecting the Arrange Tables Option

You should select all tables and use the Table View | Name Only option to display only the names of the tables (see Figure 10), because column names in the diagramming database do not correspond to actual objects in the database of interest. Figure 2 depicts how a diagram appears when Name Only is selected.

Click to enlarge

Figure 10. Selecting the Name Only Option

Don't forget to save your diagram to preserve your work!

Create Your Own Dependency Diagrams

By running the downloadable script described in this article, you can create your own dependency diagrams instead of buying commercial products. Third-party diagramming tools still have their place with their expanded feature sets, but you can accomplish a lot in a matter of minutes using this script. The script is commented to help you modify it and extend it to meet your unique needs.

John Paul Cook is a database and systems architect in Houston, Texas, and is a Microsoft MVP.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date