How to Search a Text/String in SQL Stored Procedure

Recently I found a problem with Symmetric Key, fixed it in one of the stored procedures. I had to find similar problem in all Stored Procedures in the database, had to search for a string all the Procedures. Following query helps to find the same!

-- -- Searching for Symmetric text in all the Stored Procedures of given database

-- Method 1
SELECT Name, OBJECT_DEFINITION(OBJECT_ID)
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Symmetric%'

-- Method 2
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE '%Symmetric%'

-- Method 3
SELECT OBJECT_NAME(object_id), *
FROM sys.sql_modules
WHERE definition LIKE '%Symmetric%'

-- -- This can search in limitted number of characters, DO NOT Use this
--SELECT ROUTINE_NAME, ROUTINE_DEFINITION
--FROM INFORMATION_SCHEMA.ROUTINES
--WHERE ROUTINE_TYPE='PROCEDURE'
--ND ROUTINE_DEFINITION LIKE '%Employee%'

Similar Posts:
https://vijredblog.wordpress.com/2014/04/01/sql-query-execution-history-from-cache-plans/
https://vijredblog.wordpress.com/2014/04/15/sql-dependency-analysis-identify-impacted-modules/

Advertisements
This entry was posted in SQL, SQL Query and tagged , , , . Bookmark the permalink.

2 Responses to How to Search a Text/String in SQL Stored Procedure

  1. Pingback: SQL query execution history from cache plans | Blog for reference – Vijred

  2. Pingback: SQL-dependency analysis-Identify impacted modules | Blog for reference – Vijred

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