How to find column name within stored procedures in sql server

Introduction

Sometime it's very useful if we can search word in Stored Procedure for debugging purposes.Last week, i need to find a column name within stored procedures, then i do the goggling, but i could not find any useful information to find.so i try to compose a simple query to find the script. scripts is simple and small but result is effective and most useful script for developers and database administrators.

Sql script

Use below script to search with stored procedure using table called "tblLookup"

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tblLookup%'
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME ASC

Now i would like to make standard stored procedure to seach any column names in stored procedures.here is that for you.

create proc spFindWordsInSP
@word nvarchar(100)
as
BEGIN

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@word+'%'
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME ASC
END

Now i have run created procedures to find the userID columns in all my procedures,

exec spFindWordsInSP 'userID'

Output

In above result, we can get the name of procedure and script of composed procedure.i hope this is help and save lots times.thank you for reading.