SQL Server 2005: Locating Stored Procedures that Contain a Keyword

Contributor Icon Contributed by shamanstears Date Icon December 4, 2008  
Tag Icon Tagged: Database

Making changes to a database that has been created by a third pary can be a difficult task, especially when you don’t want to break any of the existing stored procedures. You can search the database’s stored procedures for keywords to find which procedures use a keyword that is involved in your change. This makes it much easier in identifying these procedures so you can make the appropriate changes.

1. Open the SQL Server Management Studio.

2. Go to the toolbar and click the New Query button.

3. Select the desired database.

4. Copy, paste and execute the following:

SELECT sys.sysobjects.name, sys.syscomments.text
FROM sys.sysobjects INNER JOIN syscomments
ON sys.sysobjects.id = sys.syscomments.id
WHERE sys.syscomments.text LIKE '%YourKeyword%'
AND sys.sysobjects.type = 'P'
ORDER BY sys.sysobjects.NAME

Where YourKeyword is replaced with the value you are searching for.

The result set will contain the name and text of each stored procedure that contains the given keyword.

Previous recipe | Next recipe |
 
  • sleakbug
    In SQL 2000 you will need to join together all the 8K records in syscomments for each stored procedure to search within, and beware of your search term breaking across an 8K boundary between 8K records. That will require creating a record that includes the last 1000 chars of one record + the first 1000 chars of the next record to include in the search. You may use less than 1000 chars depending on the size of your search terms.
  • Thanks! good info. Here is the OBJECT_NAME built-in for SS05 and beyond... I'm on ss08 now.

    select OBJECT_NAME(m.object_id), m.definition from sys.sql_modules m where
    Definition like '%YourKeyword%'

    Frankly I like yours better
blog comments powered by Disqus