-1

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?

Improve this question
1

2 Answers 2

Reset to default
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...

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

thanks for reply .I am looking for 2012 edition.
Are you sure you're in the right database? Are you sure there is a procedure that has tvfname in it?
Yes i am sure .I checked twice.
@user3203331 well, I don't know what to tell you, I've validated that this code works in my database.
@AaronBertrand, could be all the instances where it is used are in dynamic sql.
|
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

Improve this answer

5 Comments

One of the reasons to not use INFORMATION_SCHEMA in the first place. Also note that the OBJECT_ functions do not obey isolation semantics, so could block even if you use things like NOLOCK.
@Aaron, is this of serious consequence in a typical DEV environment? Or a local DEV environment?
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?)
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.
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.