Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 8 April 2015

How to find all the Stored Procedures having a given text in it?


Below screen shot illustrates the difference between these two queries:
Script:
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'cid' + '%'

SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CId' + '%'

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 '%' + 'CId' + '%'
AND TYPE = 'P'

SELECT table_catalog,table_name,column_name,data_type,column_default
FROM information_schema.columns
WHERE column_name LIKE '%' + 'CId' + '%'