USMeta.Columns

Previous Next

At runtime, gets metadata information about columns from the model developed in USoft Definer. This information takes the form of column attribute settings, but also contains relevant attribute settings done in USoft at domain level, for example, information about datatype, column length, and allowed values.

Returns an XML document of the form demonstrated in Example 1 below.

Syntax

SELECT USMeta.Columns(

       table-name-pattern

,      column-name-pattern

,      interfaces-only

)

 

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

 

 

All parameters are optional. Parameter values passed (if any) are mapped to parameters by position from left to right.

Table-name-pattern is a regular expression that matches zero, one or more table names in the model. Only information about columns in tables that match the pattern is returned.

If table-name-pattern is omitted, that is, if no parameters are passed at all, then information on ALL tables and columns is retrieved.

You can pass the empty string as the value of table-name-pattern if you only want to specify a column-name-pattern, as demonstrated in Example 1.

Column-name-pattern is a regular expression that matches zero, one or more column names in the model. Only information about columns that match the pattern is returned.

If column-name-pattern is omitted, then information on ALL columns in tables matching table-name-pattern (if this parameter is not the empty string) is retrieved.

If you want to pass column-name-pattern but not table-name-pattern, you must pass the empty string for table-name-pattern, as demonstrated in Example 1.

Interfaces-only optionally takes either a yes-value or a no-value, where yes-value is a value from the set containing the string values 'yes', 'Y', 'True' and '1', regardless of case, and no-value is any other string value.

If interfaces-only is set to 'yes', only columns that have Interface = Yes are included. The default of interfaces-only is 'no', meaning that columns are included regardless of the value of their Interface attribute.

Example 1

This example returns column information about all columns in the model that have Column Name = 'SCHEDTOUR_ID' and Interface = Yes:

SELECT USMeta.Columns( '', 'SCHEDTOUR_ID', 'Y' )

 

The return value of this statement could be:

<?xml version="1.0" encoding="UTF-16"?>

<Meta>

  <Columns>

    <Column COLUMN_NAME="SCHEDTOUR_ID" TABLE_NAME="RESERVATION" TABLE_MODULE="TRAVEL" PRIMARY_KEY="N" FOREIGN_KEY="Y" MANDATORY="Y" INPUT_ALLOWED="Y" QUERY_ALLOWED="Y" UPDATABLE="Y" DISPLAYED="Y" INTERFACE="N" DEF_VALUE="" COLUMN_TYPE="DATABASE" PROMPT="Scheduled Tour No" COLUMN_HELP_TEXT="ID Number of Scheduled Tour for which this reservation is made" DOMAIN_NAME="SCHEDTOUR_ID" DATATYPE="NUMBER" TOTAL_LENGTH="4" LENGTH_AFTER_PERIOD="0" DISPLAY_DATATYPE="INT" DISPLAY_LENGTH="0" DISPLAY_LENGTH_AFTER_PERIOD="0" UPPERCASE="N" FIXED_LENGTH="N" IS_SEQNO="Y" UNICODE_TYPE="N" RANGE_LOW="" RANGE_HIGH="" DOMAIN_HELP_TEXT="" REG_EXP="" DOMAIN_MODULE="TRAVEL" IO_FORMAT=""/>

    <Column COLUMN_NAME="SCHEDTOUR_ID" TABLE_NAME="SCHEDTOUR" TABLE_MODULE="TRAVEL" PRIMARY_KEY="Y" FOREIGN_KEY="N" MANDATORY="Y" INPUT_ALLOWED="N" QUERY_ALLOWED="Y" UPDATABLE="Y" DISPLAYED="Y" INTERFACE="N" DEF_VALUE="" COLUMN_TYPE="DATABASE" PROMPT="Scheduled Tour ID" COLUMN_HELP_TEXT="This Scheduled Tour&apos;s ID number" DOMAIN_NAME="SCHEDTOUR_ID" DATATYPE="NUMBER" TOTAL_LENGTH="4" LENGTH_AFTER_PERIOD="0" DISPLAY_DATATYPE="INT" DISPLAY_LENGTH="0" DISPLAY_LENGTH_AFTER_PERIOD="0" UPPERCASE="N" FIXED_LENGTH="N" IS_SEQNO="Y" UNICODE_TYPE="N" RANGE_LOW="" RANGE_HIGH="" DOMAIN_HELP_TEXT="" REG_EXP="" DOMAIN_MODULE="TRAVEL" IO_FORMAT=""/>

  </Columns>

</Meta>

 

Example 2

This example returns column information about all DATE columns in table RESERVATION, for example, BOOK_DATE, START_DATE, END_DATE and CANCEL_DATE:

SELECT USMeta.Columns( 'RESERVATION', '^[A-Z|_]*DATE$' )

 

 

See Also

USMeta.Tables

USMeta.Relationships