Count all records for all tables

  • Thread starter Thread starter jez123456
  • Start date Start date
J

jez123456

Hi Experts

Hope I’m in the correct thread.

I’m using VS 2005 and the .NET Framework Data Provider for Oracle to connect
to an Oracle 7 database.

All is ok and I can view and run simple SQL like

SELECT COUNT(*) AS EXPR1
FROM ADDRESSES

I’m trying to write some SQL that will count all the records in all the
tables. I have over 600 tables in the Oracle database.

I’ve tried

SELECT OBJECT_NAME(id) AS Expr1, rows
FROM sysindexes
WHERE (indid IN (1, 0))

Which works ok when connected to SQL Server but not Oracle.

Any ideas?
 
To get all the table names for Oracle use this query
SELECT USER_TABLES.TABLE_NAME FROM USER_TABLES
And there run count on each table.
 
The VisualStudio prompt has some limitations, but I've checked and Oracle
supports Count(*)....
During the checks I've come across this idea
SELECT table_name, nvl(num_rows,1)
FROM dba_tables
 
I still get the Visual Studio message

'This command is not supported by this provider.'

I'm aware that oracle should be able to process the sql, but is this a
problem with the .NET Framework Data Provider for Oracle?
 
¤ Hi Experts
¤
¤ Hope I’m in the correct thread.
¤
¤ I’m using VS 2005 and the .NET Framework Data Provider for Oracle to connect
¤ to an Oracle 7 database.
¤
¤ All is ok and I can view and run simple SQL like
¤
¤ SELECT COUNT(*) AS EXPR1
¤ FROM ADDRESSES
¤
¤ I’m trying to write some SQL that will count all the records in all the
¤ tables. I have over 600 tables in the Oracle database.
¤
¤ I’ve tried
¤
¤ SELECT OBJECT_NAME(id) AS Expr1, rows
¤ FROM sysindexes
¤ WHERE (indid IN (1, 0))
¤
¤ Which works ok when connected to SQL Server but not Oracle.

First, you probably need to do this by schema. Second, if you Google you can probably find a few
script solutions which can be used as stored procedures. I found one below:

http://decipherinfosys.wordpress.co...-number-of-records-for-all-the-tables-oracle/


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top