/*

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 'FUNS'  -- 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 'FUNS'  -- 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 'FUNS'   -- 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).
*/