达梦数据库查询数据库所有表名_达梦数据库查询表空间,及各表记录数

1.执行

CREATE OR REPLACE FUNCTION count_rows (

table_name IN varchar2,

owner IN varchar2 := NULL

)

RETURN number AUTHID current_user

AS

num_rows number;

stmt varchar2(2000);

BEGIN

IF owner IS NULL THEN

stmt := 'select count(*) from "' || table_name || '"';

ELSE

stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';

END IF;

EXECUTE IMMEDIATE stmt INTO num_rows;

RETURN num_rows;

END;

2.查询记录数

select table_name, count_rows(table_name) nrows from user_tables

3.查询记录数以及各表占存储

select A.TABLE_NAME,A.nrows,B.OWNER ,B.KB

from (select table_name, count_rows(table_name) nrows from user_tables  group by TABLE_NAME) as A

left join (select owner,segment_name,sum(bytes)/1024 as KB from dba_segments where segment_type='TABLE'group by owner,segment_name) as B

on A.TABLE_NAME=B.SEGMENT_NAME

where B.OWNER='CECPORTAL' order by nrows desc;