Find SQL Server Stored Procedures Containing Some Text

Relatively basic stuff here, but I do like to stick to the KISS rule. I am often required to search SQL Server's stored procedures to find out which ones refer to specific fields, tables, or even variable names. Recently, I had to refactor several stored procedures defined a specific alias for a column. Being the geek that I am, I wrote a little stored proc helper routine that quickly came up with the results I needed. In my case, there were over 1000 procs to search and my results turned up 25 procs to refactor.

CREATE PROCEDURE sp_find_procs_containing
    @search VARCHAR(100) = ''
AS
SET @search = '%' + @search + '%'
SELECT    
    ROUTINE_NAME,
    ROUTINE_DEFINITION
FROM    
    INFORMATION_SCHEMA.ROUTINES
WHERE    
    ROUTINE_DEFINITION LIKE @search
ORDER BY
    ROUTINE_NAME
GO

If you invoke this proc and pass it the phrase you are looking for in the proc, it will return them in sorted order. Not brain surgery, just something to help me code faster.

As you can tell, I like the INFORMATION_SCHEMA views. Here is another post I put up a while back on using them.

Tweet Post Share Update RSS

Hi, I'm John Papa. I author this blog, create courses for Pluralsight and am a Google Developer Expert and Microsoft Regional Director. I travel speaking at events and train technology thought leaders

Tags:
All