Ive tested this on 2008 and changed report definition SCHEMA to http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition but I still do not get all the reports that uses the column Im looking for. Is there anything else I need to modify on the script for 2008 version?

If you can find out a report that is 'supposed to show up in the query results' and 'does not show up', please send me the report file definition. I can then figure out why it is not coming up in the query.

I reviewed your file and found that the column you are looking for is not actually used in the report data source. However, it is part of the query text. I posted an example that shows how to search the query text in the original post: http://beyondrelational.com/ask/jacob/questions/11/finding-all-reports-referencing-a-certain-fieldcolumn-on-report-server.aspx

In the where clause you have = sql:variable("@FieldToSearch") What is the syntax for a Like statement?

For LIKE search, you can use contains(). Here is the modified version of the WHERE clause: WHERE df.exist('DataField[ contains(., sql:variable("@FieldToSearch"))]') = 1

Jacob, Your query works beautifully!

To enhance the search, how would I search the query used in each report for a wildcard field? So I have 1000 reports and I would like to see how many times field 'widget1' is used in any report. Basically I want to see if field 'widget1' was used anywhere in any query to make a report.

Thanks, Brady

P.S. Tip for others, to be sure what version of report definition SCHEMA(http://schemas.microsoft.com) you need to use for your particular report simply open your rdl in notepad and you will see it in the very top.

Are you trying to figure out whether the field 'widget1' is used in a query used by a report, or whether that field is actually used in a report?

Is there a way to query all reports that have a specific text in the inline query? using (Like) Thanks

THANK-YOU SO MUCH FOR SHARING YOUR KNOWLEDGE TO THE WORLD!

This is incredibly advanced code; I don’t know anyone who could develop this code. I joined this web-site just to let you know that I really appreciate your blog!

Everything works for me but the report column names. I need to extract the report field names in addition to the data field names. When I run this code the data field names and report field names both result to the data field names. I’m using 2008. Any suggestions?

;WITH XMLNAMESPACES (
    DEFAULT 
    'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
    name,
    d.value('@Name[1]', 'VARCHAR(50)') AS DataSetName,

    ??--df.value(('DataField[ . = ('@Name[1]', 'VARCHAR(50)') ]'), 'VARCHAR(50)')AS ReportFieldName,


    df.value('DataField[1]', 'VARCHAR(50)') AS DataFieldName
FROM (
    select name, 
    CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
    from dbo.Catalog
    WHERE Type = 2
    AND [Path]like '/Policy_Reports%' ) a

CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r(d)
CROSS APPLY d.nodes('Fields/Field') f(df)

@abc,

What do you mean by report column names? Are you referring to the column headers in the output grid? It is helpful if you can send me an RDL file to jacob at beyondrelational.com and show me what is the output you are expecting.