Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 7, 2016 7:21 AM
Points: 1,296, Visits: 382
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 @ 12:30 PM
Points: 14,413, Visits: 37,712
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1464142
Posted Monday, June 17, 2013 7:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 7, 2016 7:21 AM
Points: 1,296, Visits: 382
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
Posted Tuesday, May 31, 2016 1:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 31, 2016 3:51 AM
Points: 1, Visits: 1
hi,
to do this i wrote this query:

declare @d varchar(max)='this is my query'
declare @d_trim varchar(max)=replace(replace(replace(replace(REPLACE(@d,CHAR(13),''),char(10),''),' ',''),'[',''),']','')
select *
from
(
select CHARINDEX(t1.name,@d)c,
SUBSTRING(@d,CHARINDEX(t1.name,@d)-5,LEN(t1.name)+10)x,
SUBSTRING(@d,CHARINDEX(t1.name,@d)-1,1)L,
ascii(SUBSTRING(@d,CHARINDEX(t1.name,@d)-1,1))L_ascii,
SUBSTRING(@d,CHARINDEX(t1.name,@d)+LEN(t1.name),1)R,
ascii(SUBSTRING(@d,CHARINDEX(t1.name,@d)+LEN(t1.name),1))R_ascii,
SUBSTRING(@d_trim,CHARINDEX(t1.name,@d_trim)-1,1)S_val,
case when SUBSTRING(@d_trim,CHARINDEX(t1.name,@d_trim)-1,1)in('.') then 1 else 0 end S_exists,
SUBSTRING(@d_trim,CHARINDEX(t1.name,@d_trim)-LEN(t2.name)-1,LEN(t2.name))S,
t1.*,
t2.name sch
--select *
from sys.objects t1
join sys.schemas t2
on t1.schema_id=t2.schema_id
)t1
where type IN ('P','FN', 'IF', 'TF','V','U')
and @d like '%'+name+'%'
--i exclude the objects that could be something like 'object2'/'_object'/...
and L in('','[','.',CHAR(13),char(10))
and R in('',']',CHAR(13),char(10))
--i check the schema if defined
and case S_exists when 1 then S else '' end=case S_exists when 1 then sch else '' end


Post #1790733
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse