How to compare table size

[m2001331]

Hi I need to compare number of records as in before cutover and after cutover from production,which requires me to perform select count(*) from all tables, I would like to know if there are shorter methods to compare?
I have run a script that lists the select count(*) from all tables in tab, but when i run this it just gives the count,
how can i make the table name appear before the count in order to make the comparison easier?
please help.
thanks.

 

[gregsimpson]

I think you simply need to use as.

db2 => select count(*) as first_table_count from my_table

FIRST_TABLE_COUNT
-----------------
160178

1 record(s) selected.

db2 => select count(*) from my_table

1
-----------
160178

1 record(s) selected.

db2 =>

 

[m2001331]

Hi, the suggested solution would give me :-
select count(*) as first_table_count from tab_a;
select count(*) as first_table_count from tab_b;

FIRST_TABLE_COUNT
-----------------
486

FIRST_TABLE_COUNT
-----------------
0

what I am looking for is :-

TAB_A
-----------------
486

TAB_B
-----------------
0

There are more than one table therefore i need the table name to be displayed.
regards.

 

[gregsimpson]

I was merely showing you how to do it. I thought you would be able to apply it from there. Do the following.

select count(*) as tab_a from tab_a;
select count(*) as tab_b from tab_b;

TAB_A
-----------------
486

TAB_B
-----------------
0

 

[m2001331]

The solution is :-
select 'select count(*) '||tname||' from ' || tname || ';' from tab
where tabtype = 'TABLE'

and this will generate a script that lists
the select count(*) of all tables in tab and that can be run to get the table name and count, one by one.

regards.

 

[gregsimpson]

I'm pleased you have finally got a solution.

You posted into the ANSI SQL forum and I gave you a solution which should work under ANSI SQL. Your latest posting clearly isn't ANSI compliant, it is vendor specific in its use of vendor specific system tables and wouldn't run on most RDBMS.

I must say however you latest posting was much clearer than your inital one and explains exactly what your after. I think your idea of putting these into a script in this manner, to be run at a later stage is very good and could easily be adapted for other RDBMS.