oracle统计用户下所有的表，字段个数，表数据条数，以及表中文名

由于系统表没有直接存储表字段个数，需要写函数根据表名查询单个表的字段个数，直接上sql

create or replace function count_rows(table_name in varchar2,
                                      owner      in varchar2 default null)
  return number authid current_user IS
  num_rows number;
  stmt     varchar2(2000);
begin
  if owner is null then
    stmt := ' select count(*) from user_tab_columns where table_name=upper(''' ||
            table_name || ''')';
  else
    stmt := 'select count(*) from user_tab_columns where table_name=upper( "' ||
            owner || '"."' || table_name || '"';
  end if;
  execute immediate stmt
    into num_rows;
  return num_rows;
end;

然后执行sql

SELECT
	A .TABLE_NAME,
	A .num_rows,
	count_rows (A .table_name),
	b.COMMENTS
FROM
	user_tables A,
	user_tab_comments b
WHERE
	A .TABLE_NAME = b.TABLE_NAME
ORDER BY
	TABLE_NAME;

 执行结果为：