Searching a Stored Procedure for Text

Story time. Let’s say a database server is receiving a new release that include a change to a stored procedure. All of the databases are supposed to get the changes but one way or another there are problems with the release and it has to be stopped part of the way through. Maybe some changes got rolled back but others weren’t rolled back. We don’t have accurate logging of what databases have been updated but we want to know if a stored procedure is on the old version or received the new version.

How do we check for these changes?

First, we need a query to search the stored procedures in a database. We can do that with the following query:

SELECT O.name, O.create_date, O.modify_date, m.definition
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%SEARCH_TEXT%';
GO

Going with our example, if we want to confirm a database has had ExampleProc updated to version 1.2.3 and that’s specified in the stored procedure, we might see the following when replacing SEARCH_TEXT in the script above with 1.2.3:

Beyond One Database

That gets us far enough to see what’s in one database but we have a whole server of databases that we’re trying to search. Do we need to run that script on each individual database one at a time? You can if you want to but I wouldn’t recommend wasting that much time. Instead, I would recommend combining the script above with Aaron Bertrand’s sp_ineachdb.

You may be familiar with the system procedure sp_MSforeachdb that is available in SQL Server by default. What you may not know is that even though it exists and can be found across many sources as a solution to running a query against all databases, it is not actually supported. Furthermore, there are circumstances where, even though it says “foreachdb” right in the name, databases will be skipped. Microsoft isn’t going to spend time on fixing it since it’s not officially support which means you can either write your own variation to get the job done or go with sp_ineachdb.

Using sp_ineachdb would look something like this for our example:

EXEC dbo.sp_ineachdb @user_only = 1, @command = N'SELECT DB_NAME() AS ''Database'',O.name, O.create_date, O.modify_date, m.definition
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE ''%1.2.3%'';';

You can also use @select_dbname=1 with sp_ineachdb to see each database your script ran against:

Do your stored procedures include a version number in the comments or somewhere else within the script? Is there a date along with a short description of what changed in each iteration of the stored procedure? If so, those can be good options to use when searching. If all else fails, search for a string of text that only exists in the new procedure.

Thanks for reading!