Credits:
Author: Sorna Kumar Muthuraj
Date: 9 Jul 2011
Description
Retrieves the creation and last change dates and user names for one or many objects stored in the Report Server Database Catalog table.
There are multiple types of objects stored in the Catalog table. The number of these has increased through the different editions, and currently (SQL 2008 R2) stands at:
NOTE: It is assumed that the Report Server Database has its default name of 'ReportServer'
Code
Stored Procedure:
DROP PROC usp_GetReportObjectChangeDetails GO
CREATE PROC usp_GetReportObjectChangeDetails @ReportName NVARCHAR(850) = NULL AS
/**************************************************** Purpose: To retreive the creation/change dates for one or many report objects. Uses the Catalog table in the report server database (assumed to be called by its default name of 'ReportServer')
Author: Sorna Kumar Muthuraj History: 9 Jul 2011 *****************************************************/
SELECT c.Name AS ObjectName, CASE c.TYPE WHEN 1 THEN 'Folder' WHEN 2 THEN 'Report (.rdl)' WHEN 3 THEN '.XML' WHEN 4 THEN 'Link Report' WHEN 5 THEN 'Data Source (.rds)' WHEN 6 THEN 'Model' WHEN 8 THEN 'Shared Dataset' WHEN 9 THEN 'Report Part' ELSE 'Unknown' END AS ObjectType, u.UserName AS CreatedBy, c.CreationDate AS CreationDate, um.UserName AS ModifiedBy, c.ModifiedDate AS ModifiedDate FROM Reportserver.dbo.Catalog C INNER JOIN Reportserver.dbo.Users U ON C.CreatedByID = U.UserID INNER JOIN Reportserver.dbo.Users UM ON c.ModifiedByID = UM.UserID WHERE Name LIKE '%' + ISNULL(@ReportName, Name) + '%' -- if param is null, then return all reports
|