获取模式中所有表的计数 [英] Get counts of all tables in a schema

问题描述

我正在尝试获取模式中所有表的记录计数.我在编写PL/SQL时遇到问题.到目前为止，这是我所做的，但出现错误.请提出任何更改建议:

I am trying to get the record counts of all tables in a schema. I am having trouble writing the PL/SQL. Here is what I have done so far, but I am getting errors. Please suggest any changes:

DECLARE
v_owner varchar2(40);
v_table_name varchar2(40);

cursor get_tables is
select distinct table_name,user
from user_tables
where lower(user) = 'SCHEMA_NAME';


begin

open get_tables;
fetch get_tables into v_table_name,v_owner;

    INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
    SELECT v_table_name,v_owner,COUNT(*),TO_DATE(SYSDATE,'DD-MON-YY') FROM         v_table_name;

CLOSE get_tables;

END;

推荐答案

这应该做到:

declare
    v_count integer;
begin

    for r in (select table_name, owner from all_tables
              where owner = 'SCHEMA_NAME') 
    loop
        execute immediate 'select count(*) from ' || r.table_name 
            into v_count;
        INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
        VALUES (r.table_name,r.owner,v_count,SYSDATE);
    end loop;

end;

我从您的代码中删除了各种错误.

I removed various bugs from your code.

这篇关于获取模式中所有表的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！