Searching SQL Server Stored Procedure and Trigger Text

Published 30 September 11 08:16 PM | Scott Mitchell

While I like to consider myself a web developer, every now and then I have to put on my DBA hat to address some SQL related issue. This little script has saved my butt more than once. It searches the text of triggers, UDFs, stored procedures and views for a particular substring, returning the name and type of those database objects that match.

DECLARE @Search varchar(255)
SET @Search='text_to_search'

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 '%'+@Search+'%'
    ORDER BY 2,1

The above script is one of many in my “bag of scripts” I’ve collected over the years. This particular gem was snagged from Stackoverflow: How to find a text inside SQL Server procedures / triggers?

Filed under:

Comments

# Jason Kohlhoff said on September 30, 2011 12:55 PM:

Scott - Have you tried Redgate's free SQL Search add-in for SQL Server Managment Studio (SSMS)?  

www.red-gate.com/.../sql-search

It's awesome!  SSMS should have this feature built in/out of the box.

# RollemIra said on September 30, 2011 02:00 PM:

You may just want to grab a nice free tool for that like this one - www.red-gate.com/.../sql-search

Leave a Comment

(required) 
(required) 
(optional)
(required) 

Archives

My Books

  • Teach Yourself ASP.NET 4 in 24 Hours
  • Teach Yourself ASP.NET 3.5 in 24 Hours
  • Teach Yourself ASP.NET 2.0 in 24 Hours
  • ASP.NET Data Web Controls Kick Start
  • ASP.NET: Tips, Tutorials, and Code
  • Designing Active Server Pages
  • Teach Yourself Active Server Pages 3.0 in 21 Days

I am a Microsoft MVP for ASP.NET.

I am an ASPInsider.