Finding out the stored procedure sql text from the sys catalog

Question

Hi I need to search thru SQL within the stored procedure. Do you know which table in the sys catalog i can query for that?

Answer 1

What is that you look to search at the SP level ? Can you make it clear

---

Thanks, Leks

Answer 2

I need to search within the code, within the sql. For example: if employee_status = 'TERMINATED' I need to query something like:
select procedure_Name, 
         procedure_text
from   sys.xxx
where procedure_text like '%TERMINATED%'

Answer 3

Hi HJaneiro

You can use the INFORMATION_SCHEMA views for this.

If you don't want the text of the SP displayed, comment out the object_definition from the select

select distinct
  routine_schema + '.' + routine_name AS [Object]
, routine_type AS [Object Type]
, object_definition(object_id(routine_schema + '.' + routine_name)) AS 'Object Definition'
from information_schema.routines
 where object_definition(object_id(routine_schema + '.' + routine_name)) like '%' +
'<<<MySearchStringHere>>>'
 + '%'

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 4

Alternatively, you could use the following code.  Both examples will return the correct information.  

SELECT SCHEMA_NAME (p.schema_id) AS schema_name, OBJECT_NAME(m.object_id) AS name, m.definition
FROM sys.sql_modules AS m
INNER JOIN sys.procedures AS p ON m.object_id = p.object_id 
WHERE definition LIKE '%search text%'

For additional examples using the system catalogs, see this Books Online topic: http://msdn.microsoft.com/en-us/library/ms345522(SQL.90).aspx

Kind regards,
Gail

---

Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights