基于Oracle编写统计指定Schema表行数列数的动态存储过程

以下是满足需求的PL/SQL存储过程，通过动态SQL实现精准统计指定Schema下所有表的列数与实时行数：

CREATE OR REPLACE PROCEDURE proc_count_schema_tables(p_schema IN VARCHAR2)
IS
    v_table_name VARCHAR2(128);
    v_col_count NUMBER;
    v_row_count NUMBER;
    v_sql VARCHAR2(1000);
BEGIN
    -- 遍历指定Schema下的所有用户表
    FOR tbl IN (SELECT table_name 
                FROM all_tables 
                WHERE owner = UPPER(p_schema)) LOOP
        v_table_name := tbl.table_name;
        
        -- 获取表的列数（从数据字典直接读取，精准可靠）
        SELECT COUNT(column_id)
        INTO v_col_count
        FROM all_tab_columns
        WHERE owner = UPPER(p_schema)
          AND table_name = v_table_name;
        
        -- 动态构造SQL统计实时行数（避免依赖统计信息的num_rows，确保精准）
        v_sql := 'SELECT COUNT(*) FROM ' || UPPER(p_schema) || '.' || v_table_name;
        EXECUTE IMMEDIATE v_sql INTO v_row_count;
        
        -- 输出统计结果
        DBMS_OUTPUT.PUT_LINE('表名: ' || v_table_name 
                            || ' | 列数: ' || v_col_count 
                            || ' | 行数: ' || v_row_count);
    END LOOP;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('指定Schema ' || p_schema || ' 不存在或无可用表');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('处理表 ' || v_table_name || ' 时出错: ' || SQLERRM);
END;
/

关键说明 #

• 列数统计：直接查询all_tab_columns数据字典，获取指定表的列总数，这是数据库存储的元数据，精准无误差。
• 行数统计：通过动态执行SELECT COUNT(*)语句获取实时行数，避免使用all_tables.num_rows（该字段基于统计信息，可能未及时更新，数据不准确）。
• 动态逻辑：通过游标遍历指定Schema下的所有表，针对每个表动态生成统计SQL，适配任意Schema的表结构。

使用示例 #

执行存储过程时传入目标Schema名称（区分大小写，建议传大写或存储过程内统一转大写）：

SET SERVEROUTPUT ON;
EXEC proc_count_schema_tables('SCOTT');

注意事项 #

• 执行存储过程的用户需具备SELECT权限访问指定Schema下的所有表，以及SELECT权限访问all_tables和all_tab_columns数据字典视图。
• 若Schema下存在大量数据的表，COUNT(*)操作可能会消耗一定时间，需根据实际场景评估执行时机。

内容的提问来源于stack exchange，提问作者Triveni Jasti