Show all SSRS reports and their subscriptions

This is a very handy script to give you a quick overview of the report subscriptions you have on your Microsoft SQL Server Reporting Services server. It works on SSRS 2005 to 2012.

-- Show reports and their subscriptions

USE ReportServer;

SELECT CASE

              WHEN (SubString(c.[Path], 1, Len(c.[Path]) - (CharIndex('/', Reverse(c.[Path])) - 1))) = ''

                     THEN '<root folder>'

              ELSE SubString(c.[Path], 1, Len(c.[Path]) - (CharIndex('/', Reverse(c.[Path])) - 1))

              END AS Folder

       ,C.Name

       ,s.Description

       ,s.LastRunTime

       ,REPLACE(REPLACE(CAST(CAST(extensionsettings AS XML).query('/ParameterValues/ParameterValue/Value[../Name = ''TO'']') AS VARCHAR(MAX)), '</Value>', ''), '<Value>', '') AS 'To:'

       ,REPLACE(REPLACE(CAST(CAST(extensionsettings AS XML).query('/ParameterValues/ParameterValue/Value[../Name = ''CC'']') AS VARCHAR(MAX)), '</Value>', ''), '<Value>', '') AS 'CC:'

       ,REPLACE(REPLACE(CAST(CAST(extensionsettings AS XML).query('/ParameterValues/ParameterValue/Value[../Name = ''BCC'']') AS VARCHAR(MAX)), '</Value>', ''), '<Value>', '') AS 'BCC:'

       ,u.UserName AS ScheduleOwner

FROM dbo.subscriptions s

INNER JOIN dbo.CATALOG AS C

       ON C.itemid = s.report_oid

INNER JOIN dbo.CATALOG AS C1

       ON C.ParentID = C1.itemid

LEFT JOIN dbo.Users u

       ON u.UserID = s.OwnerID

ORDER BY C.NAME

         ,s.LastRunTime

<end>