This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.
When working on large databases there can in some project be hundreds or even thousands of stored procedures, so if your task is to find all stored procedures that query a specific table then finding it could be quite a headache.
This query below has helped me many times and I hope it will help you too.
DECLARE @searchText VARCAHR(200) = 'customer' SELECT name AS [Stored procedure name] FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+ @searchText +'%' AND [type]='P' ORDER BY name