System Tables

Page edited by Steven Hawkins


Changes (2)

...
| UID | string | Table Unique ID

h2. SYSADMIN.StoredProcedures

|| Column Name || Type || Description ||
| VDBName | string | VDB name |
| SchemaName | string | Schema Name |
| Name | string | Procedure name |
| Body | clob | Procedure Definition Body (BEGIN ...)
| UID | string | Unique ID

h1. Procedure Metadata

...
| NullType | string | Nullability: "Nullable", "No Nulls", "Unknown" |

h2. SYSADMIN.StoredProcedures

|| Column Name || Type || Description ||
| VDBName | string | VDB name |
| SchemaName | string | Schema Name |
| Name | string | Procedure name |
| Body | clob | Procedure Definition Body (BEGIN ...)
| UID | string | Unique ID

h1. Datatype Metadata

...

Full Content

VDB, Schema, and Properties

SYSADMIN.VDBResources

This table provides the current VDB contents.

Column Name Type Description
resourcePath string The path to the contents.
contents blob The contents as a blob.

SYS.VirtualDatabases

This table supplies information about the currently connected virtual database, of which there is always exactly one (in the context of a connection).

Column Name Type Description
Name string The name of the VDB
Version string The version of the VDB

SYS.Schemas

This table supplies information about all the schemas in the virtual database, including the system schema itself (System).

Column Name Type Description
VDBName string VDB name
Name string Schema name
IsPhysical boolean True if this represents a source
UID string Unique ID
OID integer Unique ID (see note below)
Description string Description
PrimaryMetamodelURI string URI for the primary metamodel describing the model used for this schema

SYS.Properties

This table supplies user-defined properties on all objects based on metamodel extensions. Normally, this table is empty if no metamodel extensions are being used.

Column Name Type Description
Name string Extension property name
Value string Extension property value
UID string Key unique ID
OID integer Unique ID (see note below)

Table Metadata

SYS.Tables

This table supplies information about all the groups (tables, views, documents, etc) in the virtual database.

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
Name string Short group name
Type string Table type (Table, View, Document, ...)
NameInSource string Name of this group in the source
IsPhysical boolean True if this is a source table
SupportsUpdates boolean True if group can be updated
UID string Group unique ID
OID integer Unique ID (see note below)
Cardinality integer Approximate number of rows in the group
Description string Description
IsSystem boolean True if in system table

SYSADMIN.MatViews

This table supplies information about all the materailized views in the virtual database.

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
Name string Short group name
TargetSchemaName string Name of the materialized table schema
TargetName string Name of the materialized table
Valid boolean True if materialized table is currently valid.
LoadState boolean The load state, can be one of NEEDS_LOADING, LOADING, LOADED, FAILED_LOAD
Updated timestamp The timestamp of the last full refresh.
Cardinality integer The number of rows in the materialized view table.

SYS.Columns

This table supplies information about all the elements (columns, tags, attributes, etc) in the virtual database.

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
TableName string Table name
Name string Element name (not qualified)
Position integer Position in group (1-based)
NameInSource string Name of element in source
DataType string Teiid runtime data type name
Scale integer Number of digits after the decimal point
ElementLength integer Element length (mostly used for strings)
sLengthFixed boolean Whether the length is fixed or variable
SupportsSelect boolean Element can be used in SELECT
SupportsUpdates boolean Values can be inserted or updated in the element
IsCaseSensitive boolean Element is case-sensitive
IsSigned boolean Element is signed numeric value
IsCurrency boolean Element represents monetary value
IsAutoIncremented boolean Element is auto-incremented in the source
NullType string Nullability: "Nullable", "No Nulls", "Unknown"
MinRange string Minimum value
MaxRange string Maximum value
DistinctCount integer Distinct value count, -1 can indicate unknown
NullCount integer Null value count, -1 can indicate unknown
SearchType string Searchability: "Searchable", "All Except Like", "Like Only", "Unsearchable"
Format string Format of string value
DefaultValue string Default value
JavaClass string Java class that will be returned
Precision integer Number of digits in numeric value
CharOctetLength integer Measure of return value size
Radix integer Radix for numeric values
GroupUpperName string Upper-case full group name
UpperName string Upper-case element name
UID string Element unique ID
OID integer Unique ID (see note below)
Description string Description

SYS.Keys

This table supplies information about primary, foreign, and unique keys.

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
Table Name string Table name
Name string Key name
Description string Description
NameInSource string Name of key in source system
Type string Type of key: "Primary", "Foreign", "Unique", etc
IsIndexed boolean True if key is indexed
RefKeyUID string Referenced key UID (if foreign key)
UID string Key unique ID
OID integer Unique ID (see note below)

SYS.KeyColumns

This table supplies information about the columns referenced by a key.

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
TableName string Table name
Name string Element name
KeyName string Key name
KeyType string Key type: "Primary", "Foreign", "Unique", etc
RefKeyUID string Referenced key UID
UID string Key UID
OID integer Unique ID (see note below)
Position integer Position in key

SYSADMIN.Triggers

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
TableName string Table name
Name string Trigger name
TriggerType string Trigger Type
TriggerEvent string Triggering Event
Status string Is Enabled
Body clob Trigger Action (FOR EACH ROW ...)
TableUID string Table Unique ID

SYSADMIN.Views

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
Name string View name
Body clob View Definition Body (SELECT ...)
UID string Table Unique ID

Procedure Metadata

SYS.Procedures

This table supplies information about the procedures in the virtual database.

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
Name string Procedure name
NameInSource string Procedure name in source system
ReturnsResults boolean Returns a result set
UID string Procedure UID
OID integer Unique ID (see note below)
Description string Description

SYS.ProcedureParams

This supplies information on procedure parameters.

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
ProcedureName string Procedure name
Name string Parameter name
DataType string Teiid runtime data type name
Position integer Position in procedure args
Type string Parameter direction: "In", "Out", "InOut", "ResultSet", "ReturnValue"
Optional boolean Parameter is optional
Precision integer Precision of parameter
TypeLength integer Length of parameter value
Scale integer Scale of parameter
Radix integer Radix of parameter
NullType string Nullability: "Nullable", "No Nulls", "Unknown"

SYSADMIN.StoredProcedures

Column Name Type Description
VDBName string VDB name
SchemaName string Schema Name
Name string Procedure name
Body clob Procedure Definition Body (BEGIN ...)
UID string Unique ID

Datatype Metadata

SYS.DataTypes

This table supplies information on datatypes.

Column Name Type Description
Name string Teiid design-time type name
IsStandard boolean Always false
IsPhysical boolean Always false
TypeName string Design-time type name (same as Name)
JavaClass string Java class returned for this type
Scale integer Max scale of this type
TypeLength integer Max length of this type
NullType string Nullability: "Nullable", "No Nulls", "Unknown"
IsSigned boolean Is signed numeric?
IsAutoIncremented boolean Is auto-incremented?
IsCaseSensitive boolean Is case-sensitive?
Precision integer Max precision of this type
Radix integer Radix of this type
SearchType string Searchability: "Searchable", "All Except Like", "Like Only", "Unsearchable"
UID string Data type unique ID
OID integer Unique ID (see note below)
RuntimeType string Teiid runtime data type name
BaseType string Base type
Description string Description of type
The OID column is no longer used on system tables.  You should use UIDs instead.
Stop watching space | Change email notification preferences
View Online | View Changes | Add Comment