Search for specific text inside a stored procedure.

Search for specific text inside a stored procedure.

Problem:

Whenever there is a change in column name or table name or schema name we want to modify the stored procedures to reflect the same and look if the stored procedure has the text you are looking for

Solution:

SQL server stores the text of stored procedure inside system tables and we can view the definition of an object using few different ways.

i.                    SYS.PROCEDURES

ii.                   INFORMATION_SCHEMA.ROUTINES

iii.                 SYS.SQL_MODULES

iv.                 SYS.SYSCOMMENTS

Using all these system views we can search for the specific text you want to look for in each stored procedure. For example, I want to look all the store procedures inside my DB having a BEGIN TRANSACTION.

--Checking the text of all the procedure to see search conditions

SELECT ROUTINE_NAME, ROUTINE_DEFINITION

  FROM INFORMATION_SCHEMA.ROUTINES

  WHERE ROUTINE_DEFINITION  LIKE '%BEGIN TRANSACTION%' 

    AND ROUTINE_TYPE='PROCEDURE'

--Checking the text of all the procedure to see how many times a word has been specifed

declare @word varchar(128)

set @word = 'BEGIN TRANSACTION'

select name, (len(object_definition(object_id)) -  len(replace(object_definition(object_id), @word, ''))) / len (@word) as qty

from sys.procedures

where object_definition(object_id) like '%'+@word+'%' and type = 'P'

order by qty desc;

-- using sys.sql_modules

select * from sys.sql_modules where  object_definition(object_id) like '%BEGIN TRANSACTION%'

-- using sys.sql_comments

select * from syscomments where  text like '%BEGIN TRANSACTION%'