Lloyd Banks Lloyd Banks - 2 months ago 7
SQL Question

Finding Out If a Table is Being Used by a Report

Is there anyway to find out if a particular table is being used by a report on the reporting server?

Answer
USE ReportServer

DECLARE @TEXTTOSEARCH AS VARCHAR(200)
SET @TEXTTOSEARCH = 'urtableorview'

;WITH XMLNAMESPACES 
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)

SELECT name
    , x.value('CommandType[1]', 'VARCHAR(100)') AS CommandType
    , x.value('CommandText[1]','VARCHAR(MAX)') AS CommandText
    , x.value('DataSourceName[1]','VARCHAR(150)') AS DataSource
FROM (SELECT name, 
    CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML 
    FROM Catalog 
    WHERE content IS NOT NULL AND type != 3) AS a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') IS NULL
    AND x.value('CommandText[1]','VARCHAR(MAX)') LIKE '%' + @TEXTTOSEARCH + '%'
ORDER BY name