How to find dependencies in dynamic sql queries

  • 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?

  • 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!

  • 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.

  • 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

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

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