If you work with databases which contain hundreds if not thousands of views, functions, tables and stored procedures, sometimes the databases might get so big and complex that it is hard to keep track of dependencies and the structure of the backend of the application. Think about it, a system grows and grows over time, programmers just keep adding more and more functionality, sometimes they dont even remember the functionality was there in the first place, so they add something new, which is the same as something alread there.

The reason I wrote code similar to this to help with my work was a bit different, but the underlying problem was basically the same 'dependencies'. I needed to replicate my code from system to system and do it in a smooth simple way. The solution was to write a script based on the same ideas in this script, which would replicate my code and take care of dependencies. Now I just push a button and a few minutes later my code has been replicated, with detail information and summary information about how the replication executed. Writing that script for myself gave me the idea that it would be nice to have a script which would print me a graph of the dependencies of routines in any database.

I include in this article a script I called the Routine Dependency Map Visualizer. The script generates a graph like output showing for each routine in the database (view, function,stored procedures) all its dependencies as well as all the routines that are dependent on it.

How the script works?

Microsoft maintains routine dependency information by storing for each routine in the database all the references, one row in a table for each reference to another routine. If you call the system view sys.sql.dependencies, you will see a row for each reference to each routine of each routine. The tool uses this formation to find all the references to each routine as well as to count the number references each routine uses. This sounds confusing, it is easier to look at them like all the routines each routine calls as well as all routines that call a given routine.  All the routines that call me (any routine) and all the routines that I (any routine) call. In the graph i print this using the following terminology

     routine.1

     all routines pointing to me

     <--- routine.2

     <-- routine.3

     ....

    all routines I point to

    ---> routine.4

    ----> routnine.5

   ....

There are two columns we need to use for these queries, the first one is the well known object_id, which every one knows is the id of a given routine or object in a Microsoft database. The second column is not so well know but now you will know it well, it is called, referenced_major_id, and it contains exactly that, the id of the routine which any routine is calling in its code.

With these knowledge we can now count or list all the routines any given routine calls as well as all the routines which call any given routine, its dependencies and its dependent. I say both count and list because the script does exactly that, it counts dependencies and dependents in order to sort the output in such a way that all routines with no dependencies or very few come before all routines that have dependents.  This sorting allows us  to see the graph from the roots downwards, so the routines with no dependencies are the roots and all their dependents come bellow.

I also needed this order because I used a similar script to replicate code from database to database and it needed to take care of dependencies, so by the time a routine was to be created or updated all its dependencies had to exist and had to had been updated. The script counts and lists all dependencies and dependents. The listing is just to show all the dependencies and dependents for each routine (output).

Let's look at an example and the you will be able to understand what the script does. Lets pick a routine from the MSDB database, say 'sp_verify_proxy_identifiers'. System stored procedures has object_id in my database of '1318295756'. You can get the object_id by simply using this query

select object_id('sp_verify_proxy_identifiers')

Now to count the number of routines 'p_verify_proxy_identifiers' calls we run this query

select count(distinct referenced_major_id)
 from sys.sql_dependencies
 where object_id = 1318295756

Since we know that each row in the 'sys.sql.dependencies' view contains a reference for each routine that each routine is calling, by narrowing the search with the where clause to return only the routines 'sp_verify_proxy_identifiers' (where object_id = 1318295756) we get a count of the number of routines 'sp_verify_proxy_identifiers' is calling in this case only one. 'sp_verify_proxy_identifiers'' only calls one other routine. The scripts also tells us which one is that routine, or in this case is a table called 'sysproxies'. You can tell this routine is very high up in the dependency graph hierarchy, because it only depends on tables not on other routines.

Now lets count the number of dependents of sp_verify_proxy_identifiers' . In other words lets count the number of routines that call (use) it. The query is similar to the one before

 select count(distinct object_id)
 from sys.sql_dependencies
 where referenced_major_id = 1318295756

The difference is that instead of using where object_id we use where referenced_major_id, why? Simply remember that each row contains the reference for each routine, so before we counted all the rows where object_id = 1318295756, we counted what 1318295756 was calling, and now if we count the rows where the referenced object is 1318295756, we get all the instances when this routine is called (referenced).

The count where object_id =  1318295756 is how many routines this object is calling and the count where referenced_major_id =  1318295756 is counting how many objects are calling  this routine. For this example routine we get a count of 16, so  'sp_verify_proxy_identifiers' is called by 16 other routines.

Look a the image below.

The script works exactly like that, but with the script we can't use where object_id = some hard coded number? This is because we want the script to run for all routines. In order to do this, the script links to sys.modules and sys.objects to grab other information for each routine and builds a cursor called all_objects, I should have called all just 'routines' but sometimes tables are involved as you saw in the example before, specially top level routines which only depend on tables.

With this explanation I am convinced you can follow the script which is commented at each section.

The algorithm in pseudo code is like this

• create cursor for all routines
• for each routine
• create cursor of all its dependencies
• for each dependency, show it
• create cursor for all its dependents
• for each dependents, show it

The script is also available for download in a file. But you can just copy it from below

I hope this articles helps you in your work, and also I hope it gives you more insight into your own code, and other programmers' code by helping you understand how it is structured and linked together.

/*
 routines dependancy map visualizer
 written by Angel Rapallo 2012
 
 prints dependancy map for allroutines
*//*
 use msdb database for this example
*/use msdb
/*
 declare some variables
*/declare @objects_name as varchar(255)
declare @objects_definition as varchar(max)
declare @objects_type as varchar(3)
declare @object_id as int
declare @object_dependency_name as varchar(255)
declare @object_dependency_type as varchar(3)
declare @message as varchar(max)
/*
 normally sql programmers would use the print statement
 but the print statement only flushes the putput buffer
 until all statement have run so in order to provide
 some feed back as the code developes, i use raiserror
 which returns inmediately, i know is wierd becuase it can make
 someone not famailiar with it, think i am raising some error
 but that is the only way.
*/print ''
raiserror ('msdb dependancy map',0,1) with nowait
print ''
/*
 create a cursor for all routines
 views, table functions, scalar functions,
 and stored procedures
*/raiserror ('building dependancy tree...',0,1) with nowait
declare all_objects cursor static for
 select
   x.object_id,
   x.[name],
   y.[definition],
   x.[type]
 from
             [msdb].sys.objects x
  inner join [msdb].sys.sql_modules y  
  on x.object_id = y.object_id
 /* i dont think i need to do this checking but just in case */ where x.[type]  in ('p','fn','if','v') 
 order by
  /*
   this sorting takes care of dependancy for good
   becuase the routines which have less dependancy
   on others and the highest number of dependants
   allways come first so by the timne a routine gets
   to be created all its dependancies have been
   created already
  */  /*
   sort by the number of routines i point to ascending
  */  (  
   select count(distinct xxx.object_id)
   from [msdb].sys.sql_dependencies xxx
   where
    x.object_id = xxx.object_id and
    (
     select [type]
     from [msdb].sys.objects zzz
     where zzz.object_id = xxx.referenced_major_id
    ) in ('p','fn','if','v','u')    
  ) asc,  
  /*
   sort by the count the number of routines pointing to me   
   descending. only  views and routines counted
  */  (  
   select count(distinct xxx.object_id)
   from [msdb].sys.sql_dependencies xxx
   where
    x.object_id = xxx.referenced_major_id and
    (
     select [type]
     from [msdb].sys.objects zzz
     where zzz.object_id = xxx.object_id
    ) in ('p','fn','if','v','u')
  ) desc
    
open all_objects
fetch next
from all_objects
into
 @object_id,
 @objects_name,
 @objects_definition,
 @objects_type
/*
 loop through all routines to gather
 the dependancy information
*/while (@@fetch_status = 0) begin
 raiserror(@objects_name ,0,1) with nowait
 raiserror(' routines pointing to me' ,0,1) with nowait
 
 /*
  create a cursors for all objects which point to me
  that is which depend on the current routine beeing
  done. in microsoft terminology objects that reference me
 */ declare objects_pointing_to_me cursor static for
  select distinct
   xxx.[name],
   xxx.[type]
  from
   [msdb].sys.objects xxx
   inner join [msdb].sys.sql_dependencies yyy
   on
    xxx.object_id = yyy.object_id and
    yyy.referenced_major_id = @object_id 
 
 open objects_pointing_to_me
 fetch next from objects_pointing_to_me
 into
  @object_dependency_name,
  @object_dependency_type
 
 while (@@fetch_status = 0) begin
  set @message =
   '  <- ' + @object_dependency_name +
   replicate('.',60-len(@object_dependency_name)) +
   case
    when @object_dependency_type = 'fn' then '(scalar function)'
    when @object_dependency_type = 'if' then '(table function)'
    when @object_dependency_type = 'v' then '(view)'
    when @object_dependency_type = 'p' then '(stored procedure)'
    when @object_dependency_type = 'u' then '(table)'
   end   
  raiserror(@message ,0,1) with nowait
  fetch next from objects_pointing_to_me
  into
   @object_dependency_name,
   @object_dependency_type
 end
 
 if (@@cursor_rows = 0) begin
  raiserror('  none' ,0,1) with nowait
 end
 raiserror('' ,0,1) with nowait
 
 close objects_pointing_to_me
 deallocate objects_pointing_to_me
 
 raiserror(' routines i point to' ,0,1) with nowait
 
 /*
  create a cursor for all objects which i point to
  meanning which i depend on. in microsoft terminology
  objects i reference.  
 */ declare objects_i_point_to cursor static for
  select distinct
   xxx.[name],
   xxx.[type]
  from
   [msdb].sys.objects xxx
   inner join [msdb].sys.sql_dependencies yyy
   on
    xxx.object_id = yyy.referenced_major_id and
    yyy.object_id = @object_id 
 
 open objects_i_point_to
 fetch next from objects_i_point_to
 into
  @object_dependency_name,
  @object_dependency_type
   
 while (@@fetch_status = 0) begin
  set @message =
   '  -> ' + @object_dependency_name + ' ' +
   replicate('.',60-len(@object_dependency_name)) +
   case
    when @object_dependency_type = 'fn' then '(scalar function)'
    when @object_dependency_type = 'if' then '(table function)'
    when @object_dependency_type = 'v' then '(view)'
    when @object_dependency_type = 'p' then '(stored procedure)'
    when @object_dependency_type = 'u' then '(table)'
   end   
  raiserror(@message ,0,1) with nowait
  fetch next from objects_i_point_to
  into
   @object_dependency_name,
   @object_dependency_type
 end
 
 if (@@cursor_rows = 0) begin
  raiserror('  none' ,0,1) with nowait
 end
 
 close objects_i_point_to
 deallocate objects_i_point_to
 
 raiserror('' ,0,1) with nowait
 
 fetch next from all_objects
 into
  @object_id,
  @objects_name,
  @objects_definition,
  @objects_type
end
close all_objects
deallocate all_objects