Declare @Namespace NVARCHAR(500)
Declare @SQL VARCHAR(max)
SELECT @Namespace= SUBSTRING(x.CatContent,x.CIndex,CHARINDEX(‘”‘,x.CatContent,x.CIndex+7) – x.CIndex)
FROM
(SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))
,CIndex = CHARINDEX(‘xmlns=”‘,CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))
FROM ReportServer_SQL2008_Prod.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
) X
SELECT @Namespace = REPLACE(@Namespace,’xmlns=”‘,”) + ”
SELECT COALESCE(C.Path, ‘Unknown’) as ReportPath,Name,CreatedBy = U.UserName
,CreationDate = C.CreationDate
,ModifiedBy = UM.UserName
,ModifiedDate
FROM ReportServer_SQL2008_Prod.dbo.Catalog C
JOIN ReportServer_SQL2008_Prod.dbo.Users U
ON C.CreatedByID = U.UserID
JOIN ReportServer_SQL2008_Prod.dbo.Users UM
ON c.ModifiedByID = UM.UserID