7/16/2012

SSRS Search for Keyword

I may have posted this before.

Except I use this query several times per week.

So I'm re-posting it for you and for me:

Simply copy & paste into SQL-Server SSMS T-SQL, change the Put_Keyword_here to the value you would like to search for and RUN...


Since SSRS stores the entire report in XML in the database ReportServer, the query basically scans every report and returns the matches - quite nice!!!!!

use [ReportServer]
SELECT
        CONVERT(varchar(MAX)
      , CONVERT(varbinary(MAX), c.[content]))
      , c.[Path]
      , c.[Name]
FROM [ReportServer].[dbo].[catalog] c
where path not like '/Data Sources/%'
and c.[content] IS NOT NULL
AND CONVERT(varchar(MAX), CONVERT(varbinary(MAX), c.[content]))
LIKE '%Put_Keyword_here%'

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Thoughts to Ponder