Get Report Cache Policy

DROP PROC usp_GetReportCachePolicy

GO

CREATE PROC usp_GetReportCachePolicy @ReportName NVARCHAR(850) = NULL

AS

/****************************************************

Purpose: To retrieve a list of defined cache policies against one or

many reports.

Uses the Catalog, and CachePolicy tables

in the report server database (assumed to be called by its default

name of 'ReportServer')

Author: Sorna Kumar Muthuraj

History: 9 Jul 2011 - Initial Issue

*****************************************************/

SELECT

c.ItemID AS ReportID,

c.name AS ReportName,

c.Path AS ReportFolder,

cp.cacheexpiration AS CacheExpiration,

cp.expirationflags AS ExpirationFlags

FROM ReportServer.dbo.CachePolicy cp (NOLOCK)

INNER JOIN ReportServer.dbo.Catalog c (NOLOCK)

ON c.itemid = cp.reportid

-- no restriction on CatalogType added as cache policy is just for reports

WHERE c.Name LIKE '%' + ISNULL(@ReportName, '') + '%'