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 2

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.


How to Make a Dependency Diagram

Creating a new database just for dependency diagrams is necessary to avoid cluttering the database of interest with tables that are only for creating dependency diagrams. The script for this task takes the name of the current database and appends Diagram to it. Thus, when the current database is AdventureWorks, the script creates a database named AdventureWorks Diagram. The rest of this article uses those two database names as examples.

A dependency diagram for AdventureWorks is actually created in the AdventureWorks Diagram database, not the AdventureWorks database itself. The script assumes that the database you are diagramming is on your local server and that you want your diagram database to reside on your local server. You may want to modify the Step 1 section of the script if your circumstances are different.

Each database object (table, stored procedure, function, view, etc.) in the AdventureWorks database is modeled as a table in the AdventureWorks Diagram database. You need an easy way to distinguish between the different types of objects modeled in the dependency diagram. In the Step 2 section of the script file, a prefix (TABL, VIEW, PROC, etc.) is appended to each table name to indicate which type of object is being modeled in the dependency diagram. Data Definition Language (DDL) statements similar to the following are used to create the new objects in the AdventureWorks Diagram database:

create table [AdventureWorks Diagram] .[PROC HumanResources.uspUpdateEmployeeLogin] 
(pk tinyint primary key, fk tinyint)

In other words, the uspUpdateEmployeeLogin stored procedure in the HumanResources schema of the AdventureWorks database gets modeled in the AdventureWorks Diagram database as a table named PROC HumanResources.uspUpdateEmployeeLogin. The statement is created by querying system metadata views and concatenating the results of the queries into a DDL statement.

Once all of the tables are created in AdventureWorks Diagram, dependencies between objects are modeled by creating a foreign key constraint everywhere a dependency needs to be shown in a diagram. The necessary syntax looks similar to this:

alter table [AdventureWorks Diagram] .[dbo] .[TRIG Sales.iuIndividual] add constraint 
[fk TR Sales.iuIndividual U Sales.Store] foreign key (fk) references [Sales.Store] (pk)

Creating a DDL statement like the preceding one is a bit of a challenge because the various SQL Server 2005 system metadata views use different collations. Concatenations fail when attempting to concatenate different collations. The solution is using SQL fragments such as these to force conversion to specific collations:

CASE (parent.type collate Latin1_General_CI_AI) 

where (child.type collate Latin1_General_CI_AI) in

In the diagramming database, all objects are represented as tables. The only way to draw lines between two tables is to create a foreign key constraint. A standard feature of SQL Server database diagramming is disabled foreign key constraints appearing as dashed lines and enabled foreign key constraints appearing as solid lines. Ideally, you want the diagram to be able to show the difference between a true foreign key constraint between actual tables and a dependency that is being modeled with a foreign key constraint. You fulfill this requirement by disabling all foreign key constraints that were created only to model dependencies between objects, as follows:

alter table [AdventureWorks Diagram] .[dbo].[TRIG Sales.iuIndividual] nocheck constraint 
[fk TR Sales.iuIndividual U Sales.Store]

Modeling true foreign key constraints is easy because you can easily model a foreign key using a foreign key! The syntax for creating those foreign key constraints is similar to the following:

alter table [AdventureWorks Diagram] .[dbo].[TABL Sales.Individual] add constraint 
[fk_U Sales.Individual U Person.Contact] foreign key (fk) references [TABL Person.Contact] (pk)

The difference in appearance between disabled and enabled foreign key constraints is readily apparent in Figure 3.

Click to enlarge

Figure 3. Using Disabled Foreign Key Constraints to Model Dependencies Between Objects Using Dashed Lines

It is important to understand the limitations of SQL Server metadata. Not all dependencies may show up. For example, if you create a stored procedure from a registered CLR assembly, it will not have entries in sys.sql_dependencies for the tables it references. Dependencies hidden in compiled DLLs are not known to sys.sql_dependencies. If you are aware of a dependency that is not defined in SQL Server metadata, you can manually add a foreign key relationship to the diagram to model the dependency.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date