There might be times when you simply need to search through the objects in a SQL Server database for a specific word or text.
It would be a very tedious task to open each stored procedure, function, View, etc. and look through each to see if it references the text you are looking for.
And that “word or text” can be anything. Maybe it’s the name of a column, or the name of a table, or a specific user defined data type.
There is one simple query you can run to find all references to a specific text within the definition of any stored procedure (or any other database object)
Here’s the query:
/*
Some TYPE values that can be used:
P - Stored procedure
U - User defined table
FN - Scalar function
IF - Inline Table Valued function
TF - Multi statement table value function
V - View
*/
SELECT schem.name as 'Schema', obj.name as 'Object', obj.type
FROM sys.objects as obj
INNER JOIN syscomments as comms
ON obj.object_id = comms.id
INNER JOIN sys.schemas as schem
ON obj.schema_id = schem.schema_id
WHERE obj.type IN ('P')
AND comms.text like '%TEXT_TO_LOOK_FOR%'
ORDER BY obj.name
This query simply utilizes a few handy system tables to search through all the stored procedures in our database looking for procedures that contain a specific word or text.
You would replace TEXT_TO_LOOK_FOR with the actual text you want to search for. Notice the text needs to be wrapped around the percent wildcard characters.
I set up this query to look specifically within stored procedures. This is done by outlining a type of ‘P‘, which is what we see in the WHERE clause: WHERE obj.type IN ('P')
In the comment block above the query, I have outlined several other type codes you can outline in your WHERE clause. Understand that this list is not complete. There are several other type codes that can be used. For the full list, check out the official Microsoft documentation.
If you didn’t want to filter on specific types, you could definitely leave off that part of the WHERE clause altogether and it will return references in any and all types of database objects.
An example of finding text in a stored procedure
Take a look at the following Books table:
Let’s say we want to change the name of a column in this table. We want to change the Pages column to say NumberOfPages.
This is very easy to do using the SP_RENAME system stored procedure:
Great, now our column name is changed, but understand that any references to the old name have not been changed. We are responsible for finding those references and changing them.
There are many different database objects that can reference a column name, but we’ll focus on stored procedures at the moment. We can use our handy query to locate any stored procedure containing the word ‘Pages‘ somewhere in the definition of the procedure:
It looks like in my database, there is one stored procedure that references the word ‘Pages‘. We can find that stored procedure in the Object Explorer and see it’s definition by right-clicking and choosing Modify:
We see there is, in fact, a reference to that old column name:
Yep, we need to change that reference!
Remember, there are other database objects you should consider
The great thing about this query is that we can very easily use it to search through other database objects besides stored procedures. We can add more type codes to the WHERE clause to include objects like User Defined Functions or Views. Take a look at this instance of the query that looks for instances of the word ‘Qty‘:
Looks like there are several objects that reference the word ‘Qty‘, and none of them are stored procedures.
So just remember that if you are using this query to find all instances of something, you shouldn’t limit yourself to just looking through stored procedures. Make sure you look in other objects created and used in your database as well!
Next Steps:
Leave a comment if you found this tutorial helpful!
If you found this tutorial helpful, you should consider downloading the following FREE Ebook:
FREE Ebook on SQL Server Stored Procedures!
This FREE Ebook discusses all the key points you need to understand about stored procedures in Microsoft SQL Server. The query discussed in this tutorial can be found in the Ebook. It will definitely be an excellent resource for you to keep and reference throughout your career as a data professional. Make sure you get it today!
Do you know how to change a column name in SQL Server? Click the link to find out!
Stored procedures are a very common thing in SQL Server. As a database professional, I’d say you will probably encounter a stored procedure nearly every single day! I put together a full beginner-friendly tutorial on everything you need to know about stored procedures that you should also read up on:
SQL Server Stored Procedures: The Ultimate Guide for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
