Extract metadata about reports (T-SQL)

Introduction

This T-SQL script will extract metadata about reports.

Scenarios

You can use this script for reference when you want to find information about dataset, data source of a report.

Script

You can use this script in this way:

1. Open SQL Server Management Studio (SSMS) and connect SQL Server.

2. Copy the code from ReportInfo.sql and run the script.
Note: Please scan the code before run the script.
Replace the ReportServer name with yours.
Replace the schema of yours. Using the following code to find the schema:

SELECT CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
   FROM ReportServer.dbo.Catalog C

SELECT CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
   FROM ReportServer.dbo.Catalog C

Here are some code snippet for your reference:

SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
      FROM ReportServer$JULIE1SERVER.dbo.Catalog C
     WHERE C.Content IS NOT NULL
      AND C.Type = 2  --2: report

SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
      FROM ReportServer$JULIE1SERVER.dbo.Catalog C
     WHERE C.Content IS NOT NULL
      AND C.Type = 2  --2: report

SQL Server 2008 or higher version

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.