Get Report Cache Policy


SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested


Author: Sorna Kumar Muthuraj

Date: 9 Jul 2011


Retrieves a list of defined cache policies against one or many reports

NOTE: It is assumed that the Report Server Database has its default name of 'ReportServer'


Stored Procedure:

DROP PROC usp_GetReportCachePolicy


CREATE PROC usp_GetReportCachePolicy @ReportName NVARCHAR(850) = NULL



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



c.ItemID AS ReportID, 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, '') + '%'