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