Post reply

Search Report queries

  • March 20, 2009 at 1:32 pm

    #207617

    I am running SQL Server 2005 with 2005 reporting services. I have over 100 reports created and I need a way to search through the queries of the reports for instances where a particular table name or column is being referenced. Is there any way of doing this?

  • March 20, 2009 at 2:08 pm

    #963780

    This comes with a disclaimer... querying the reportserver database is not supported because MS reserves the right to change whatever they want whenever they want. There are other means to get this information like using the SOAP api's and such...

    That said, a quick and dirty solution is this...

    ;WITH XMLNAMESPACES (

    DEFAULT

    'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',

    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd

    )

    SELECT

    name,

    x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,

    x.value('CommandText[1]','VARCHAR(50)') AS CommandText

    FROM (

    select name,

    CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML

    from dbo.Catalog

    ) a

    CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)

    WHERE x.value('CommandText[1]','VARCHAR(50)') LIKE '%YOURTABLENAME%'

    replace YOURTABLENAME with your table name and you should be all set...

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • March 20, 2009 at 2:31 pm

    #963798

    Thanks, I'll give that a try

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

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