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 made in USoft Definer 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     Tables
,      column-name-pattern    Columns
,      interfaces-only        InterfacesOnly

,      module-pattern         Module
)
 
interfaces-only  :=  { yes-value | no-value }
 
yes-value        :=  A value from the set { 'yes', 'Y', 'true', '1' } regardless of case
no-value         :=  Any value other than a yes-value

All parameters are optional. Parameter values that you pass are mapped to parameters by alias name. The 'ID' value in the following example is for column-name-pattern because of the "Columns" alias:

SELECT   USMeta.Columns( 'ID' Columns )

(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 4 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.

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

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 in scope regardless of the value of their Interface attribute.

Module-pattern is a regular expression that matches zero, one or more module names in the model. The result data is limited to columns within modules matching the pattern. Any columns 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 column 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.

Example 1

This example returns column information about any columns in table 'GUIDE' that have a Column Name containing the letters 'ID':

SELECT USMeta.Columns( 

   'GUIDE'     Tables

,  '.*ID.*'    Columns

)

The return value of this statement could be:

<?xml version="1.0" encoding="UTF-16"?>
<Meta>
   <Columns>
      <Column TABLE_NAME="GUIDE" COLUMN_NAME="PERSON_ID" MODULE_NAME="TRAVEL" USABLE_AS_INTERFACE="N" PRIMARY_KEY="Y" 

          FOREIGN_KEY="N" MANDATORY="Y" INPUT_ALLOWED="N" QUERY_ALLOWED="Y" UPDATABLE="Y" DISPLAYED="Y" DEF_VALUE="" 

          COLUMN_TYPE="DATABASE" PROMPT="Person No" COLUMN_HELP_TEXT="">
         <Domain NAME="PERSON_ID" MODULE_NAME="TRAVEL" USABLE_AS_INTERFACE="N" DATABASE_TYPE="NUMBER" DISPLAY_DATATYPE="INT" 

             UPPERCASE="N" UNICODE_TYPE="N" TOTAL_LENGTH="5" LENGTH_AFTER_PERIOD="0" DISPLAY_LENGTH="0" DISPLAY_LENGTH_AFTER_PERIOD="0" 

             FIXED_LENGTH="N" DEFAULT_VALUE="" MIN_VALUE="" MAX_VALUE="" HELP_TEXT="" SEQNO_TYPE="RDMBS" SEQNO_COMPONENT="" IO_FORMAT=""/>
      </Column>
   </Columns>
</Meta>

 

Example 2

This example returns column information about all DATE interface columns in table RESERVATION (for example, BOOK_DATE, START_DATE, END_DATE and CANCEL_DATE) that have Interface = Yes:

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

 

 

See also

USMeta internal component