How to track dependencies on column and table level

2008-05-09T16:23:00.001-06:00

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)

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)

Alternatives to an OR on a join

2008-05-07T15:29:00.001-06:00

Really liked a solution I saw recently for an alternative to using an OR clause in a join, as in

select t1.blah, t2.blah from t1

inner join t2 on t1.id = t2.id

or t1.id = t2.id2

instead, try it like this

select t1.blah, t2.blah from t1

inner join t2 on t1.id = t2.id

union

select t1.blah, t2.blah from t1

inner join t2 on t1.id = t2.id2

Nice and elegant, I think. I'll have to explain it better later :-)

The Cranky DBA

How to track dependencies on column and table level

Alternatives to an OR on a join