Home » SQL & PL/SQL » SQL & PL/SQL » count of all tables in a schema
count of all tables in a schema [message #268992] Thu, 20 September 2007 06:51 Go to next message
srilaxmi
Messages: 16
Registered: June 2007
Location: hyderabad
Junior Member

hi all!

how to get the table names and record count(no.of rows) of each table in a schema

thanks & regards
srilaxmi
Re: count of all tables in a schema [message #268995 is a reply to message #268992] Thu, 20 September 2007 06:56 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

query on USER_TABLES.
TABLE_NAME,NUM_ROWS colums are respectively represents the name of the table and no of rows

Cheers
Soumen
Re: count of all tables in a schema [message #268996 is a reply to message #268995] Thu, 20 September 2007 06:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be accurate, num_rows is the number of rows that were in the table last time its statistics were gathered.
Re: count of all tables in a schema [message #269000 is a reply to message #268995] Thu, 20 September 2007 07:07 Go to previous messageGo to next message
srilaxmi
Messages: 16
Registered: June 2007
Location: hyderabad
Junior Member
hi!

in the scott user, i have given a query like this

sql>select table_name,num_rows from user_tables;

then it is dispalying like this
NUM_ROWS TABLE_NAME
--------- ------------------------------

BONUS
EMP
3 LOOKUP
................and so on

it is displaying num_rows for only 1 table

why it is not displaying count for all the tables?
please reply me

thanks & regards
srilaxmi
Re: count of all tables in a schema [message #269003 is a reply to message #269000] Thu, 20 September 2007 07:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
num_rows is the number of rows that were in the table last time its statistics were gathered


If you've never gathered statistics for your tables then this column will be unpopulated.

You need to read up on DBMS_STATS, and particularly the GATHER_TABLE_STATS and GATHER_SCHEMA_STATS subprograms
Previous Topic: change in data format by query for data extraction
Next Topic: inconsistent datatypes: expected - got -
Goto Forum:
  


Current Time: Tue Feb 18 01:39:58 CST 2025