I recently rolled off a project where we needed to extract some information from the report server catalog (e.g. the reportserver db). This function will includes the report path to the report.
IF EXISTS
(
SELECT 1
FROM information_schema.routines
WHERE routine_type = 'FUNCTION'
AND routine_schema = 'dbo' AND routine_name = 'ssrs_report_info'
)
DROP FUNCTION dbo.ssrs_report_info
GO
CREATE FUNCTION dbo.ssrs_report_info(@path varchar(200))
-- for the path variable you must include the /'s in front and back (e.g. /quest_2/)
RETURNS TABLE
RETURN
WITH cte AS
(
SELECT -- Do All aliases
[path],
[name] AS Report_Name,
creationDate,
ModifiedDate,
CONVERT(XML, CONVERT(VARBINARY(MAX), content)) AS rdl
FROM reportserver.dbo.[catalog]
WHERE Type=2 AND ([Path] LIKE @path+'%' OR (@path IS NULL OR @path = '/'))
)
SELECT TOP 2000000000 -- required for the order by clause, helps get higher cardinality est.
ReportInfo_id = row_number() over (order by (select null)),
ReportRDL = rdl,
Path AS Report_Path,
LEFT([Path], LEN([path]) - CHARINDEX('/',REVERSE([Path])) + 1) AS Report_Folder,
Report_Name,
T2.N.value('(*:DataSourceName/text())[1]', 'nvarchar(128)') AS DataSourceName,
T1.N.value('@Name', 'nvarchar(128)') AS DataSetName,
ISNULL(T2.N.value('(*:CommandType/text())[1]', 'nvarchar(128)'), 'T-SQL') AS CommandType,
T2.N.value('(*:CommandText/text())[1]', 'nvarchar(max)') AS CommandText,
creationDate,
ModifiedDate
--, property, parameter
FROM cte AS T
CROSS APPLY T.rdl.nodes('/*:Report/*:DataSets/*:DataSet') AS T1 (N)
CROSS APPLY T1.N.nodes('*:Query') AS T2 (N)
--ORDER BY Report_Path, Report_Name, DataSetName, DataSourceName, CommandType, CommandText
-- use the new query/flwor technique to eliminate the cross-apply (double-check paralellism), or just query method
-- use a copy of the ssrs db with xml unpacked and indexes applied
GO
I can't test this at the moment because I don't have a populated reportserver DB handy but I think this will get you what you need.
-- Itzik Ben-Gan 2001