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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:38 AM
Points: 386, Visits: 228
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 #1464137
Posted Monday, June 17, 2013 7:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 12,741, Visits: 31,053
ksatpute123 (6/17/2013)
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?


in this kind of unusual instance, i'd take the outputted query,a nd turn it into a view, and then get the dependencies from the view;
the other thing you can do is get the execution plan for the query;
it will have all the dependencies in it if you look; for example, here's a example snippet from one of mine:
              <OutputList>
<ColumnReference Database="[LHC_InitialImport]" Schema="[dbo]" Table="[GMAALLOCDATA]" Column="AALLOCTBLKEY" />
<ColumnReference Database="[LHC_InitialImport]" Schema="[dbo]" Table="[GMAALLOCDATA]" Column="SOURCETBLKEY" />
<ColumnReference Database="[LHC_InitialImport]" Schema="[dbo]" Table="[GMAALLOCDATA]" Column="YEARTBLKEY" />
<ColumnReference Database="[LHC_InitialImport]" Schema="[dbo]" Table="[GMAALLOCDATA]" Column="ESTIMATEDPIAMT" />
<ColumnReference Database="[LHC_InitialImport]" Schema="[dbo]" Table="[GMSOURCE]" Column="SOURCENAME" />
<ColumnReference Database="[LHC_InitialImport]" Schema="[dbo]" Table="[TBYEAR]" Column="DESCRIP" />
<ColumnReference Database="[LHC_InitialImport]" Schema="[dbo]" Table="[GMAALLOCDATA]" Column="ALLOCAMT" />



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1464142
Posted Monday, June 17, 2013 7:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:38 AM
Points: 386, Visits: 228
Thanks for such a quick reply. I have already tried this method. It is effective when I have to go through small pool of db objects with dynamic queries.

In my case I may have a entire layer of procedures most of them dynamic queries. It is not feasible for me to go through each and every object and manually map the dependencies.
Post #1464147
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse