2

I need to search for a string inside a stored procedure. This is created by user. So I cannot use this: 

SELECT OBJECT_NAME(object_id), AS MYSPDEFINITION
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%string%';

I have a column which has stored procedures, example: "execute salarybyid" and so on, in each and every row up to 200. How to find if these stored procedures have a specific string in them?

Thiago Marcello
  • 343
  • 2
  • 10
Codelover
  • 21
  • 1
  • 1
  • 4
  • Possible duplicate of [How do I find a stored procedure containing ?](http://stackoverflow.com/questions/5079457/how-do-i-find-a-stored-procedure-containing-text) – Siyual May 11 '16 at 19:47
  • I'm confused - you want to find the string within the actual stored procedures or in the column which contains the script to _execute_ stored procedures? – D Stanley May 11 '16 at 22:02

2 Answers2

3

Try like this,

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION like '%String%'

However, it will only show the first 4000 characters in the editor.

Your query would give the entire routine

SELECT OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%String%'
StackUser
  • 5,370
  • 2
  • 24
  • 44
0

Try something like......

SELECT OBJECT_NAME(OBJECT_ID) Proc_Name
      ,definition
FROM sys.sql_modules
WHERE OBJECTPROPERTY(OBJECT_ID, 'isprocedure') = 1
 AND definition LIKE '%String%'
M.Ali
  • 67,945
  • 13
  • 101
  • 127