How to Make a Dependency Diagram inn sql

How to Make a Dependency Diagram in sql

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.

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.

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.

Page 3 of 3
for futher information look into http://www.devx.com/dbzone/Article/31733/0/page/3