How to Search all Stored Procedures in a Database for a Particular String

Hi friends in this post we will discuss How to Search all Stored Procedures in a Database for a Particular String or Search all stored Procedures having a particular string.

I have been asked by my Manager to send him a report all stored procedures created by our vendor. We have already implemented naming pattern for different teams. So it’s easy for me to documented them one by one but length too and most of all boring. So I Googled and find out of this and its a easy one and i think best one.

Earlier we had learnt Send SQL SERVER Query Result by Database Mail, How to Configure Database Mail in SQL Server, Database Mail in SQL Server using GMAIL and Send SQL SERVER Query Result by Database Mail.

Script:

USE DATABASENAME
GO
SELECT OBJECT_NAME(OBJECT_ID), OBJECT_DEFINITION(OBJECT_ID)
FROM SYS.PROCEDURES
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%VEN%'