Schema View Reference
The SMS view schema information views can be joined together and used to retrieve specific data.
View Schema Information Views
The view schema information views provide information about all of the views in an SMS site that are in the SMS view schema family. The two view schema information views are listed and described in the following table.
Schema Information View |
Description |
|---|---|
v_SchemaViews |
Lists all the views and view types in the view schema family. |
v_ReportViewSchema |
Lists all the views in the view schema family and the column names for each view. |
Views Schema
The following query uses the v_SchemaViews view to retrieve a list of all the view schema family views and their associated view category:
SELECT Type, ViewName
FROM v_SchemaViews
ORDER BY Type, ViewName
Each of the SMS views has multiple columns, and determining which of these columns to use when building queries for the desired data is sometimes difficult. The following query joins the v_SchemaViews and v_ReportViewSchema to list all of the views in the SMS view schema family, each of the columns within each view, and the view category:
SELECT RVS.ViewName, RVS.ViewColumnName, SV.Type
FROM v_SchemaViews as SV INNER JOIN v_ReportViewSchema as RVS
ON SV.ViewName = RVS.ViewName
ORDER BY SV.Type, RVS.ViewName, RVS.ViewColumnName
The output from this query and the information in the following sections provide information to help you use the correct view and view column to build queries for effective reporting.