Search for a table name in all the stored precedures

Question

Greeting all ,

i want to search for a table name in all my stored procedures( over 200 ) to add some filters in it .

is there a way i can make a search on all my stored procedures to find which ones that contain the table name ?

Thanks in advance .

Answer 1

Hi @Rami Frikha ,

Please refer to the following code, you only need to replace tablename with the table name you want to search：

SELECT obj.Name Storedprocedurename, sc.TEXT Storedprocedurecontent  
FROM syscomments sc   
INNER JOIN sysobjects obj ON sc.Id = obj.ID   
WHERE sc.TEXT LIKE '%tablename%'   
AND TYPE = 'P'  
  
--Note: the table name cannot add [] → brackets  

Below is the test I did, please refer to：

Create Table dbo.Employee (EmpID int null, EmpName varchar(25) null, SalaryCalc money null, HireDateYear int null )  
Insert into dbo.Employee values  
(1 , 'Tom', '5000', 2015 ),(2 , 'Tom', '8000', 2015 ),(3 , 'Tom', '9000', 2016 ),  
(4 , 'Sam', '4000', 2016 ),(5 , 'Tom', '5000', 2015 ),(6 , 'Tom', '8000', 2015 ),(7 , 'Tom', '9000', 2016 ),  
(8 , 'Sam', '4000', 2016 ),(9 , 'Tom', '5000', 2015 ),(10 , 'Tom', '8000', 2015 ),  
(11 , 'Tom', '9000', 2016 ),(12 , 'Sam', '4000', 2016 ),(13 , 'Tom', '5000', 2015 ),  
(14 , 'Tom', '8000', 2015 ),(15 , 'Tom', '9000', 2016 ),(16 , 'Sam', '4000', 2016 ),  
(17 , 'Tom', '9000', 2016 ),(18 , 'Sam', '4000', 2016 ),(19 , 'Tom', '5000', 2015 ),  
(20 , 'Tom', '8000', 2015 ),(21 , 'Tom', '9000', 2016 ),(22 , 'Sam', '4000', 2016 ),  
(23 , 'Tom', '5000', 2015 ),(24 , 'Tom', '8000', 2015 ),(25 , 'Tom', '9000', 2016 ),  
(26 , 'Sam', '4000', 2016 ),(27 , 'Tom', '9000', 2016 ),(28 , 'Sam', '4000', 2016 ),  
(29 , 'Tom', '5000', 2015 ),(30 , 'Tom', '8000', 2015 ),(31 , 'Tom', '9000', 2016 ),  
(32 , 'Sam', '4000', 2016 ),(33 , 'Tom', '5000', 2015 ),(34 , 'Tom', '8000', 2015 ),  
(35 , 'Tom', '9000', 2016 ),(36 , 'Sam', '4000', 2016 ),(37 , 'Tom', '9000', 2016 ),  
(38 , 'Sam', '4000', 2016 ),(39 , 'Tom', '5000', 2015 ),(40 , 'Tom', '8000', 2015 )  
  
  
create procedure b_stu  
(@poll int)  
as  
select EmpID,EmpName,SalaryCalc,case when EmpID=@Poll*10-9 then (select count(*) from dbo.Employee ) END Ycount    
from dbo.employee   
where  EmpID between @Poll*10-9  and @Poll*10   
  
create procedure b_stu1  
(@poll int)  
 as  
 select * from dbo.employee   
 where  EmpID=@Poll*10   
  
  
SELECT obj.Name Storedprocedurename, sc.TEXT Storedprocedurecontent  
FROM syscomments sc   
INNER JOIN sysobjects obj ON sc.Id = obj.ID   
WHERE sc.TEXT LIKE '%dbo.employee%'   
AND TYPE = 'P'  
  
drop table dbo.Employee  
drop procedure b_stu  
drop procedure b_stu1  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

Answer 2

This should get you started:

select  *
from INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tablename%'

Answer 3

Another method:

Select b.name from sys.sql_modules A
inner join sys.procedures b on A.object_id = b.object_id
where a.definition like '%tablename%'

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
Blog
LinkedIn

Answer 4

In addition the other posts, I have an article on my web site that discusses various alternatives: http://www.sommarskog.se/sqlutil/SearchCode.html.

Answer 5

Thanks all for you answers !