Get Report Object Creation and Change Dates

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