Recently I have been checking all the Dependencies on the Reports in SSRS(Sql server Reporting Services) because of a Major deployment on the ETL. The ETL change was to accomodate the transfer of a few columns from old Table TableA to the new table TableB. Some of the Datasets in the Reports are Stored Procedures and so I used sp_depends procedure to get the list of dependencies for TableA.
I also used the following TSQL to get list of objects in any database where the command text includes TableA.
DECLARE @tablename VARCHAR(100)
SET @tablename = ‘%tableA%’
SELECT OBJECT_NAME(id) AS OBJECT ,TEXT,* FROM syscomments WHERE TEXT LIKE @Tablename
So far its good. But I found there are some TSQL written directly in the Reports Datasets(which I think is bad SSRS Development) and I cannot search them using the above two procedures.
Upon researching in the ReportServer database the TSQL that is stored in the datasets are embedded in the CommandText tag of contentcolumn in the Catalog table.
The following is the script that illustrates it.
DECLARE @tablename VARCHAR(100)
SET @tablename = ‘%TableA%’
SELECT PATH
, NAME
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS ReportXML
FROM ReportServer.dbo.Catalog
WHERE CAST(content AS VARBINARY(MAX)) LIKE @tablename
You can even update the reports using the ReportXML column which I will discuss about it the future blogs.
