CODEWITHSUNDEEP
sql-server-2005stored-procedures
Rohit Agarwal
Rohit Agarwal

Reputation: 4289

Text search in Stored Procedures in SQL Server 2005

How do I find a stored procedure containing a certain text? While I understand that the best place to do this kind of searching is through your source control tool, but are there ways to do this in the database?

Upvotes: 8

Views: 12892

Answers (3)

Nalan Madheswaran
Nalan Madheswaran

Reputation: 10562

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules        m 
INNER JOIN sys.objects  o 
    ON m.object_id=o.object_id
WHERE m.definition Like '%Serach_Text%'

Upvotes: 12

Russ Bradberry
Russ Bradberry

Reputation: 10865

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%your text here%' 
    AND ROUTINE_TYPE='PROCEDURE'

Upvotes: 15

Remus Rusanu
Remus Rusanu

Reputation: 294277

You can search sys.sql_modules. Definition contains the text of procedures. The view contains procedures, views, udfs etc. To restrict yourself to stored procedures you should join with sys.procedure on object_id.

Upvotes: 2

Related Questions