I work on a team that has an SSRS setup with roughly ~1300 (and growing) canned reports. As one can imagine, this has presented problems when a breaking change is introduced to a table. Finding all the reports that may touch a table/field is error prone at best.
I'm trying to programmatically build a dependency model. Getting the schema for Owner -> Object -> Fields
and how views/tables relate is fairly trivial.
What I'm struggling with is how to get a resolved list of fields from a given query. I can pull the query from the RDL files, but interpreting those files is far less so. Queries can contain aliases for fields and tables alike, plus there are problems such as select *
. I'm trying to avoid a regexp hack, and I really don't want to write a SQL interpreter…
My initial thought was to loop through the RDL files and parse the explain plan output. While technically possible, this doesn't give me a complete list of resolved fields.
Is there any method in which I can use to get the DB analyse a given query and return a list of Object_Name.Field_Name
? I don't mind having to do some text processing to pull out the results if necessary.
Best Answer
Oracle 12c provides the procedure
DBMS_UTILITY.EXPAND_SQL_TEXT
.EXPAND_SQL_TEXT Procedure
If you have a query, it can expand its text to involve th base tables and its columns.
Example:
To find the tables and columns used for
select * from v2
:Output is:
The rest is up to you though.