Seek table column in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Seek table column in stored procedure

Author

Topic

ajing
Starting Member

3 Posts

Posted - 2007-12-13 : 23:24:27

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-17 : 04:11:17

A quick way to do this is

select specific_name from information_schema.routines r
where r.routine_definition like '%col_name%' and r.routine_type='procedure'

Madhivanan

Failing to plan is Planning to fail

ajing
Starting Member

3 Posts

Posted - 2007-12-17 : 20:00:24

Thanks madhivanan this was just the script that I was looking for before I had improvised.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources