Count all records for all tables

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
Thu Jul 03 07:30:01 CDT 2008

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" wrote:

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

jez123456
Thu Jul 03 07:49:03 CDT 2008

I get the Visual Studio message

'This command is not supported by this provider.'

MrSmersh
Thu Jul 03 08:12:02 CDT 2008

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" wrote:

> I get the Visual Studio message
>
> 'This command is not supported by this provider.'
>
>

jez123456
Thu Jul 03 08:43:02 CDT 2008

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
Thu Jul 03 08:48:57 CDT 2008

On Thu, 3 Jul 2008 05:01:00 -0700, jez123456 <jez123456@discussions.microsoft.com> wrote:

¤ 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.com/2007/09/21/counting-number-of-records-for-all-the-tables-oracle/


Paul
~~~~
Microsoft MVP (Visual Basic)