forum.venkateswarlu.net
      Register      Login
How to find Stored Procedures having particular text

3 replies to this topic

Jahnavi N #1
Member
164 Points
 Posted on 23 Aug 2013 02:13 PM IST
Hi,

How to find the list of stored procedures having particular text/ column name/ table name?
 
SQL Server     691 views     Reply to this topic
Cherukuri Venkateswarlu #2
Member
140 Points
 Replied on 23 Aug 2013 02:18 PM IST
List of all Stored Procedures having particular string or text or column name in SQL Server

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Your Coulmna name/text' + '%'
AND TYPE = 'P' 

1) Search in All Objects like Tables, Stored Procedures, views etc.,

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'Your Column name/text' + '%' 

 2) Search in Stored Procedure

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'Your Column name/text' + '%'

 
Reply to this topic
Subramaniam R #3
Member
152 Points
 Replied on 28 Aug 2013 04:32 PM IST SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%your Text%' 
Reply to this topic
Aisha Bobby #4
Member
96 Points
 Replied on 30 Aug 2013 05:46 PM IST

Hi, the above queries has been executed successfully for me..

Thanks Venkateswarlu.

 
Reply to this topic