USE ReportServer

go

create procedure ListareRapoarteInRS

as

SELECT DISTINCT
[c].[name] AS [reportname],
[c].path AS [reportpath],
isnull([u0].[username], ‘Not Scheduled’) AS [reportinternalowner],
isnull([s].[name], ‘Not Scheduled’) AS [scheduleinternalname],
isnull([u1].[username], ‘Not Scheduled’) AS [scheduleinternalowner],
CASE
WHEN [s].[name] IS NOT NULL
AND [sj].[name] IS NULL
THEN ‘*** ERROR: SQL Agent Job “‘+CONVERT( VARCHAR(50), [s].[scheduleid])+'” Does Not Exist ***’
ELSE [sj].[name]
END AS [sqlagentjobname],
[su].[name] AS [sqlagentjobowner],
[sj].[date_created] AS [sqlagentjobcreateddate],
[sjs].[next_run_date],
[sjs].[next_run_time],
AVG(CAST([timedataretrieval] AS FLOAT)) AS [avgtimedataretrieval],
AVG(CAST([timeprocessing] AS FLOAT)) AS [avgtimeprocessing],
AVG(CAST([timerendering] AS FLOAT)) AS [avgtimerendering],
AVG(CAST([bytecount] AS FLOAT)) AS [avgbytecount],
AVG(CAST([rowcount] AS FLOAT)) AS [avgrowcount]
FROM [dbo].[catalog] AS [c]
INNER JOIN [dbo].[users] AS [u0] ON [c].[createdbyid] = [u0].[userid]
LEFT JOIN [dbo].[reportschedule] AS [rs] ON [c].[itemid] = [rs].[reportid]
LEFT JOIN [dbo].[schedule] AS [s] ON [rs].[scheduleid] = [s].[scheduleid]
LEFT JOIN [dbo].[users] AS [u1] ON [s].[createdbyid] = [u1].[userid]
LEFT JOIN [msdb].[dbo].[sysjobs] AS [sj] ON CONVERT( VARCHAR(50), [s].[scheduleid]) = [sj].[name]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sjs] ON [sj].[job_id] = [sjs].[job_id]
LEFT JOIN [msdb].[dbo].[sysusers] AS [su] ON [sj].[owner_sid] = [su].[sid]
LEFT JOIN [dbo].[executionlog] AS [el] ON [el].[reportid] = [c].[itemid]
WHERE [c].[type] = 2 — Reports
GROUP BY [c].[name],
[c].path,
isnull([u0].[username], ‘Not Scheduled’),
isnull([s].[name], ‘Not Scheduled’),
isnull([u1].[username], ‘Not Scheduled’),
CASE
WHEN [s].[name] IS NOT NULL
AND [sj].[name] IS NULL
THEN ‘*** ERROR: SQL Agent Job “‘+CONVERT( VARCHAR(50), [s].[scheduleid])+'” Does Not Exist ***’
ELSE [sj].[name]
END,
[su].[name],
[sj].[date_created],
[sjs].[next_run_date],
[sjs].[next_run_time]
ORDER BY 1,2,3