表创建

CREATE TABLE 
    "testdb".person
    (
        id      integer NOT NULL PRIMARY KEY,
        name    varchar(16) NOT NULL,
        age     integer NOT NULL,
        address varchar(100)
    )

查询表数量

select count(*)
from SYSCAT.TABLES
where lower(TABSCHEMA) = lower('testdb');

查询表中数据量

select CARD
from SYSCAT.TABLES
where lower(TABSCHEMA) = lower('testdb')
  and lower(tabname) = lower('person2');

查询表基本元数据

select tabname,
       case
           when type = 'A' then 'Alias'
           when type = 'G' then 'Created temporary table'
           when type = 'H' then 'Hierarchy table'
           when type = 'L' then 'Detached table'
           when type = 'N' then 'Nickname'
           when type = 'S' then 'Materialized query table'
           when type = 'T' then 'Table'
           when type = 'U' then 'Typed table'
           when type = 'V' then 'View'
           when type = 'W' then 'Typed view'
           else 'Unknow'
           end,
       remarks
from SYSCAT.TABLES
where lower(TABSCHEMA) = lower('testdb')
  and lower(tabname) like lower('%p%')
offset 1 rows fetch next 1 rows only;

查询列数量

select count(*)
from SYSCAT.COLUMNS c
         join SYSCAT.TABLES t on c.TABNAME = t.TABNAME and c.TABSCHEMA = t.TABSCHEMA
where lower(t.TABSCHEMA) = lower('testdb')
  and lower(t.TABNAME) like lower('%p%');

查询列基本元数据

select COLNAME, TYPENAME, c.KEYSEQ is not null, NULLS = 'Y', c.REMARKS
from SYSCAT.COLUMNS c
         join SYSCAT.TABLES t on c.TABNAME = t.TABNAME and c.TABSCHEMA = t.TABSCHEMA
where lower(t.TABSCHEMA) = lower('testdb')
  and lower(t.TABNAME) like lower('%p%');