Developing Matt

My Technical Journal

Find all procs that use this column

leave a comment »

Mark (my boss) found a nice little t-sql script that finds any stored procedure that uses a certain table and column

declare @TableName varchar(250)
declare @ColumnName varchar(250)
declare @TableId int
declare @ColumnId int
set @TableName = ‘tablename’
set @ColumnName = ‘columnname’
— 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

Advertisements

Written by matt

December 3, 2008 at 10:15 am

Posted in T-Sql

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

%d bloggers like this: