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.