T-SQL script list all existing SSRS Subscriptions and their Schedules

In SSRS Report Manager everyone can lookup and manage his own subscriptions.
But as a System Admin I would like to see all existing subscriptions and the schedule to check, if may at a certain time particularly many subscriptions are planned and a bottleneck could occur.
This Transact-SQL script list all existing subscriptions and their schedule data.

Please remark: Querying the ReportServer database directly is not a supported way to get information’s of/from SSRS.
Works with SQL Server 2005 and higher versions in all editions.
Requires SELECT permissions on the ReportServer database.



-- List all SSRS subscriptions
USE [ReportServer]; -- You may change to your ReportServer database name if it configured with different databaseName.
GO
SELECT USR.UserName AS SubscriptionOwner
,SUB.ModifiedDate
,SUB.[Description]
,SUB.EventType
,SUB.DeliveryExtension
,SUB.LastStatus
,SUB.LastRunTime
,SCH.NextRunTime
,SCH.Name AS ScheduleName
,CAT.[Path] AS ReportPath
,CAT.[Description] AS ReportDescription
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR
ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS
ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH
ON RS.ScheduleID = SCH.ScheduleID
ORDER BY USR.UserName
,CAT.[Path];

Below is the Sample Output you will get after executing above script As I don’t have any reports you will not see any data in the below Output :


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.