Hello Friends,
Sometime we want to search a particular column or some text in entire stored procedures. For this we can utilize following two simple approaches
Approach 1:-
BEGIN TRY
DECLARE @strColumn VARCHAR(1000)
SET @strColumn ='Rajat'
SELECT DISTINCT o.name
FROM sys.syscomments c
INNER JOIN sys.objects o ON o.object_Id = c.Id
AND o.type ='P'
WHERE text like '%' + @strColumn +'%'
ORDER BY o.NAME
END TRY
BEGIN CATCH
SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()
END CATCH
Approach 2:-
BEGIN TRY
DECLARE @strColumn VARCHAR(1000)
SET @strColumn ='Rajat'
SELECT SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE= 'PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%' + @strColumn +'%'
ORDER BY SPECIFIC_NAME
END TRY
BEGIN CATCH
SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()
END CATCH
Hope this will helpful to you somewhere.
Thanks
Rajat Jaiswal
