question

ajit.ghule avatar image
ajit.ghule asked

dependency of db objects in dynamic query

Hello all, I have number of sql stored procedures in which I am constructing the query dynamically and executing those. In this dynamic queries , i am using sql scaler function say 'Myfunction'. Now I want to find all stored procedures which are using this scaler function 'Myfunction' Can annbody please let me know how can i find these ? Remember , I am using function 'Myfunction' in dynamic query of stored procedure. Please let me know. Thanks in advance.
sql-server-2008stored-proceduresdynamic-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Assuming your SP is building the query dynamically and the SP name isn't being populated by a parameter to the SP, or by reading a value from a table, then the name should be held within the stored procedure definition itself. So all you need to do is to search `sys.all_sql_modules` for `Myfunction`: SELECT OBJECT_NAME(Object_id), * FROM sys.all_sql_modules WHERE definition like '%MyFunction%' (the `OBJECT_NAME(object_id)` bit gives you the name of the procedure or view)
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Doggone it, quite typing faster than me. It's irritating.
0 Likes 0 ·
Hey - we came up with different answers, though, so it's all good...
0 Likes 0 ·
You forgot the semi-colon at the end of the statement.
0 Likes 0 ·
I'm surprised you didn't pick up on the lack of capitalisation of `LIKE`, and the mangling of the cases in `Object_id`... ;-)
0 Likes 0 ·
I was saving that for a later comment. Ha!
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Since you're not building this in a traditional fashion with the code, your only option is going to be to search the text of the objects themselves. Try this: SELECT r.SPECIFIC_SCHEMA, r.SPECIFIC_NAME, r.ROUTINE_TYPE, r.ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES AS r WHERE r.ROUTINE_DEFINITION LIKE '%MyFunction%'; It's not going to be terribly fast or efficient, but you'll be able to identify where the function has been referenced within your code.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.