表创建

CREATE TABLE 
    "test_db".person
    (
        id      integer NOT NULL PRIMARY KEY,
        name    text NOT NULL,
        age     integer NOT NULL,
        address text
    )

CREATE TABLE 
    test_db:person
    (
        id      integer NOT NULL PRIMARY KEY,
        name    text NOT NULL,
        age     integer NOT NULL,
        address text
    )

查询表数量

select count(*)
from systables
where owner = 'test_db';

查询表中数据量

select nrows
from systables
where owner = 'test_db'
  and tabname = 'person2';

但需要注意，如果需要较准确数据，需要手动刷新统计数据

UPDATE STATISTICS

查询表基本元数据

select skip 1 tabname, case
    when tabtype = 'T' then 'Table'
    when tabtype = 'E' then 'External Table'
    when tabtype = 'V' then 'View'
    when tabtype = 'Q' then 'Sequence'
    when tabtype = 'P' then 'Private synonym'
    else 'Public synonym'
    end,
       null::text
from systables
where owner = 'test_db'
  and lower(tabname) like '%p%' limit 1;

查询列数量

select count(*)
from syscolumns c
         join systables t on c.tabid = t.tabid
where t.owner = 'test_db'
  and lower(t.tabname) like '%p%';

查询列基本元数据

select c.colname,
       case
           when bitand(c.coltype, 255) = 0 then 'CHAR'
           when bitand(c.coltype, 255) = 1 then 'SMALLINT'
           when bitand(c.coltype, 255) = 2 then 'INTEGER'
           when bitand(c.coltype, 255) = 3 then 'FLOAT'
           when bitand(c.coltype, 255) = 4 then 'SMALLFLOAT'
           when bitand(c.coltype, 255) = 5 then 'DECIMAL'
           when bitand(c.coltype, 255) = 6 then 'SERIAL'
           when bitand(c.coltype, 255) = 7 then 'DATE'
           when bitand(c.coltype, 255) = 8 then 'MONEY'
           when bitand(c.coltype, 255) = 9 then 'NULL'
           when bitand(c.coltype, 255) = 10 then 'DATETIME'
           when bitand(c.coltype, 255) = 11 then 'BYTE'
           when bitand(c.coltype, 255) = 12 then 'TEXT'
           when bitand(c.coltype, 255) = 13 then 'VARCHAR'
           when bitand(c.coltype, 255) = 14 then 'INTERVAL'
           when bitand(c.coltype, 255) = 15 then 'NCHAR'
           when bitand(c.coltype, 255) = 16 then 'NVARCHAR'
           when bitand(c.coltype, 255) = 17 then 'INT8'
           when bitand(c.coltype, 255) = 18 then 'SERIAL8'
           when bitand(c.coltype, 255) = 19 then 'SET'
           when bitand(c.coltype, 255) = 20 then 'MULTISET'
           when bitand(c.coltype, 255) = 21 then 'LIST'
           when bitand(c.coltype, 255) = 22 then 'ROW (unnamed)'
           when bitand(c.coltype, 255) = 23 then 'COLLECTION'
           when bitand(c.coltype, 255) = 40 then 'LVARCHAR fixed-length opaque types'
           when bitand(c.coltype, 255) = 41 then 'BLOB, BOOLEAN, CLOB variable-length opaque types'
           when bitand(c.coltype, 255) = 43 then 'LVARCHAR (client-side only)'
           when bitand(c.coltype, 255) = 45 then 'BOOLEAN'
           when bitand(c.coltype, 255) = 52 then 'BIGINT'
           when bitand(c.coltype, 255) = 53 then 'BIGSERIAL'
           when bitand(c.coltype, 255) = 2061 then 'IDSSECURITYLABEL'
           when bitand(c.coltype, 255) = 4118 then 'ROW (named)'
           else 'Unknown Type'
           end,
       c.colno = i.part1 and con.constrtype = 'P',
       bitand(c.coltype, 256) = 0,
       null::text
from syscolumns c
         join systables t on c.tabid = t.tabid
         join sysindexes i ON t.tabid = i.tabid
         join sysconstraints con ON i.idxname = con.idxname
where t.owner = 'test_db';