Friday, May 17, 2024
HomeDatabaseSQL Server 2005: Locating Stored Procedures that Contain a Keyword

SQL Server 2005: Locating Stored Procedures that Contain a Keyword

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.syscomments.text
FROM sys.sysobjects INNER JOIN syscomments
ON =
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.

Rob Rogers
Rob Rogers
Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.


Please enter your comment!
Please enter your name here

Most Popular


Recent Comments

error: Content is protected !!