Get counts of all tables in a schema

Question

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

User · Accepted Answer

This should do it  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  Note  For the benefit of other readers  Oracle does not provide a table called STATS TABLE  you would need to create it

User · Answer

You have to use execute immediate  dynamic sql    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   loop     fetch get tables into v table name v owner       EXIT WHEN get tables NOTFOUND      execute immediate  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   end loop  CLOSE get tables   END

User · Answer

select owner  table name  num rows  sample size  last analyzed from all tables    This is the fastest way to retrieve the row counts but there are a few important caveats    NUM ROWS is only 100  accurate if statistics were gathered in 11g and above with ESTIMATE PERCENT   gt  DBMS STATS AUTO SAMPLE SIZE  the default   or in earlier versions with ESTIMATE PERCENT   gt  100   See this post for an explanation of how  the AUTO SAMPLE SIZE algorithm works in 11g  Results were generated as of LAST ANALYZED  the current results may be different

User · Answer

This can be done with a single statement and some XML magic   select table name          to number extractvalue xmltype dbms xmlgen getxml  select count    c from    owner       table name     ROWSET ROW C    as count from all tables where owner    FOOBAR

User · Answer

If you want simple SQL for Oracle  e g  have XE with no XmlGen  go for a simple 2-step   select    SELECT        table name        as Tablename COUNT    FROM       table name        UNION   from USER TABLES    Copy the entire result and replace the last UNION with a semi-colon        Then as the 2nd step execute the resulting SQL

User · Answer

Get counts of all tables in a schema and order by desc  select  with tmp table name  row number  as    from dual  union all  select  select      table name      count    from    table name    union    from USER TABLES  union all select  select      0 from dual  select table name row number from tmp order by row number desc    from dual    Copy the entire result and  execute

[oracle] Get counts of all tables in a schema

Examples related to oracle

Examples related to plsql