Find All Deployed SSRS Reports That Reference A Particular Field

13 November, 2009 (11:14) | SSRS | By: Mark V

This summer I built an SSRS 2008 Report Model for a client. The end users report against the model using Report Builder 2.0.

I shall pause here for a rave.


Report Builder 2.0 is just outstanding, in my humble opinion. I built a SQL 2005 Report Model for a different client a few years ago that is still using Report Builder 1.0 and the difference is astounding.


We recently needed to change the data type of a field used in the model. The field in question is an integer in the source database but is essentially similar to an account number. The users preferred to have it be a string in their reports so that Report Builder 2.0 would stop insisting on trying to SUM it.

Making the change to the model was no big deal. However, I thought about what would happen to reports that were already created that may be using the field in question and how we could find those reports with minimal effort in case they would need to be updated.

Then I thought to myself, self, the reports are really just files stored in the reportserver database. You should be able to write a query against that db to return the names of any reports which utilize the field in question pretty easily.

After a quick search on the Interwebs, I found this:

Hazzah! The reports actually reside in the Catalog table in the Content field (Image data type). I ended up creating a script similar to the following with my search string being the name of the field as it appears in the model:

SELECT [name]

FROM reportserver.dbo.catalog

WHERE CONVERT(varchar(max), CONVERT(varbinary(max), content)) LIKE '%fieldname%'


This worked like a champ. And it is really easy to come up with other uses for querying the reportserver database. I will be continuing to play with this for sure.


Comment from Michael
Time September 3, 2014 at 5:15 am

Thanks Mark.
I’ve been looking for something like this for a while. Your solution is very good and saved me from hours of dredging through all of my reports manually. Cheers

Comment from William Blozan
Time December 5, 2015 at 1:19 pm

Me too Mark,
I’m working on a Saturday morning onsite and the code was very slow, but it worked for me. I had success with both searching a errant data constant as well as the specific name of a stored procedure I was back-tracking.

Three cheers!

Comment from John Harding
Time March 22, 2016 at 6:46 am

Used this to find reports with hard coded addresses which needed to change. A really useful three lines of code!

Comment from Mark V
Time March 23, 2016 at 8:00 am

Thanks for sharing, John! Glad you found it helpful. I sure did. :)

Comment from Cornelius
Time November 23, 2017 at 9:20 am

I can’t tell you how much time this has saved me! I love you random internet person for posting this!!!

Comment from Joe Jakacky
Time March 26, 2018 at 12:58 pm

Thanks Mark! Oldie, but goodie! Helped me out in a pinch!

Comment from Mark V
Time March 27, 2018 at 1:45 pm

Thanks, Joe. Glad it helped.

Write a comment