question

Try this for starters. I've been using this on a recent SSRS project where there was no documentation of the existing SSRS instances. It is based in part on queries from Bret Stateham ([ http://bretstateham.com/category/ssrs/][1]) --The first CTE gets the content as a varbinary(max) --as well as the other important columns for all reports, --data sources and shared datasets. WITH ItemContentBinaries AS ( SELECT ItemID,Name,[Type] ,CASE Type WHEN 2 THEN 'Report' WHEN 5 THEN 'Data Source' WHEN 7 THEN 'Report Part' WHEN 8 THEN 'Shared Dataset' ELSE 'Other' END AS TypeDescription ,CONVERT(varbinary(max),Content) AS Content,path FROM ReportServer.dbo.Catalog WHERE Type IN (2,5,7,8) ), --The second CTE strips off the BOM if it exists... ItemContentNoBOM AS ( SELECT ItemID,Name,[Type],TypeDescription ,CASE WHEN LEFT(Content,3) = 0xEFBBBF THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content))) ELSE Content END AS Content, path FROM ItemContentBinaries ) , --This CTE strips off the trailing 0x00 if there is one ItemContentNoNullTerm AS ( SELECT ItemID ,Name ,[Type] ,TypeDescription ,path ,CASE WHEN RIGHT(Content,1) = 0x00 THEN CONVERT(varbinary(max),LEFT(Content,LEN(Content)-1)) ELSE Content END AS Content FROM ItemContentNoBOM ) --The old outer query is now a CTE to get the content in its xml form only... ,ItemContentXML AS ( SELECT ItemID,Name,[Type],TypeDescription, path ,CONVERT(xml,Content) AS ContentXML FROM ItemContentNoNullTerm ) --now use the XML data type to extract the queries, and their command types and text.... SELECT path --,ItemID ,Name --,[Type] ,TypeDescription --,ContentXML ,Query.value('(./*:DataSourceName/text())[1]','nvarchar(max)') AS DataSourceName ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText --,cast(patindex('%subreport%',convert(varchar(max), ContentXML)) as bit) as HasSubReports FROM ItemContentXML --Get all the Query elements (The "*:" ignores any xml namespaces) outer APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query) order by path [1]: http://bretstateham.com/category/ssrs/