2 Replies Latest reply on Mar 2, 2020 10:16 AM by Doan Ninh

    Search for SQL in IDQ Repository

    Marvin Mondejar Guru

      i'm using this query againsts the IDQ repository to find all my SQL Overrides for READ and Lookup Transformations


      Does this do the trick. It seems to find READ but i don't think its pulling from Lookup.

      select

      POD_SQLQUERY, POD_PRESQL, *

      from

          InformaticaModelRepositoryV101.dbo.PO_DATASOURCEOPERATION

      where POD_SQLQUERY is not null or POD_PRESQL is not null

        • 1. Re: Search for SQL in IDQ Repository
          Marvin Mondejar Guru

          this is what i tried:

           

          select t.name, c.name, concat('select  ''', t.name, ''' as tb, ''', c.name, ''' as col, cast(', c.name , ' as nvarchar(200)) as txt from ', t.name ,' where ', c.name ,' like ''%Person%'' union') from

          InformaticaModelRepositoryV101.sys.columns c

          inner join InformaticaModelRepositoryV101.sys.tables t

          on c.object_id = t.object_id

          where 1=1

          and system_type_id IN (99,175,239,231,35,167,241)

          order by t.name

           

          then paste the last column in SSMS, remote the last UNION. I guess the hard part is when you do find a reference, what object is it in.

          • 2. Re: Search for SQL in IDQ Repository
            Doan Ninh New Member

            Hi Marvin,

             

            Please be aware that it is not supported and recommended to run sql queries against IDQ Model repository service database tables.

            You can refer to database view reference to see if there are any external views (MRX) that can help provide the information you are looking for: Preface

            If there are further questions, please open a support case with Informatica support.

             

            Doan