Find all the tables and columns used in SSRS reports

SQL DBA 0 Reputation points
2023-07-17T18:54:32.89+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,238 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,726 questions
{count} votes