Search for fields on ReportServer

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

/* * Get a list of reports in a specific path/folder */ ;WITH ItemContentBinaries AS ( SELECT ItemID ,Name ,[Type] ,CASE [Type] WHEN 2 THEN 'Report' WHEN 5 THEN 'Data Source' WHEN 7 THEN 'Report Part' WHEN 8 THEN 'Shared Dataset' ELSE 'Other' END AS TypeDescription ,CONVERT(varbinary(max),Content) AS Content FROM ReportServer.dbo.[Catalog] with (nolock) WHERE [PATH] LIKE '/Department Level/%' ) ---ADD WORDS, USED IN FIELDS, TO SEARCH FOR ,WordSearchCTE AS ( SELECT 'SSN' AS Word UNION ALL SELECT 'AGE' UNION ALL SELECT 'DOB' UNION ALL SELECT 'EPMP' UNION ALL SELECT 'PAY' UNION ALL SELECT 'COMP' ) --This CTE strips off the BOM if it exists... ,ItemContentNoBOM AS ( SELECT ItemID ,Name ,[Type] ,TypeDescription ,CASE WHEN LEFT(Content,3) = 0xEFBBBF THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content))) ELSE Content END AS Content FROM ItemContentBinaries ) --The old outer query is now a CTE to get the content in its xml form only... ,ItemContentXML AS ( SELECT ItemID,Name,[Type],TypeDescription ,CONVERT(xml,Content) AS ContentXML FROM ItemContentNoBOM ) --now use the XML data type to extract the queries, and their command types and text.... --and only select from those that contain the search terms SELECT * FROM ( SELECT ItemID,Name,[Type],TypeDescription,ContentXML ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText FROM ItemContentXML --Get all the Query elements (The "*:" ignores any xml namespaces) CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query) )x JOIN ( select '%'+Word+'%' as pattern from WordSearchCTE ) w on CommandText like w.pattern

Quick Reminder: How to find a field in a database, within SQL Server

select
t.name as table_name,
SCHEMA_NAME(schema_id) as schema_name,
c.name as column_name
from sys.tables as t
inner join sys.columns c on t.object_id = c.object_id
where c.name like '%Some Field Name%'
order by schema_name, table_name;