Find a Table Name :

To find table_name used in multiple procedures,



Create Procedure [dbo].[Proc_Table_Name_Search] (@table_name nvarchar(50))
as
Begin

select top 0 (left( cast(' ' as varchar(max)) + replicate(' ',450) ,450)) as text into #t1
select top 0 (left( cast(' ' as varchar(max)) + replicate(' ',100) ,100)) as sp_name,9999 as Line_number into #t2
declare @name nvarchar(50)
declare @query nvarchar(max)
declare @no int
declare test_cur cursor for
select name from sys.objects where type='p'

open test_cur
fetch next from test_cur into @name
while @@fetch_status=0
begin

truncate table #t1

insert into #t1
exec sp_helptext @name

print @name

    declare @line nvarchar(max)
    set @no=1
    declare test1_cur cursor for
    select text from #t1

    open test1_cur
    fetch next from  test1_cur into @line
    while @@fetch_status=0
    begin
  
    if (@line like '%'+@table_name+'%')
    begin
    insert into #t2 values(@name,@no)
  
    end
    set @no=@no+1
    fetch next from  test1_cur into @line
    end
    close test1_cur
    deallocate test1_cur

fetch next from test_cur into @name
end

select sp_name,Line_number from #t2

close test_cur
deallocate test_cur

End


exec Proc_Table_Name_Search '<sample_table_name>'