Extract SSRS reports objects

 The script below is:

·       Retrieving object names from Data Sources of SSRS reports stored as XML on Report Server database

·       Selecting these objects definitions from information schema of the desirable database

·       Searching a keyword in the report definition

;WITH
     XMLNAMESPACES
         (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
                 ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
          AS rd)
    ,DEF AS
        (SELECT RPT.ItemID
                ,RPT.Name
                ,RPT.ReportPath
                ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName
                ,CommandText = REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)'))),'&gt;', '>'),'&lt;', '<')
         FROM (SELECT RPT.ItemID
                        ,RPT.Name
                        ,RPT.Path AS ReportPath
                     ,RPT.name AS ReportName
                     ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
               FROM ReportServer.dbo.[Catalog] AS RPT
               WHERE RPT.Type in (2, 5, 7, 8)
             ) AS RPT
         CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode))
    SELECT   DEF.Name ReportName
            ,DEF.ReportPath
            ,DEF.DataSetName
            ,DEF.CommandText
            ,o.OBJECT_NAME
            ,o.OBJECT_DEFINITION
    FROM DEF
        ,(select o.name as OBJECT_NAME
                , m.definition as OBJECT_DEFINITION
            from sys.sql_modules m
            inner join sys.objects o on o.object_id = m.object_id
            WHERE (o.type in ('P','V','TF','IF','FN')
            AND (m.definition Like '%fld_current%'
            OR m.definition Like '%originalcost%')
            )
    ) 

    WHERE DEF.CommandText like '%' + o.object_name + '%'

If you find this script useful please donate generously.