Q
Problem solve Get help with specific problems with your technologies, process and projects.

SQL query to sum record count

What SQL query will sum the record count for all tables in a schema?
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;

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close