USE REPORTSERVER_REPOSITORY
GO
DECLARE @Subscriptions TABLE (
                Report_OID  UNIQUEIDENTIFIER,
                ToList      VARCHAR(8000),
                CCList      VARCHAR(8000),
                BCCList     VARCHAR(8000),
                SubjectLine VARCHAR(8000))DECLARE @ExtensionSettings XML
DECLARE @Report_OID UNIQUEIDENTIFIER
DECLARE @idoc  INT
DECLARE SubscriptionList CURSOR FOR
SELECT Report_OID,ExtensionSettings 
    FROM subscriptionsOPEN SubscriptionList
    FETCH NEXT     FROM SubscriptionList INTO @Report_OID ,@ExtensionSettings
     WHILE (@@FETCH_STATUS=0)
    BEGIN
    EXEC sp_xml_preparedocument @idoc OUTPUT, @ExtensionSettings
    INSERT INTO @Subscriptions
        SELECT @Report_OID,[TO],[CC],[BCC],[Subject]
        FROM
        (
        SELECT *
            FROM OPENXML (@idoc, '/ParameterValues/ParameterValue')
                    WITH (Name NVARCHAR(100) 'Name',
                        Value NVARCHAR(100) 'Value')
        ) AS SourceTable
                pivot
        (
                    MAX(value) FOR [Name] IN ([TO],[BCC],[CC],[Subject])
        ) AS pivottable
    EXEC sp_xml_removedocument @idoc
   FETCH NEXT  FROM SubscriptionList INTO @Report_OID ,@ExtensionSettings

    END
            CLOSE SubscriptionList
            DEALLOCATE SubscriptionList



SELECT c.path,c.name,s.Tolist,s.cclist,s.bcclist,s.subjectline    FROM 
Catalog c INNER JOIN @Subscriptions  s ON c.ItemID = s.Report_OID    ORDER BY [path], Name



This will list the all reports configured for subscriptions with following details of Report Path, Report Name, ToList,CCList,BCCList and subject line of email.



If you liked this post, do like my page on FaceBook