Post reply

View Tables/Views in the reports

  • December 4, 2014 at 8:51 am

    #397731

    We need to make some changes in our DB tables but don't know how many reports use them on the reporting server?

    Since we have a lot of reports. Is there a way to query it?

  • December 4, 2014 at 9:05 am

    #1762757

    sharonsql2013 (12/4/2014)

    We need to make some changes in our DB tables but don't know how many reports use them on the reporting server?

    Since we have a lot of reports. Is there a way to query it?

    This is one that I use; it runs really slow but it works.

    IF OBJECT_ID('tempdb..#ReportContents') >0

    DROP TABLE #ReportContents

    GO

    -- collect required rows

    SELECT

    [PATH],

    NAME,

    CONVERT(XML,'')AS xml_content,

    CONVERT(VARCHAR(MAX),REPLACE(CONVERT(VARBINARY(MAX),c.Content),'',

    '')) AS [Content]

    INTO

    #ReportContents

    FROM

    dbo.[Catalog] as C

    WHERE

    [C].[Type]=2

    AND LEFT(NAME,1) !='{'

    -- create XML type content

    UPDATE

    [#ReportContents]

    SET

    [xml_content]=CONVERT(XML,content)

    ;WITH

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

    AS ns1),

    ReportDefinition AS (

    SELECT name, path,

    rc.xml_content.query('//ns1:CommandText').value('(/ns1:CommandText)[1]','varchar(max)')

    AS CommandText,

    rc.xml_content.query('//ns1:CommandType').value('(/ns1:CommandType)[1]','varchar(max)')

    AS CommandType

    FROM

    #ReportContents rc)

    SELECT

    [Name] AS [ReportName] ,

    [Path] AS [ReportPath] ,

    CommandText ,

    CommandType

    FROM

    ReportDefinition

    WHERE

    CommandText LIKE '%searchString%' -- change to the table name you're looking for

  • December 4, 2014 at 9:40 am

    #1762771

    Great! Thanks a lot. Will definitely try

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

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