GBase 8s 常用元数据查询语句

GBase 8s 常用元数据查询语句

查询数据库

database sysmaster;

select name, is_logging, is_case_insens from sysdatabases;

> select name, is_logging, is_case_insens from sysdatabases;



name            sysmaster
is_logging      1
is_case_insens  0

name            sysutils
is_logging      1
is_case_insens  0

name            sysuser
is_logging      1
is_case_insens  0

name            sysadmin
is_logging      1
is_case_insens  0

name            gbasedb
is_logging      1
is_case_insens  0

name            mydb
is_logging      1
is_case_insens  0

6 row(s) retrieved.

> 

查询数据库字符集

database sysmaster;

select * from sysdbslocale;

> select * from sysdbslocale;



dbs_dbsname  sysmaster
dbs_collate  en_US.819

dbs_dbsname  sysutils
dbs_collate  en_US.819

dbs_dbsname  sysuser
dbs_collate  en_US.819

dbs_dbsname  sysadmin
dbs_collate  en_US.819

dbs_dbsname  gbasedb
dbs_collate  zh_CN.57372

dbs_dbsname  mydb
dbs_collate  zh_CN.57372

6 row(s) retrieved.

> 

查询表

database <db_name>;

select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';

[gbasedbt@train ~]$ dbaccess - -
Your evaluation license will expire on 2022-06-09 12:00:00
> database mydb;

Database selected.

> create table t_user(f_userid int, f_username varchar(20));

Table created.

> select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';



tabid    102
tabname  t_user
tabtype  T

1 row(s) retrieved.

> 

查询列

database <db_name>;

select colname from syscolumns where tabid = <tab_id>;

select colname, coltype, coltypename from syscolumnsext where tabid = <tab_id> order by colno;

> database mydb;

Database closed.


Database selected.

> select colname from syscolumns where tabid = 102;



colname  f_userid

colname  f_username

2 row(s) retrieved.

> select colname, coltype, coltypename from syscolumnsext where tabid = 102 order by colno;



colname      f_userid
coltype      2
coltypename  INTEGER 

colname      f_username
coltype      13
coltypename  VARCHAR(20) 

2 row(s) retrieved.

> 

查询视图

database <db_name>;

select tabname,tabtype from systables where tabid >= 100 and tabtype = 'V';

> database mydb;

Database closed.


Database selected.

> create view v_user as select * from t_user;

View created.

> select tabname,tabtype from systables where tabid >= 100 and tabtype = 'V';



tabname  v_user
tabtype  V

1 row(s) retrieved.

> 

查询表的索引

database <db_name>;

select tabid, idxname,tabid,idxtype from sysindexes where tabid = <tab_id>;

> database mydb;

Database closed.


Database selected.

> create index idx_user_name on t_user(f_username asc);

Index created.

> select tabid, idxname,tabid,idxtype from sysindexes where tabid = 102;



tabid    102
idxname  idx_user_name
tabid    102