How to find dependencies in dynamic sql queries

Question

How to find dependencies in dynamic sql queries

ksatpute123

Hall of Fame

Points: 3325
More actions
June 17, 2013 at 7:25 am

#276349

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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

ksatpute123 Hall of Fame Points: 3325 More actions · Answer 2

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.

concorsomusic SSC Journeyman Points: 93 More actions · Answer 3

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