Trafodion 元数据获取数据库所有表

本短文介绍如何通过一个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