Roll Your Own SQL Server 2005 Database Diagrams : Page 3

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).

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).

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).

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.

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).

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.

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.

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.

Previous Page