Ever wanted to search your stored procedures for a piece of text?
If like me you have a poor memory and you forget where you wrote that wicked piece of code this is the post for you.
The other day I was looking for some code I have written in a Stored Procedure but I couldn’t remember which SP the code was in.
I personally think that this is functionality that Microsoft has missed the boat on, I should be a standard feature but it’s not.
A colleague found s a way round it.
I knew my SP had the text “Service Team” inside it but that was about all I could remember.
Open the SQL Server Management Studio and go to the correct database.
Open a new query and paste this code in. You will need to change the search text to suit your needs but remember to keep the % signs otherwise the search will only find exact matches.
SELECT OBJECT_NAME(ID) FROM syscomments WHERE [text] like '%Service Team%' AND OBJECTPROPERTY(ID,'IsProcedure') = 1 GROUP BY OBJECT_NAME(ID)
The only downfall for this code is that you will have to run it on each database on your server.
