Run the script below in the database you want to create a diagram for. It will create a series of statements that you will execute in a new [Databasename]_Diagram database.
/*
1. First, before getting into any details, before even getting
into the requirements, understand a simple scenario.
a. We have a table named some_table.
b. We have a stored procedure named some_proc.
c. The stored procedure some_proc contains the following SQL
statement:
select * from some_table
d. So, we now know that some_proc has a dependency on some_table.
2. Now understand the requirements.
a. The goal is to determine the impact of changes. Suppose some_table
is changed. Because some_proc depends on some_table, a change to
some_table could impact some_proc. That is what must be shown, the
dependency of some_proc on some_table.
b. There isn't any need to worry about whether or not a change to
some_table actually does impact some_proc. The issue is that there
is a potential impact. The process of impact analysis is really a
two step process:
(1) Figure out potential impacts.
(2) Consider the change carefully and see which potentially impacted
objects are actually impacted.
The issue is that figuring out where to look in the first place is
the pain point. That is the objective. The second part is just a
matter of going the analysis after figuring out where the analysis
should be performed.
3. The way to show a potential impact is to have lines drawn from dependent
objects to "parent" objects. The only way to draw a line is to create a
foreign key relationship. A foreign key relationship only exists between
two tables, not between a table and a stored procedure.
If the proper abstraction of the problem is made, the solution is obvious.
Create a new database. Every object in the database being analyzed is made
into a table in the new database. Dependencies between objects (which are
all tables now) are shown as foreign key relationships.
4. Generate DDL to create a table for every table, view, functions and stored procedure.
Every table name begins with a prefix indicating the type of object
for the original object. The DDL that is generated will create the following
tables:
TABL some_table
PROC some_proc
The tables will not contain data. I don't like using embedded spaces in object
names, but this is a good time to make an exception. Object names containing
embedded spaces must be delimited. The best approach is to use brackets. The
other approach involves using QUOTED_IDENTIFIERS, but that is a setting that
must be turned on. Brackets always work.
2. Generate DDL to create foreign keys for the tables created in the previous step.
The foreign keys are created where one object depends on another. Referring to
the example objects, a foreign key is needed to represent the dependency
some_proc has on some_table. This is accomplished by the following DDL (remember
it is necessary to delimit object names containing spaces):
alter table [PROC some_proc]
add constraint fk_P_some_proc_U_some_table
foreign key (fk) references [TABL some_table] (pk)
3. Sybase has all dependencies in sysdepends.
SQL Server 2000 has all dependencies in sysdepends and sysreferences.
4. How to make all of this work.
The SQL statements shown below are used to generate DDL statements that must
be executed. You will need to set the Query Analyzer or SQL Server Management
Studio to display the results as text instead of in a grid in order to
facilitate the copy/paste process.
*/
/* Step 1
Set to Results in Text first.
Run this to generate DDL statements.
*/
set nocount on -- suppress the message "(n row(s) affected)"
/* This select statement generates DDL of this general format:
create table [PROC CustOrderHist] (pk int primary key, fk int)
It is necessary to create a table for each object that appears in the
diagram. The type of object is appended to the object's name so that
the diagram clearly indicates both the object type and object name.
*/
select
'create table ['
+ CASE rtrim(type) -- rtrim is used because type is char(2)
WHEN 'FN' THEN 'FUNC' -- scalar FuNction
WHEN 'IF' THEN 'FUNI' -- Inline table Function
WHEN 'P' THEN 'PROC' -- stored Procedure
WHEN 'RF' THEN 'REPR' -- Replication Filter stored procedure
WHEN 'TF' THEN 'FUNT' -- Table Function
WHEN 'TR' THEN 'TRIG' -- TRigger
WHEN 'U' THEN 'TABL' -- User table
WHEN 'V' THEN 'VIEW' -- View
WHEN 'X' THEN 'XPRC' -- eXtended stored procedure
END
+ ' ' + name + '] (pk int primary key, fk int)'
from sysobjects
/* restrict the diagram to only include the object types of interest */
where rtrim(type) in ('FN','IF','P','RF','TF','TR','U','V','X')
/* don't want system views in the diagram */
and name not in ('sysconstraints','syssegments') -- these are system views
/* don't want the Visual SourceSafe stored procedures in the diagram */
and name not like 'dt_addtosourcecontrol%'
and name not like 'dt_adduserobject%'
and name not like 'dt_checkinobject%'
and name not like 'dt_checkoutobject%'
and name not like 'dt_displayoaerror%'
and name not like 'dt_drop%byid'
and name not like 'dt_getobjwithprop%'
and name not like 'dt_getpropertiesbyid%'
and name not like 'dt_isundersourcecontrol%'
and name not like 'dt_setpropertybyid%'
and name not like 'dt_validateloginparams%'
and name not like 'dt_verstamp%'
and name not like 'dt_whocheckedout%'
and name not in ('dtproperties','dt_generateansiname','dt_removefromsourcecontrol','dt_vcsenabled')
/* This select statement generates DDL of this general format:
alter table [PROC CustOrderHist] add constraint fk_P_CustOrderHist_U_Customers foreign key (fk) references [TABL Customers] (pk)
*/
select distinct 'alter table [' +
CASE rtrim(child.type)
WHEN 'FN' THEN 'FUNC' -- scalar FuNction
WHEN 'IF' THEN 'FUNI' -- Inline table Function
WHEN 'P' THEN 'PROC' -- stored Procedure
WHEN 'RF' THEN 'REPR' -- Replication Filter stored procedure
WHEN 'TF' THEN 'FUNT' -- Table Function
WHEN 'TR' THEN 'TRIG' -- TRigger
WHEN 'U' THEN 'TABL' -- User table
WHEN 'V' THEN 'VIEW' -- View
WHEN 'X' THEN 'XPRC' -- eXtended stored procedure
END
+ ' ' + child.name
+ '] add constraint fk_'
+ REPLACE(rtrim(child.type) + '_' + child.name + '_' +
rtrim(parent.type) + '_' + parent.name, ' ', '_' )
+ ' foreign key (fk) references ['
+ CASE rtrim(parent.type)
WHEN 'FN' THEN 'FUNC' -- scalar FuNction
WHEN 'IF' THEN 'FUNI' -- Inline table Function
WHEN 'P' THEN 'PROC' -- stored Procedure
WHEN 'RF' THEN 'REPR' -- Replication Filter stored procedure
WHEN 'TF' THEN 'FUNT' -- Table Function
WHEN 'TR' THEN 'TRIG' -- TRigger
WHEN 'U' THEN 'TABL' -- User table
WHEN 'V' THEN 'VIEW' -- View
WHEN 'X' THEN 'XPRC' -- eXtended stored procedure
END
+ ' ' + parent.name
+ '] (pk)'
from sysobjects parent inner join sysdepends
on parent.id = sysdepends.depid
inner join sysobjects child
on child.id = sysdepends.id
where rtrim(child.type) in ('FN','IF','P','RF','TF','TR','U','V','X')
and child.name not in ('sysconstraints','syssegments') -- these are system views
and child.name not like 'dt_addtosourcecontrol%'
and child.name not like 'dt_adduserobject%'
and child.name not like 'dt_checkinobject%'
and child.name not like 'dt_checkoutobject%'
and child.name not like 'dt_displayoaerror%'
and child.name not like 'dt_drop%byid'
and child.name not like 'dt_getobjwithprop%'
and child.name not like 'dt_getpropertiesbyid%'
and child.name not like 'dt_isundersourcecontrol%'
and child.name not like 'dt_setpropertybyid%'
and child.name not like 'dt_validateloginparams%'
and child.name not like 'dt_verstamp%'
and child.name not like 'dt_whocheckedout%'
and child.name not in ('dtproperties','dt_generateansiname','dt_removefromsourcecontrol','dt_vcsenabled')
and child.id != parent.id -- filter out self dependencies
/* This select statement generates DDL of this general format:
alter table [TABL Orders] add constraint fk_U_Orders_U_Customers foreign key (fk) references [TABL Customers] (pk)
*/
select 'alter table [TABL'
+ ' ' + child.name
+ '] add constraint fk_'
+ REPLACE(rtrim(child.type) + '_' + child.name + '_' +
rtrim(parent.type) + '_' + parent.name, ' ', '_' )
+ ' foreign key (fk) references [TABL'
+ ' ' + parent.name
+ '] (pk)'
from sysobjects parent inner join sysreferences
on parent.id = sysreferences.rkeyid
inner join sysobjects child
on child.id = sysreferences.fkeyid
/* Step 2
Run the DLL created by the previous statement.
You should probably execute the DDL in a completely new database to keep
the diagramming tables separate from the real tables.
*/
/* Step 3
Go make your diagram!
Important usage note: You might need to check the checkbox "Add related tables
automatically" before you select your first table. It depends on what you are
trying to accomplish.
If you are diagramming Northwind, select TABL Products to get a good
example diagram (check the checkbox first).
*/
Source: http://www.dbazine.com/sql/sql-articles/cook17
