本短文介绍如何通过一个SQL语句列出所有数据库中的表,我们知道,Trafodion中有一个”MD“的schema,是用来保存元数据信息的,如表、列、索引、键等,在”MD“下面都有对应的元数据表,
SQL>set schema "_MD_";
--- SQL operation complete.
SQL>get tables;
Tables in Schema TRAFODION._MD_
===============================
AUTHS
COLUMNS
DEFAULTS
INDEXES
KEYS
LIBRARIES
LIBRARIES_USAGE
OBJECTS
REF_CONSTRAINTS
ROUTINES
SEQ_GEN
TABLES
TABLE_CONSTRAINTS
TEXT
UNIQUE_REF_CONSTR_USAGE
VERSIONS
VIEWS
VIEWS_USAGE
--- SQL operation complete.
因此,如果希望查看所有的表,就要利用上面的OBJECTS元数据表,SQL如下
select distinct catalog_name,schema_name,object_name from trafodion."_MD_".OBJECTS where schema_name not like '|_HV|_%%|_' escape '|' and schema_name not like '|_HB|_%%|_' escape '|' and schema_name not like 'VOLATILE_SCHEMA_%%' and object_type in ('BT', 'IX');
举例如下
SQL> select distinct catalog_name,schema_name,object_name from trafodion."_MD_".OBJECTS where schema_name not like '|_HV|_%%|_' escape '|' and schema_name not like '|_HB|_%%|_' escape '|' and schema_name not like 'VOLATILE_SCHEMA_%%' and object_type in ('BT', 'IX');
CATALOG_NAME SCHEMA_NAME OBJECT_NAME
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
TRAFODION _REPOS_ METRIC_QUERY_TABLE
TRAFODION _REPOS_ METRIC_SESSION_TABLE
TRAFODION _REPOS_ METRIC_QUERY_AGGR_TABLE
TRAFODION _REPOS_ METRIC_TEXT_TABLE
TRAFODION _TENANT_MD_ TENANTS
TRAFODION _TENANT_MD_ TENANT_USAGE
TRAFODION SEABASE SB_HISTOGRAMS
TRAFODION SEABASE SB_HISTOGRAM_INTERVALS
TRAFODION SEABASE SB_PERSISTENT_SAMPLES
TRAFODION SEABASE TBL_TGT
TRAFODION SEABASE JSBC_LIVE_EVENTS1
TRAFODION SEABASE TBL_SRC