atabase diagrams are indispensable tools for understanding the structure of a complex database. SQL Server 2000 and SQL Server 2005 provide useful yet rudimentary diagrams that show only the relationships between tables. Some third-party diagramming solutions go beyond table relationships to show full dependencies between all objects in a database. Such dependencies diagrams provide powerful graphic representations that are very useful for performing impact analysis, the careful evaluation of the consequences of a change to a database object. For example, the impact of altering the structure of a database table is not limited to potentially affecting child tables with foreign key constraints. Views, stored procedures, and functions that refer to (depend on) the database table may have to be modified to match the revised table structure.
You can extend SQL Server 2005's database diagramming feature to produce full dependency diagrams by creating a companion database that you use only for creating dependency diagrams. You create a table in the diagramming database for every database object in the database of interest. For example, when you create a table in the diagramming database to represent a stored procedure in the database of interest, you can model the dependency between the stored procedure and a table by using a foreign key constraint.
A Standard Diagram Versus a Dependency Diagram
Figure 1 shows a portion of the
AdventureWorks database using just the out-of-the-box diagramming capability of SQL Server 2005. I created the diagram by initially adding the
Employee table from the
HumanResources schema to the diagram. Next, I used the
Add Related Tables option to add the remaining tables. Finally, I chose the
Name Only option from the
Table View submenu.
| Figure 1. Standard Diagram of the Employee Table and All of Its Child Tables |
The diagram is useful and clearly shows that seven tables in four schemas could be impacted by changes to the Employee table. While helpful, that information is only the tip of the iceberg. What about the views, triggers, stored procedures, and functions that also have dependencies on the Employee table? When considering changes, you need to see the entire picture to understand the full impact of a proposed change.
To see the full complexity of the database, you need a dependency diagram that shows all of the objects, not just the tables (see Figure 2).
| Figure 2. Enhanced Diagram of the Employee Table and All of Its Dependencies |
Adding the dependencies to the database diagram reveals the full complexity of the database.
