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.
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)
Hey - we came up with different answers, though, so it's all good...
I'm surprised you didn't pick up on the lack of capitalisation of `LIKE`, and the mangling of the cases in `Object_id`... ;-)
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.
13 People are following this question.
