Querying SSRS Report MetaData for data sources , datasets associated query and connection string information

SSRS  metada    contains wealth    of information which   can be usefull   under varieous circumstances , but extracting    that information can be a timefull process , the basic information  is   stored    under the ReportServer  database  tables, but to retrieve the embedded data  from the report xml  ,the data needs to be   extracted into readable format from an xml format.

The below query  queries the report catalog table of ReportServer  Database   and flattens the xml content   from the Catalog table.

In this below dataset i have  mainly flattened the  Report xml   data   from the Content column of the Catalog table  ,  which was initially converted  to a varbinary type and than to xml,After the conversion to xml and  flattening  the xml , we can associate report name with its related datastes and datasources and connection string     as below



Declare @ReportName varchar(200)
Set @ReportName='SomeReport'
;With Cte
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] AS varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] WHERE type in (2))t 
where t.Name=@ReportName
)
/*** Get the Report Name and DataSource Name ***/,
ReportDataSources
As
(

Select 
dss.dtt.value('(@Name)[1]','varchar(100)') as dsDataSourceName,
Ltrim(Replace(dst.dts.value('(text())[1]','varchar(max)'),'/Data Sources/','')) AS DataSourceName,
Cnts.Cn.value('(text())[1]','varchar(100)') as Cnstring
FROM Cte
cross Apply cte.Content.nodes('//*:DataSource') dss(dtt)
Outer Apply dss.dtt.nodes('.//*:DataSourceReference') dst(dts)
outer Apply Cte.Content.nodes('.//*:ConnectString') Cnts(Cn)
) 
,ReportsAndDS
As
(
SELECT Distinct 
cte.name AS ReportName,

dss.dtt.value('(@Name)[1]','varchar(100)') AS DatasetName,

ds.dstt.value('(text())[1]','varchar(max)') AS DataSourceName,
Ltrim(Replace(shared.Sha.value('(text())[1]','varchar(100)'),'/Datasets/','')) AS SharedDataSet,
COALESCE(ct.cts.value('(text())[1]','varchar(max)'),'ReportQuery') as QueryType,
dst.dts.value('(text())[1]','varchar(max)') AS Query
FROM Cte
cross Apply cte.Content.nodes('//*:DataSet') dss(dtt)
cross Apply dss.dtt.nodes('.//*:CommandText') dst(dts)
Outer Apply dss.dtt.nodes('.//*:CommandType') ct(cts)
Outer Apply dss.dtt.nodes('.//*:DataSourceName') ds(dstt)
Outer Apply cte.content.nodes('.//*:SharedDataSetReference') shared(Sha)

)

,FtReportsAndDs
As
(

Select rs.ReportName,rs.DatasetName,rds.DataSourceName,rs.SharedDataSet ,rs.QueryType ,rs.Query, Substring(rds.Cnstring,CharIndex('=',rds.Cnstring,1)+1, CharIndex(';',rds.Cnstring,1)-(CharIndex('=',rds.Cnstring,1)+1)) as ServerName
from ReportsAndDS rs
Left Join ReportDataSources rds 
on rs.DataSourceName=rds.dsDataSourceName
)
/*** Get the DatSource Name and DatSource XML ***/
,DataSources
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] as varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] where type in (5))t )
,SharedDataSources
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] as varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] where type in (8))t )

,SharedDataSourceConnectionDetails
As
(
Select sdc.Name,dsr.ds.value('(text())[1]','varchar(100)') as DataSourceName ,
ddr.dr.value('(text())[1]','varchar(max)') as Query

from SharedDataSources sdc

Cross Apply sdc.Content.nodes('//*:DataSourceReference') dsr(ds)
Outer Apply sdc.Content.nodes('//*:CommandText') ddr(dr)

)
/*** Prepare the Final Query to Get Report Name and its asscoiated datasources datasets ,QueryType,Query and Connection String ***/

Select t.ReportName,t.DatasetName,t.DataSourceName,t.QueryType,t.Query, t.DatSourceConnectionDetails
,
t.DataSourceCredentials
, Isnull(Substring(DatSourceConnectionDetails,CharIndex('=',DatSourceConnectionDetails,1)+1, CharIndex(';',DatSourceConnectionDetails,1)-(CharIndex('=',DatSourceConnectionDetails,1)+1)),t.DataSourceName) as ServerName
from (
SELECT Distinct 
ds.ReportName,ds.DatasetName,IsNull(ds.DataSourceName,ds.ServerName) as DataSourceName,ds.Query,ds.QueryType,

dss.dtt.value('(./*:ConnectString)[1]','varchar(100)') AS DatSourceConnectionDetails,
dss.dtt.value('(./*:CredentialRetrieval)[1]','varchar(100)') AS DataSourceCredentials

FROM FtReportsAndDs ds Left join DataSources ctee ON
ds.DataSourceName=ctee.Name
Outer Apply ctee.Content.nodes('//*:DataSourceDefinition') dss(dtt)
UNION ALL
SELECT Distinct 
ds.ReportName,shd.Name as datasetName,shd.DataSourceName,shd.Query,ds.QueryType,
dss.dtt.value('(./*:ConnectString)[1]','varchar(100)') AS DatSourceConnectionDetails,
dss.dtt.value('(./*:CredentialRetrieval)[1]','varchar(100)') AS DataSourceCredentials
FROM ReportsAndDS ds Inner Join SharedDataSourceConnectionDetails shd
on ds.SharedDataSet=shd.Name
Left Join 
DataSources ctee ON
ctee.Name=shd.DataSourceName
Outer Apply ctee.Content.nodes('//*:DataSourceDefinition') dss(dtt))t

This is the snapshot of the above query without any filter applied for report  for this demo.

As you can see i haveflattened the xml content so that the we can see each report its associated dataset datasource name and its connection string, this can be very handy when we have very large number of reports   and wanted to migrate those reports to see the depended data sources   .