Search Text in Stored Procedures and Tables

To Search in stored procedures

 Collapse | Copy Code

SELECT Name as [Stored Procedure Name]
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Product%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%Code%'

To search in tables

https://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58