Get Report Cache Policy

Applicability:

SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Sorna Kumar Muthuraj

Date: 9 Jul 2011

Description

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'

Code

Stored Procedure:

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, '') + '%'