Skip to main content
Version: 11.0

USMeta.Tables

note

This article is about the Tables method of the USMeta internal component.

USMeta.Tables

At runtime, gets metadata information about tables from the model developed in USoft Definer. This includes Database Tables, Logical Views, and Component Tables. For each retrieved table, this information takes the form of name/value pairs:

NameValue
TABLE_NAMETable Name
TABLE_TYPE

If the table is a Database Table: DATABASE

If the table is a Component Table: COMPONENT

If the table is a Logical View: LOGICAL VIEW

MODULE_NAME

If the table is not from a consumed module: the current application name.

If you are calling from a USoft application, this is the USoft application name, eg., USD for USoft Definer.

If you are calling from a User Application, this is the User Application name, eg. SALES.

If the table is from a consumed module: the name of the consumed module.

OBJECT_NAMEObject Name
OBJECT_NAME_PLURALObject Name Plural
PHYSICAL_NAMEName of the physical table in the RDBMS. Always same as TABLE_NAME, except for subtype tables with Create Separate Table = No.
SUPERTYPESupertype Table
SUBTYPE_SEPARATE_TABLECreate Separate Table (Y or N)
DELETE_SUPER_ON_DELETEDelete Super on Delete (Y or N)
USABLE_AS_INTERFACE

Interface (Y/N)

This attribute has the value stored in the INTERFACE column of T_TABLE.

For interface tables, this value is 'N’.

NR_OF_COLUMNS

If a Datatable Table, the number of columns of type Database in the table.

If a Logical View, the total number of columns in the Logical View.

NR_OF_VR_COLUMNS

If a Database Table, the number of columns of type Virtual in the table.

If a Logical View, the number 0 (zero).

NR_OF_INDEXESThe number of indexes on the table
INSERT_ALLOWEDY if the user calling usmeta.tables() has insert rights on the table, otherwise N.
UPDATE_ALLOWEDY if the user calling usmeta.tables() has update rights on the table, otherwise N.
DELETE_ALLOWEDY if the user calling usmeta.tables() has delete rights on the table, otherwise N.
IMPLEMENTING_MODULEModule

If the table is has USABLE_AS_INTERFACE = ‘Y’ AND the Definer environment of an in-scope consumer has synchronised with the module .CON file, so that the table is visible as "Interface Table” in that Definer environment, then that consumer appears in a "Consumed_In_Modules” list of child elements, for example:

<Table TABLE_NAME="C_USVC_HERITAGE" MODULE_NAME="USVC" ...>
<Consumed_In_Modules>
<Module MODULE_NAME="USD"/>
</Consumed_In_Modules>
</Table>

Syntax

SELECT USMeta.Tables(
    *table-name-pattern* Tables
,   *include-database-tables* IncludeDatabaseTables
,   *include-logical-views* IncludeViews
,   *include-component-tables* IncludeComponentTables
,   *interfaces-only* InterfacesOnly
,   *module-pattern* Module
,   *mime-type* MimeType
)

*include-database-tables* ::= { *yes-value* | *no-value* }
*include-logical-views* ::= { *yes-value* | *no-value* }
*include-component-tables* ::= { *yes-value* | *no-value* }

*interfaces-only* ::= { *yes-value* | *no-value* }

*yes-value* ::= { 'yes', 'Y', 'true', '1' }
*no-value* ::= (A value other than a *yes-value*)

*mime-type* ::= { application/xml
, xml
, application/json
, json }

All parameters are optional. Parameter values that you supply are mapped to parameters by alias name. The 'No' value in the following example is for include-logical-views because of the "IncludeViews" alias:

SELECT   USMeta.Tables( 'No' IncludeViews )
tip

For backward compatibility only, it is possible to supply parameter values by position. Using this syntax, a list of non-aliased values maps to the parameter list in the order stated, with empty values at the end of the list if you supply less than 5 values, and '' (the empty string) as a way of explicitly declaring an empty value.

Table-name-pattern is a regular expression that matches zero, one or more table names in the model. The result data is limited to tables matching the pattern. If table-name-pattern is omitted, then result data is not limited in this way.

If include-database-tables is set to 'yes' (the default), all database tables are included in the result, including interface tables that are database tables in the provider module.

If include-logical-views is set to 'yes' (the default), all logical views are included in the result, including interface tables that are logical views in the provider module.

If include-component-tables is set to 'yes' (the default), all component tables are included in the result, including interface tables that are component tables in the provider module.

If interfaces-only is set to 'yes', only tables that have the Interface = Yes flag are included. Do not confuse this category with the category of tables listed in the Definer Catalog as "Interface Tables", that is, tables provided by other modules. The default of interfaces-only is 'no', meaning that tables are included regardless of the value of their Interface flag.

Module-pattern is a regular expression that matches zero, one or more module names in the model. The result data is limited to tables within modules matching the pattern. Any tables that are consumed from a different module which is filtered out by the regular expression, will show two additional fields 'INTERFACE_FROM_MODULE', which will be 'Y', and 'IMPLEMENTING_MODULE', which will contain the module name where the table originates from. The 'MODULE_NAME' field will in this case contain the name of the module in which the interface was consumed. If module-pattern is omitted, then the result data is not limited in this way.

Yes-value is a string value in the set { 'yes’, 'Y’, 'true’, '1’ }, regardless of case. No-value is any other value.

Mime-type determines whether the output is in XML ( ‘application/xml‘ or 'xml’) or in JSON ('application/json’ or 'json’). The default is XML.

Example 1

This example returns table information about the DISCOUNT table.

SELECT USMeta.Tables( 'DISCOUNT' Tables )

The return value of this statement could be:

<?xml version="1.0" encoding="UTF-16"?>
<Meta>
<Tables>
<Table TABLE_NAME="DISCOUNT" MODULE_NAME="TRAVEL" USABLE_AS_INTERFACE="N" OBJECT_NAME="Discount" OBJECT_NAME_PLURAL="Discounts"
PHYSICAL_NAME="DISCOUNT" TABLE_TYPE="DATABASE" IS_SUBTYPE="N" NR_OF_COLUMNS="4" NR_OF_VR_COLUMNS="0" NR_OF_INDEXES="0"/>
</Tables>
</Meta>

Example 2

This example returns table information about all database tables that have a table name starting with 'P' followed only by other letters, such as 'PERSON' and 'PARTICIPANT' but not 'P_TOTAL'. Logical Views and Component Tables are not included.

SELECT USMeta.Tables(
'^P[A-Z]*$' Tables
, 'yes' IncludeDatabaseTables
, 'no' IncludeViews
, 'no' IncludeComponentTables
)

Example 3

You can use USMeta.Tables() to determine exactly what type(s) of table you want to export with XML.MultiExportTables.

note

For example, you can export only database tables and not other types of table such as component tables and logical views. This is discussed in detail in Example 3 of XML.MultiExportTables.