Trafodion 查询表包含字段

Trafodion把所有的表、列、索引等元信息存储在单独的schema下面，这个schema名称为”MD“。下面我们来看下元数据schema下面的内容，

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.

上述结果中的每个表中存储着相应对象的元数据信息，以TABLES元数据表为例，TABLES中存储表的UID、行格式、行长度等信息。

SQL>showddl tables;


CREATE TABLE TRAFODION."_MD_".TABLES
  (
    TABLE_UID                        LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , ROW_FORMAT                       CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
  , IS_AUDITED                       CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
  , ROW_DATA_LENGTH                  INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , ROW_TOTAL_LENGTH                 INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , KEY_LENGTH                       INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , NUM_SALT_PARTNS                  INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , FLAGS                            LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , PRIMARY KEY (TABLE_UID ASC)
  )
;

--- SQL operation complete.
SQL>select * from tables limit 2;

TABLE_UID            ROW_FORMAT IS_AUDITED ROW_DATA_LENGTH ROW_TOTAL_LENGTH KEY_LENGTH  NUM_SALT_PARTNS FLAGS
-------------------- ---------- ---------- --------------- ---------------- ----------- --------------- --------------------
  242752689454723471 AF         Y                       24               81           8               0                    0
  331130927648581756 HF         Y                     2117             2780          16               0                    0

--- 2 row(s) selected.

其他的元数据表这里就不在一一列举，用户可以根据自己的需要查看相应表的内容，这里假如有一个需求：用户希望查询某一个表中所有的列名？这时我们需要用到两张表： OBJECTS和COLUMNS，利用OBJECT_UID关联，具体如下，

SQL>create table seabase.test_query_column (a int, b char(10), c varchar(10));

SQL>select b.column_name
  from trafodion."_MD_".objects a, trafodion."_MD_".columns b
 where a.object_uid = b.object_uid
   and a.object_name = 'TEST_QUERY_COLUMN'
   and a.schema_name = 'SEABASE'
   and b.column_name not in ('_SALT_','SYSKEY','_DIVISON_1_')
order by b.column_number;

COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------
A
B
C

--- 3 row(s) selected.

如果想查询更具体的信息，如列类型、精度等，可以根据COLUMNS表中的信息相应地SELECT出来即可。