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
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:02 AM
Points: 511, Visits: 249
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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 9:39 AM
Points: 23, Visits: 109
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
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:02 AM
Points: 511, Visits: 249
@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