Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find dependencies in dynamic sql queries Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 7:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:05 PM
Points: 880, Visits: 336
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?
Post #1464136
Posted Tuesday, June 18, 2013 2:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:19 PM
Points: 25, Visits: 116
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;

Post #1464876
Posted Tuesday, June 18, 2013 2:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
what is your objective?

Do you want to apply merge statement or what?
Post #1464877
Posted Wednesday, June 19, 2013 12:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:05 PM
Points: 880, Visits: 336
@MarbryHardin

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.

@Learner44

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.
Post #1464962
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse