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 