Count all records for all tables

[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?

 

[MrSmersh]

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.

 

[jez123456]

I get the Visual Studio message

'This command is not supported by this provider.'

 

[MrSmersh]

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

 

[jez123456]

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?

 

[Paul Clement]

¤ 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)