List of tables having maximum number of rows

[shsandeep]

What is the query to obtain a list of all the tables having the maximum
number of rows in descending order?

Cheers,
Sandeep.

[Mark A]

"shsandeep" <sa**********@gmail.com> wrote in message
news:c7******************************@localhost.ta lkaboutdatabases.com...

What is the query to obtain a list of all the tables having the maximum
number of rows in descending order?

Cheers,
Sandeep.

What is "maximum number of rows"? Do you mean the number of rows sorting in
descending sequence?

You need to run runstats on all tables, and then look at the syscat.tables
view for the number of rows in the table. This is documented in SQL
Reference Vol 1, in the Appendix.

[Pierre Saint-Jacques]

From a command line after connect:
db2 select tabname, card from syscat.tables order by card desc

This presumes that you have run stats and they are current.

HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"shsandeep" <sa**********@gmail.com> a écrit dans le message de news:
c7******************************@loc...tdatabases.com...

What is the query to obtain a list of all the tables having the maximum
number of rows in descending order?

Cheers,
Sandeep.

[Brian Tkatch]

If you don't have runstats, it's a simple UNION ALL.

SELECT 't1', COUNT(*) FROM T1 UNION ALL
SELECT 't2', COUNT(*) FROM T2 UNION ALL
SELECT 't3', COUNT(*) FROM T3 UNION ALL
SELECT 't4', COUNT(*) FROM T4 UNION ALL
SELECT 't5', COUNT(*) FROM T5
ORDER BY 2 DESC
To create the UNION ALL statement:

SELECT VARCHAR('SELECT ''' || TabName || ''', COUNT(*) FROM <schema>.'
|| RTrim(TabName) || ' UNION ALL \', 75) FROM SysCat.Tables WHERE
TabSchema = '<schema>'

The chop off the final UNION ALL and add ORDER BY 2 DESC.

The VARCHAR(75) works for me and keeps it within the client window
without newlines. YMMV.

B.