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