Script to Find out Row Count of All the Tables in a schema

advertisements

_____________________________________________________________________________________________________________________

To retrieve the row count of all the tables in a schema, you can use the following SQL script. This script queries the DBA_TABLES view to get the row count for each table in the specified schema

col table_name for a40 

SELECT
  table_name,
  num_rows
FROM
  all_tables
WHERE
  owner = 'YOUR_SCHEMA_NAME';

advertisements

 

Replace 'YOUR_SCHEMA_NAME' with the name of the schema for which you want to get the row counts. Explanation: The script queries the all_tables view, which contains information about all tables accessible to the current user. Here's what the script does: table_name: This column represents the name of each table. num_rows: This column provides the row count for each table. The WHERE clause filters the results to include only tables from the specified schema. 

 Theory: 

 Knowing the row count of tables in a schema is useful for various purposes, including performance tuning, capacity planning, and data analysis. It helps you understand the volume of data stored in each table and can assist in identifying potential bottlenecks or areas for optimization. It's important to note that the num_rows value in the all_tables view may not always be accurate, as it depends on when statistics were last gathered. If you need precise row counts, you may consider using the COUNT function on the table itself, but this could be resource-intensive for large tables. 

 Sample Output:

TABLE_NAME				   NUM_ROWS
---------------------------------------- ----------
ICOL$					       3996
COL$					     113394
IND$					       1865
TAB$					       1713
CLU$						 10
LOB$						259
COLTYPE$				       2353
SUBCOLTYPE$					 42
NTAB$						443

In this sample output, you see a list of tables in the specified schema along with their corresponding row counts. Please ensure you have the necessary privileges to query the all_tables view and access the specified schema's tables. Test the script in a controlled environment before running it in a production database.

_____________________________________________________________________________________________________________________

Website Stats