Finding Queries That Reference a Specific Table

ReportServer ReportServer (EN)
1

Hello,
I am looking to find all the queries that use a specific table in a specific database.
Which tables in ReportServer database can I query to find this information?
Thank you in advance.

2

Hi, try something like this

SELECT 
  rr.id,
  rr.name_field,
  rdd.name_field,
  rddc.query
FROM rs_database_datasource_conf rddc
JOIN rs_datasource_def_config rddc2 ON rddc.id = rddc2.entity_id
JOIN rs_datasource_container rdc ON rddc2.entity_id = rdc.datasource_config_id
JOIN rs_report rr ON rdc.entity_id = rr.datasource_container_id 
JOIN rs_datasource_definition rdd ON rdc.datasource_id = rdd.id
WHERE lower(rddc.query) LIKE lower('%table_name%')
AND lower(rdd.name_field) LIKE lower('%datasource_name%')

I’m not sure I understood you correctly, but this code above is for Dynamic list report type
And here is code for Birt reports

SELECT 
  rbr.id,
  rr.name_field,
  rbrf.name_field
FROM rs_birt_report rbr
JOIN rs_birt_report_file rbrf ON rbr.report_file_id = rbrf.entity_id
JOIN rs_report rr ON rbr.id = rr.id 
WHERE lower(rbrf.CONTENT) LIKE lower('%table_name%')

I’m not using other report types so I hope this can help you

3

Hi,
that was exactly what I was looking for.
thanks a lot