Article

How to Find a Stored Procedure Containing Text or String

09 Sep 2016 Kamal Pratap
0 Comments 1463 Views



Many times raise some question when we work on SQL Server.

 

How to find the stored procedure in which particular table is being used?

How to serach the text or string in stored procedure?

How to search the text or string in routine defination of stored procedure?

The same feature can be implemented from the following script described below

 

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%Text%' 
    AND ROUTINE_TYPE='PROCEDURE'

SELECT OBJECT_NAME(id) 
    FROM SYSCOMMENTS 
    WHERE [text] LIKE '%Text%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

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

 

 

Kamal Pratap

I have 6+ years experience in .Net technologies like Asp.Net, C#, WCF, Web Services, SQL Server, Ajax, LinQ. Currently I am working in Netcarrots Loyalty Services as a Software Developer.

Comments

No coments found to display!

Leave a Comment