reporting-servicessql-server-2012ssrs-2008ssrs-2012

How to find out All tables names & columns list use by SSRS Report


I have 600 SSRS reports deployed into production. How can I get meta data information of reports? Things like which reports use which tables. If any reports use more than 1 table, I am trying to get all tables used by the report.

Is it possible to get such info? Please help me


Solution

  • I had to change the 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' bit to the appropriate year your SSRS is, otherwise you get 0 rows in results.

    -- https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0cbb8997-c54c-4de8-87e8-529dc6e2b017/extract-metadata-from-report-server-database?forum=sqlreportingservices

    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
    SELECT ReportName  = name
        ,DataSetName  = x.value('(@Name)[1]', 'VARCHAR(250)') 
       ,DataSourceName = x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
       ,CommandText  = x.value('(Query/CommandText)[1]','VARCHAR(250)')
       ,Fields   = df.value('(@Name)[1]','VARCHAR(250)')
       ,DataField  = df.value('(DataField)[1]','VARCHAR(250)')
       ,DataType  = df.value('(rd:TypeName)[1]','VARCHAR(250)')
       --,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
     FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
          FROM ReportServer.dbo.Catalog C
         WHERE C.Content is not null
          AND C.Type = 2
       ) a
     CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
     CROSS APPLY x.nodes('Fields/Field') f(df) 
    ORDER BY name