Easy way to search SQL Procedures?

Question

I'm a new developer trying to add new functionality to an old project. The new code I'm trying to implement involves database tables, so I'm looking for the procedure that is called to update or originally populate the table.

But there's over 50 procedures.

Short of opening every procedure manually and using CTRL+F to search for the term I'm looking for, is there an easier way to scan stored procedures for a search term?

Answer 1

You can use the sys.syscomments view to read the text.

SELECT Distinct SO.Name, sc.text, so.type
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SC.Text LIKE '%your_string%'
ORDER BY SO.Name

Answer 2

If you use SQL Server Management Studio, you can right-click on the table and select "Show dependencies" (My Management Studio is in German...I don't know if it's exactly called "Show dependencies" in English).

A screen will pop up where you can view all objects that are dependent from this table.
If stored procedures are listed, it means that they use this table.

Answer 3

Try this:

SELECT OBJECT_NAME(id) 
    FROM syscomments 
    WHERE [text] LIKE '%foobar%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

This only search for Stored Procs, which the other methods posted don't seem to do.

According to MSDN syscomments...

Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database.

So if you don't specify that you only want to search Stored Procs you'll get a lot of other stuff you are not interested in.

Answer 4

create procedure [dbo].[uspUtilitySearchText]
(
    @txt varchar(2048)
)
AS
SET NOCOUNT ON

declare @srch varchar(3000)

set @srch = '%' + @txt + '%'

SELECT 
    o.type_desc AS ROUTINE_TYPE,
    o.[name] AS ROUTINE_NAME,
    m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m
    INNER JOIN sys.objects AS o
        ON m.object_id = o.object_id
WHERE m.definition LIKE @srch

then

exec uspUtilitySearchText 'tablename'

this searches all database objects for the given text

Answer 5

Select * from syscomments where [text] like '%table_name%'

Or use a program that can script all Non Data objects from a database and then Grep Them.