June 17, 2013 at 7:25 am
I think it is far fetched because after searching the internet for days I found nothing.
I want to find out all the object referring the columns in a table. I have a fantastic working query for the same but it does not list objects with dynamic SQL.
Is there any way with which we can find dependencies in dynamic sql queries?
June 18, 2013 at 2:30 pm
There are no "dependencies" per se for dynamic SQL because it's just text literals, so you have no defined dependencies to search on directly.
As far as I know the only way is to perform some sort of search on the text of the stored procedures, like....
DECLARE @Search varchar(255)
SET @Search='INPUT_SEARCH_HERE'
SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,1;
June 18, 2013 at 2:32 pm
what is your objective?
Do you want to apply merge statement or what?
June 19, 2013 at 12:50 am
Thanks for the reply....
I am using the search mechanism only, I have a large number of db objects with dynamic sql queries so you can understand how difficult and time consuming it is to manually map dependencies for all such objects.
I understand that there is no dependency for text literals but I am being hopeful here.
I am mapping all the dependencies in my database for migration and streamlining purpose. It happened to be the case that the database already had an entire layer of procedures with dynamic sql queries.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply