Joseph Michael Pesch
VP Programming

SQL Read Sharepoint Document Content for SSRS Reports

by 6. December 2012 09:43

I had a case where I needed to review a few hundred SSRS reports installed on a Sharepoint 2010 envrionment.  Below is a SQL script I created to read the content from the underlying SQL database and search the report structure for certain keywords (in my case I was looking for specific database/table names in the report SQL).

create table #tmp(name varchar(150), content varchar(max))
/* Get cursor of all SSRS report (*.rdl) files to cycle through */
declare cur cursor for 
select Id, LeafName, InternalVersion 
from AllDocs where LeafName like '%.rdl'
declare @id varchar(50), @name varchar(200), @version int
open cur
while 1=1 begin
  fetch next from cur into @id, @name, @version
  if @@FETCH_STATUS <> 0 break
  /* Check is current version of the report contains specific keywords */
  if exists
    select 1 
	where Id = @id 
	and InternalVersion = @version 
	  cast(Content as varchar(max)) like '%KEYWORD #1 HERE%' 
	  cast(Content as varchar(max)) like '%KEYWORD #2 HERE%')
    insert into #tmp 
    select @name, cast(Content as varchar(max)) 
    from WSS_CONTENT_SITENAMEHERE.dbo.AllDocStreams 
    where Id = @id and InternalVersion = @version
close cur
deallocate cur
select * from #tmp
drop table #tmp


Sharepoint | SQL Server

Comments are closed