What SQL query will sum the record count for all tables in a schema?

    Requires Free Membership to View

    Login

First of all you need to analyze all the tables in your schema. The best way to do so is to analyze the entire schema using the following procedure. 
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => '&YOUR;_SCHEMA_PLEASE', CASCADE
 => TRUE);  -- THIS NEEDS TO BE IN ONE LINE

Then the following query in the schema will give you the total record count. Moreover, you can also use the AVG_ROW_LEN and other column values in USER_TABLES to do additional analysis on the schema.

SELECT SUM(NUM_ROWS) FROM USER_TABLES;

You can also be fancy and USER DBA_TABLES and group the QUERY by SCHEMA: 

SELECT TABLE_OWNER, SUM(NUM_ROWS) FROM DBA_TABLES
GROUP BY TABLE_OWNER;

This was first published in June 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top