Seek Table Column In Stored Procedure

Hi All,

The script below may be use to find out what stored procedure uses a specified column from any of the table. This could be helpful in cases you have change a field name of a table and want to find out what stored procedure uses that column.

create procedure seek_sp_for_columns
@colname_para varchar(500)

as

begin
create table #temp_t
(
textcol varchar(1000)
)

create table #temp_t2
(
procname varchar(500)
)

declare @procname as varchar(500)
declare @found as int
declare @colname as varchar(500)
declare @valid_colname as int


select @valid_colname = count(id)
from syscolumns
where name = @colname_para

if (@valid_colname > 1)
begin
set @colname = '%' + @colname_para + '%'



declare sp_cursor cursor
for select name
from sysobjects
where xtype = 'P'

open sp_cursor

fetch next from sp_cursor
into @procname

while @@fetch_status = 0
begin
insert into #temp_t
exec sp_helptext @procname

set @found = 0
select @found = count(textcol)
from #temp_t
where textcol like @colname

if (@found > 0)
begin
insert #temp_t2 values(@procname)
end

delete #temp_t

fetch next from sp_cursor
into @procname
end

close sp_cursor
deallocate sp_cursor


select *
from #temp_t2

drop table #temp_t
drop table #temp_t2
end
else
begin
select 'Please verify column name'
end

end