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
all routines pointing to me
all routines I point to
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
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