Dataverse - How to retrieve FetchXML or SQL for Dataverse views
With Dataverse, we can define views which enable us to carry out basic joins, and to filter and to sort data server-side.
As an example, here's the definition of a view that's based on a custom table called 'Property'. This view retrieves the address, city, and property type conlumns from this table, and inlcudes a filter to return only records where the property type matches 'house'. The name of this view is 'houses'.
Dataverse stores the definition of this view in a standard table called Views. If we open this table in the Dataverse designer, we notice two interesting columns - FetchXML and 'Offine SQL Query'.
By default, it isn't possible to view the contents of this table in the designer - the 'Data' tab is missing from the designer
However, we can access this table through other means, such as through the TDS endpoint, or by building an app that's based on this table.
The screenshot beneath illustrates an auto-generated app that's based on the 'Views' tables, and highlights how we can display the FetchXML and SQL definition of the 'houses' view.
Conclusion
Dataverse stores view definitions in a table called Views. We can access the contents of this table and use it to discover the FetchXML and SQL definitions of the Dataverse views.
