表的信息统计


这二个视图分别统计的是哪些库中的表和视图的总量(其中是否包含视图)

有没有做过 检测: 实际累加计算过?

将每一个库的表(或视图)的个数找出来。进行计算总个数

tables是一张视图,是__all_table的子集。
视图的sql如下

CREATE VIEW `TABLES` AS                      select /*+ leading(a) no_use_nl(ts)*/                     cast('def' as char(512)) as TABLE_CATALOG,                     cast(b.database_name as char(64)) as TABLE_SCHEMA,                     cast(a.table_name as char(64)) as TABLE_NAME,                     cast(case when (a.database_id = 201002 or a.table_type = 1) then 'SYSTEM VIEW'                          when a.table_type in (0, 2) then 'SYSTEM TABLE'                          when a.table_type = 4 then 'VIEW'                          else 'BASE TABLE' end as char(64)) as TABLE_TYPE,                     cast(NULL as char(64)) as ENGINE,                     cast(NULL as unsigned) as VERSION,                     cast(NULL as char(10)) as ROW_FORMAT,                     cast( coalesce(ts.row_cnt,0) as unsigned) as TABLE_ROWS,                     cast( coalesce(ts.avg_row_len,0) as unsigned) as AVG_ROW_LENGTH,                     cast( coalesce(ts.data_size,0) as unsigned) as DATA_LENGTH,                     cast(NULL as unsigned) as MAX_DATA_LENGTH,                     cast(NULL as unsigned) as INDEX_LENGTH,                     cast(NULL as unsigned) as DATA_FREE,                     cast(NULL as unsigned) as AUTO_INCREMENT,                     cast(a.gmt_create as datetime) as CREATE_TIME,                     cast(a.gmt_modified as datetime) as UPDATE_TIME,                     cast(NULL as datetime) as CHECK_TIME,                     cast(d.collation as char(32)) as TABLE_COLLATION,                     cast(NULL as unsigned) as CHECKSUM,                     cast(NULL as char(255)) as CREATE_OPTIONS,                     cast(case when a.table_type = 4 then 'VIEW'                              else a.comment end as char(2048)) as TABLE_COMMENT                     from                     (                     select cast(0 as signed) as tenant_id,                            c.database_id,                            c.table_id,                            c.table_name,                            c.collation_type,                            c.table_type,                            usec_to_time(d.schema_version) as gmt_create,                            usec_to_time(c.schema_version) as gmt_modified,                            c.comment                     from oceanbase.__all_virtual_core_all_table c                     join oceanbase.__all_virtual_core_all_table d                       on c.tenant_id = d.tenant_id and d.table_name = '__all_core_table'                     where c.tenant_id = effective_tenant_id()                     union all                     select tenant_id,                            database_id,                            table_id,                            table_name,                            collation_type,                            table_type,                            gmt_create,                            gmt_modified,                            comment                     from oceanbase.__all_table) a                     join oceanbase.__all_database b                     on a.database_id = b.database_id                     and a.tenant_id = b.tenant_id                     join oceanbase.__tenant_virtual_collation d                     on a.collation_type = d.collation_type                     left join (                       select tenant_id,                              table_id,                              row_cnt,                              avg_row_len,                              row_cnt * avg_row_len as data_size                       from oceanbase.__all_table_stat                       where partition_id = -1 or partition_id = table_id) ts                     on a.table_id = ts.table_id                     and a.tenant_id = ts.tenant_id                     where a.tenant_id = 0                     and a.table_type in (0, 1, 2, 3, 4, 14)                     and b.database_name != '__recyclebin'                     and b.in_recyclebin = 0                     and 0 = sys_privilege_check('table_acc', effective_tenant_id(), b.database_name, a.table_name)