Developing Matt

My Technical Journal

What’s the umbrella for?

with 3 comments

I know that I can use sp_helptext to view the text of a single stored procedure, but I would like to know if I ever use the term fadrizzle in any stored procedures.  How can I search all stored procedures? fadrizzle?

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%fadrizzle %’

Turns out this doesn’t work all the time…not sure why yet.  Good news though.  Antonin Foller (www.foller.cz) wrote a script on how to do this very thing: 

http://www.motobit.com/tips/detpg_sql-find-text-stored-procedure/ 
Title="Special stored procedure to find any text inside a source code of all 
stored procedures in one database/server."
Advertisements

Written by matt

August 13, 2009 at 2:26 pm

Posted in T-Sql

3 Responses

Subscribe to comments with RSS.

  1. another way: select ‘DatabaseName’ as db, o.name from DatabaseName.sys.sql_modules m inner join DatabaseName..sysobjects o on m.object_id=o.id where [definition] like ‘%fadrizzle%’

    matt

    December 14, 2011 at 3:49 pm

  2. for a 2000 database you can query select ROUTINE_NAME , routine_description FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%fadrizzle%’

    matt

    December 14, 2011 at 4:32 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: