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
|
