Oracle查询数据库中所有表的记录数

使用如下语句进行查询:

select t.table_name,t.num_rows from user_tables t

查询出来的结果数据可能是不正确的,或者根本就没数
在这里插入图片描述

执行如下脚本可查询出正确的数据

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;

之后,再查询:

select table_name, count_rows(table_name) nrows from user_tables 
order by nrows desc;
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页