CODEWITHSUNDEEP
oracle-databasesqlplusrowcount
kimoturbo
kimoturbo

Reputation: 43

How do I get the number of rows in each table I have in my oracle database

Can someone tell me how I can get the number of rows in each table in my oracle database? I have found several queries, but none of them worked because I am using oracle 7 and sqlplus 3.2 and basically all what I found didn't work on it. I just need something that would work on sqlplus 3.2.

Required:

Table Name Rows

Table 1 0

Table 2 5

...

Is it possible to do it with something like a loop? Or what exactly should I do?

Upvotes: 0

Views: 16016

Answers (1)

xQbert
xQbert

Reputation: 35323

if SELECT table_name, num_rows from all_tables doesn't give you what you need.

You could use dynamic SQL and counts as Rahul selected.

Run the below to get results which dynamically build a union on all tables, then run the results as it's own query to get final results.

SELECT 'SELECT ' ||t.name || ' as tName, count(*) as Row_Cnt  
        FROM ' || t.name || ' UNION ALL '
FROM ALL_TABLES t

Just be sure to remove the last union statement on the last query.

Also note: if you don't have access to see the table, it will not come out in this list!

---Updated ------ So if all_tables doesn't exist none of this will work. Since I don't have a oracle 7 instance handy... could you see if SELECT * FROM dictionary returns anything that might produce a list of all the tables? If you find a view or table object use it in place of all_tables above.

I'm reading the docs for oracle 7 now but finding little easily searchable. thus a guess and check method may go faster.

Upvotes: 2

Related Questions