How to track dependencies on column and table level

 

 

This is a little something I wrote today.... I used to spend a lot of time tracking down dependencies between tables, and it's always difficult and tedious work. So after some time writing the code below, I found out that sql already provides a function that's almost as good as the one I have

 

exec sp_depends @objectname

 

Almost as good, you say? That's because mine has the option of passing a column name to the search, so if all you're looking for is which procs refer to a specific column (because you're renaming the column), then you can narrow the search down to just the procs that specifically refer to the column. If all you want is a complete list of dependencies, however, then just leave the parameter empty

 

Usage:

exec sp_find_deps @tablename, @columnname

 

Example:

exec sp_find_deps @address, '' --Leave the parameter blank for a full list

 

Code:

create proc sp_find_deps

@tablename varchar(100),
@columnName varchar(100)

as

declare @sql varchar(1000),
@databasename varchar(100)

select @databasename =  db_name()

set @sql = '
select distinct o.name parent_name, /*o.type, d.referenced_major_id, d.referenced_minor_id, c.name column_name,*/ d.is_selected, d.is_updated, d.is_select_all
from '+@databasename+'.sys.objects o
inner join '+@databasename+'.sys.sql_dependencies d on o.object_id = d.object_id
inner join '+@databasename+'.sys.columns c on c.column_id = d.referenced_minor_id --and c.object_id = o.object_id
where d.class in (0,1) and
d.referenced_major_id = (select object_id from prepassam.sys.objects where name = '''+@tablename+''' )
and c.name like ''%'+@columnName+'%''
order by o.name--, c.name
'
--print @sql
exec (@sql)