EDIT: It seems they're using "semanticQuery" and this is why I cant see usefull information?
We will be decommissioning one of our functions, and we noticed that some reports are still using it.
Is there a way to grab the "query" of a report, and then filter it?
for example:
SELECT REPORT_NAME, QUERY from Magic_DMV_report_queries where QUERY like '%dbo.badFunction%'
I tried to find some dmvs and etc but with no success even trying to get some XML information.
With this query, the "command" is not so "understandable" and I thought it would be:
DECLARE @details AS TABLE
(
id INT IDENTITY(1,1) NOT NULL,
Report NVARCHAR(100),
ReportPath NVARCHAR(500),
DataSetName NVARCHAR(50),
DataSourceName NVARCHAR(100),
ComandType NVARCHAR(50),
CommandText NVARCHAR(MAX)
)
DECLARE @report xml
DECLARE @reportname NVARCHAR(100), @reportpath NVARCHAR(500)
DECLARE @query NVARCHAR(MAX)
DECLARE db_cursor CURSOR FOR
SELECT Name, Path ,CAST(cast(Content AS varbinary(max)) as XML)
FROM Catalog
WHERE Type = 2
ORDER BY Name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @reportname, @reportpath, @report
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @details
SELECT
@reportname AS Report,
@reportpath,
t.c.value('@Name[1]', 'nvarchar(50)') AS DataSetName,
t.c.value('(./*:Query/*:DataSourceName)[1]', 'nvarchar(50)') AS DataSourceName,
t.c.value('(./*:Query/*:CommandType)[1]', 'nvarchar(100)') AS CommandType,
ISNULL(t.c.value('(./*:Query/*:CommandText)[1]', 'nvarchar(MAX)'), 'Shared - ' + t.c.value('(./*:SharedDataSet/*:SharedDataSetReference)[1]', 'nvarchar(MAX)')) AS CommandText
FROM
@report.nodes('/*:Report/*:DataSets/*:DataSet') AS t(c)
FETCH NEXT FROM db_cursor INTO @reportname, @reportpath, @report
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT * FROM @details ORDER BY Report, DataSetName
EIDT2:
I think I did it:
with CTE as
(SELECT Name,
cast(CAST(CAST(Content AS VARBINARY(MAX)) AS XML)as nvarchar(max)) AS ReportXML
FROM Catalog
where Type = 2)select * from cte
where ReportXML like '%My_Cool_Procedure_or_function%'