Reports and SSRS - 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

ċ
usp_GetReportObjectChangeDetails.sql
(1k)
Andy Hughes,
Jul 25, 2012, 10:00 AM
Comments