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

Question

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

Answer 1

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