How to get list of all hosted reports and their path on report server

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s