If you have seen any of my previous blog posts on extracting SSRS Report XML from the ReportServer.dbo.Catalog table, you may have also noticed some of the comments hoping to determine the objects and columns that a report’s data sets reference. It seems like that should be a simple task, but unfortunately it is harder than it should be.
SQL Server has to answer that exact same question for itself when the statements are submitted. SQL Server parses the SQL statements and generates an internal “tree” that represents the statement. That tree includes a number of things, but certainly the names of the objects referenced by the command. We could get something similar by using the plan information that is generated, but the plans don’t provide details on the higher level objects (views, etc) that are used in the query.
I posted a suggestion in connect to have the team provide us with a system function that might return the objects that a statement depends on.
Check it out, and vote it up if you agree: