Ce contenu n'est pas disponible dans la langue sélectionnée.

8.2. VDB Metadata

SYSADMIN.Usage

This table supplies information about how views and procedures are defined:

Column Name	Type	Description
VDBName	string	VDB name.
UID	string	Object UID.
object_type	string	Type of object (StoredProcedure, View, and so forth).
Name	string	Object Name or parent name.
ElementName	string	Name of column or parameter (may be null to indicate a table or procedure).
Uses_UID	string	Used object UID.
Uses_object_type	string	Used object type.
Uses_SchemaName	string	Used object schema.
Uses_Name	string	Used object name or parent name.
Uses_ElementName	string	Used column or parameter name (may be null to indicate a table/procedure level dependency).
Schema_Name	string	Schema name.

Every column, parameter, table, or procedure referenced in a procedure or view definition will be shown as used. Likewise every column, parameter, table, or procedure referenced in the expression that defines a view column will be shown as used by that column. Here is an example of how it is used:

SELECT * FROM SYSADMIN.Usage

Recursive common table queries can be used to determine transitive relationships:

with im_using as (
    select 0 as level, uid, Uses_UID, Uses_Name, Uses_Object_Type, Uses_ElementName
      from usage where uid = (select uid from sys.tables where name='table name' and schemaName='schema name')
    union all
    select level + 1, usage.uid, usage.Uses_UID, usage.Uses_Name, usage.Uses_Object_Type, usage.Uses_ElementName
      from usage, im_using where level < 10 and usage.uid = im_using.Uses_UID) select * from im_using

This example finds all outgoing usage:

with uses_me as (
    select 0 as level, uid, Uses_UID, Name, Object_Type, ElementName
      from usage where uses_uid = (select uid from sys.tables where name='table name' and schemaName='schema name')
    union all
    select level + 1, usage.uid, usage.Uses_UID, usage.Name, usage.Object_Type, usage.ElementName
      from usage, uses_me where level < 10 and usage.uses_uid = uses_me.UID) select * from uses_me

SYSADMIN.MatViews: This table supplies information about all the materialized 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. Will be null for internal materialization.
TargetName	string	Name of the materialized table.
Valid	boolean	True if materialized table is currently valid. Will be null for external materialization.
LoadState	boolean	The load state, can be one of NEEDS_LOADING, LOADING, LOADED, FAILED_LOAD. Will be null for external materialization.
Updated	timestamp	The timestamp of the last full refresh. Will be null for external materialization.
Cardinality	integer	The number of rows in the materialized view table. Will be null for external materialization.

Valid, LoadState, Updated, and Cardinality may be checked for external materialized views with the SYSADMIN.matViewStatus procedure:

SELECT * FROM SYSADMIN.MatViews

SYSADMIN.Triggers: This table provides the triggers in the virtual database.

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.

SELECT * FROM SYSADMIN.Triggers

SYSADMIN.Views: This table provides the views in the virtual database.

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.

Here is an example:

SELECT * FROM SYSADMIN.Views

SYSADMIN.StoredProcedures: This table provides the stored procedures in the virtual database.

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.

SELECT * FROM SYSADMIN.StoredProcedures

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
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
ClobValue	clob	Clob Value

Warning

The OID column is no longer used on system tables. Use UID instead.

Ce contenu n'est pas disponible dans la langue sélectionnée.

8.2. VDB Metadata

Apprendre

Essayez, achetez et vendez

Communautés

À propos de la documentation Red Hat

Rendre l’open source plus inclusif

À propos de Red Hat

Red Hat legal and privacy links

Red Hat legal and privacy links