Determine referenced tables and their columns from a view
Hi, Is it possible to use QueryBuilder to determine the table columns that are referenced by a view? For example, if I have a view "View1" defined as "SELECT TestTable.Col1 AS MyCol1, TestTable.Col2 AS MyCol2 FROM SomeTable AS TestTable" and then I defined the SQL for the QueryBuilder instance as "SELECT * FROM View1", is it possible to determine that View1.MyCol1 is actually referencing TestTable.Col1 in the underlying view?
Hello, Ted.
Although Active Query Builder can't do this out-of-the-box, you can do some tricks to make it happen.
Active Query Builder allows using virtual objects in the queries. They look like ordinary objects, but in fact represent SELECT queries, exactly like database views. So, what you need to do is to turn views loaded into the Metadata Container into virtual objects. For that purpose, you should fill the Expression property of appropriate View objects with the text of respective view bodies (which are SELECT statements).
After that, you'll be able to get full information about real database table fields that are used in your queries through the QueryBuilder.QueryStatistics set of collections.
You can learn more about these features in the following articles:
There are several ways you can set Expression property for views. I recommend you to use the MetadataContainer.ItemMetadataLoaded event: it will be fired as soon as an object is added to the Metadata Container. But you can instruct the component to load Metadata Container completely and then search and edit the necessary objects, or you can even save the modified content of Metadata Container to XML file and then load metadata from this file every time you open the component.
Let me know If you have any further questions.