Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Roll Your Own SQL Server 2005 Database Diagrams

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.


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



Click to enlarge

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

Click to enlarge

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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap