Find SQL Server Stored Procedure or Function by Contents

Find SQL Server Stored Procedure or Function By Contents

Problem

I was working making changes to a table function that is in a SQL Server database. This update was being made on an application that previously existed. Thus I did not have extensive knowledge of the database structure and each time I would add a feature to the application, I would have to research how things were previously done since we are using an existing database.

One aspect of this was that I wanted to find whether a table function was being used by more than one stored procedure. A function called by a stored procedure? Yes... do not ask. I did not write the original code, but am trying to make improvements where I can with the replacement applicatoin. That said, I did not know of a command that would provide me this information.

Solution

After doing some internet research for this type of problem, I did find a query that would get me what I needed. The query is:

The Query

select * 
from sys.sql_modules m
inner join sys.objects o
on o.object_id = m.object_id
where m.definition like '<text>'

Replace <text> with the name of or the contents of the procedure or function. This will return a result set that shows more detail than what you will need, including the object ID and then some.

In my case, it returned 3 rows... the 2 stored procedures that called the function and the function itself. This gave me the information that I needed to make sure that the change that I was about to make, would only impact the area of the application that I was working on.