Searching for text in a stored procedure – SQL Server 2012

For years, I have depended on some handy code called sp_search_code to search for text in stored procedures, views, and functions. It was written by Narayana Vyas Kondreddi who has a website at http://vyaskn.tripod.com. However, I just used it in SQL Server 2012 and it didn’t work. So I came across some code at Code Project, which will find text in a stored procedure. I’ve written a simple stored procedure using that code and am providing it here. Since right now it only searches stored procedures it is not as useful as sp_search_code, but it’s a start:

create procedure dbo.SearchCode
@Search varchar(100)
as

SELECT Name as [Stored Procedure Name]
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%’ + @Search + ‘%’

Posted in All