0

I am working on a TVF that is used in multiple stored procedures. How can I find all of the stored procedures that are using that TVF?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
user3203331
  • 429
  • 2
  • 7
  • 23
  • Here's a related post. http://stackoverflow.com/questions/14704105/search-text-in-stored-procedure-in-sql – xspydr Jan 17 '14 at 18:58

2 Answers2

4

You can scour the metadata for mentions of your function, keeping in mind that it can produce false positives if this is a common name mentioned elsewhere or in comments, or miss instances if the function call is built up using dynamic SQL.

SELECT s.name, p.name FROM sys.procedures AS p
  INNER JOIN sys.schemas AS s
  ON p.[schema_id] = s.[schema_id]
  INNER JOIN sys.sql_modules AS m
  ON p.[object_id] = m.[object_id]
  WHERE m.definition LIKE N'%tvfname%';

There are other ways in 2012, but since the question is also tagged 2005...

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
1

I've successfully used this;

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) LIKE '%TVFn%'

It is best to not search the ROUTINE_DEFINITION field of the view as it is capped. PS - Cannot test on 2005, sorry

MarkD
  • 5,276
  • 1
  • 14
  • 22
  • One of the reasons to [not use `INFORMATION_SCHEMA` in the first place](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx). Also note that [the `OBJECT_` functions do not obey isolation semantics](http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=432497), so could block even if you use things like `NOLOCK`. – Aaron Bertrand Jan 17 '14 at 21:10
  • 1
    @Aaron, is this of serious consequence in a typical DEV environment? Or a local DEV environment? – MarkD Jan 18 '14 at 05:50
  • Who said this is a dev environment? Also answers here aren't just for the OP, they're for all future readers too. When there is something you should stay away from in SQL Server in some cases, why only use it in the cases where it might be okay (today)? You then rely on the reader to have the same knowledge as you, and know when to use them and when to not use them. Much safer to use (and teach to use) the catalog views all the time, especially since - while you've mentioned one limitation - you've missed many. (Did you read the link?) – Aaron Bertrand Jan 18 '14 at 13:54
  • Finally, note that I didn't down-vote, just added a clarifying comment so readers can make their own decisions with more info than you've provided. When people get in the habit of using INFORMATION_SCHEMA or these blocking functions, they may not know what's going on 6 months from now when they do go wrong in production, during an emergency, etc. I prefer to teach people to fish with proper bait and tackle than dynamite, because I don't know that they'll always use the latter properly. – Aaron Bertrand Jan 18 '14 at 13:57
  • I did read the link and was unaware of the issues you brought to light - thank you. In my limited experience, this sort of operation has been used primarily in DEV environments - hence my question. – MarkD Jan 19 '14 at 06:33