Column Dependencies

 

Often it happens, that we want to play with a column value of a table. Not knowing the ramification of the column value, we have to resort to the back breaking manual process of finding the dependency on the column. Using the management studio, get the list of stored procedures depended on the table. Go thru each stored procedure and check which stored procedure does what with the column in question.

 

Today, I came across 29 stored procedure to be waded thru to get a column significance in a table. The UI in management studio gives the dependency on the table, but does not discuss the column level dependency. However, all this information is nicely tucked in the SQL tables. Using the following query, my quest reduced from 29 Stored procedures to 3 stored procedures. Here is the query

 

/*

      Script to get the name of stored procedure which

    effect the column

*/

declare @TableName varchar(250)

declare @ColumnName varchar(250)

declare @TableId int

declare @ColumnId int

set @TableName = 'EventDetail '

set @ColumnName = 'NotificationType'

-- Get the TableId

 

select @ColumnId = Column_id from sys.columns (nolock)

                  where object_id = Object_id (@TableName) and Name = @ColumnName

if @ColumnId is not null

begin

      select distinct o.Name, d.is_updated, d.is_selected

            from sys.objects o (nolock) join sys.sql_dependencies d (nolock)

                  on d.object_id = o.object_id

                  where d.referenced_major_id = Object_id (@TableName)

                              and ( d.class = 0 and d.referenced_minor_id = @ColumnId )

end                          

else

      begin

            select distinct o.Name

                  from sys.objects o (nolock) join sys.sql_dependencies d (nolock)

                        on d.object_id = o.object_id

                        where d.referenced_major_id = Object_id (@TableName)

      end

From BOL:

Dependencies are established during CREATE only if the referenced (independent) entity exists at the time that the referencing (dependent) object is created. Due to deferred name resolution, the referenced entity need not exist at the time of creation. In this case, a dependency row is not created. Moreover, entities referenced through dynamic SQL do not establish dependencies.

Comments

• Anonymous
  April 11, 2007
  Not bad. Neat trick. But it only seems to work under SQL 2005 - at least that's what I presume since it choked on SQL 2000.

• Anonymous
  April 11, 2007
  Yes, I tested it only with SQL 2005.

• Anonymous
  September 25, 2007
  works great. Thanks!

• Anonymous
  February 12, 2009
  This is just what I need! Saves me a lot of time! Thank you thank you!!

• Anonymous
  March 30, 2009
  Saved me a few hours work - thanks