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.

Advertisements

2 thoughts on “Column Dependencies

  1. to get the other table names which uses the same column

    select t.* from
    sys.tables t join sys.columns c on
    t.object_Id = c.object_id where c.name = @ColumnName ;

  2. sys.sql_dependencies = This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.sql_expression_dependencies instead.

    select distinct OBJECT_NAME (d.referenced_id) TABLE_OR_VIEW,
    OBJECT_NAME( d.referencing_id )
    from sys.objects o (nolock)
    join sys.sql_expression_dependencies d (nolock)
    on d.referenced_id = o.object_id
    where d.referenced_id
    in ( select Object_id (Name) from sys.tables where name like ‘UA%’)
    order by 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s