Caveman's Blog

My commitment to learning.

SQL Server: How to search for text/phrase in a Stored Procedure?

with one comment


Following are some of the queries to find the stored procedures that contain a search text/phrase:


SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%employee%' AND ROUTINE_TYPE IN ('PROCEDURE')

--OR

SELECT OBJECT_NAME(id) FROM syscomments
WHERE [text] LIKE '%employee%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1

--OR

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%employee%' and OBJECTPROPERTY(object_id, 'IsProcedure') = 1

Following some more “How to search” posts:

1. SQL Server: How to search for a number in a string?
2. SQL Server: how to search for a table name or a column name or a given stored procedure name?

Advertisements

One Response

Subscribe to comments with RSS.

  1. […] SQL Server: How to search for text/phrase in a StoredĀ Procedure? […]


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: