Get Report Object Creation and Change Dates

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 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:

1 = Folder

2 = Report (.rdl)

3 = .XML

4 = Link Report

5 = Data Source (.rds)

6 = Model

8 = Shared Dataset

9 = Report Part

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