Find all the tables and columns used in SSRS reports

Question

Is there a way to find all the tables and columns used in SSRS reports? Below query gives the column names but not the table names.

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_ATLSQL03.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

Answer 1

Hi @SQL DBA,

Check it out here: https://www.mssqltips.com/sqlservertip/3693/find-tables-columns-or-stored-procedures-used-in-a-sql-server-reporting-services-report/