Find SQL Server Stored Procedure or Function By Contents
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.
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:
select * from sys.sql_modules m inner join sys.objects o on o.object_id = m.object_id where m.definition like '<text>'
<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.