What is the query to obtain a list of all the tables having the maximum
number of rows in descending order?
Cheers,
Sandeep. 3 2602
"shsandeep" <sa**********@g mail.com> wrote in message
news:c7******** *************** *******@localho st.talkaboutdat abases.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.
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**********@g mail.com> a écrit dans le message de news: c7************* *************** **...atabase s.com... What is the query to obtain a list of all the tables having the maximum number of rows in descending order?
Cheers, Sandeep.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dusan Chromy |
last post by:
Hi,
I was told there is a feature in Oracle to set up a table to have a
limit on the maximum number of rows in it. By attempt to insert more
rows, the oldest rows should get deleted automatically.
Is there a way to do it, other than writing my own insert trigger?
Thanks,
|
by: Greg |
last post by:
Hello,
I've to manage many 'table' (having same scheme) on the same server.
And I ask myself what could be the best to do (and if you know, why) :
Creating as many database (the name would be a 8byte int value
(converted to a string)) as necessary, all with the same table struct
(and table name), or create 1 database and in it create...
|
by: tom |
last post by:
I am new to SQL administration.
>From a list of IDs that are the primary key in one table (i.e. Customer
Table), I want to make changes in tables that use those IDs as a
foreign key.
Basically I want to say:
If fk_ID is in list then
do these statements to that record
|
by: sandip |
last post by:
Hi,
Can anyone tell me how to calculate the ctual disk space needed for a
table? The record length and number of records are known.
A rough estimate of the disk space would suffice.
Please help.
Thanks,
Sandip.
|
by: Kriston-Vizi Janos |
last post by:
Dear Mr. Kern, and Members,
Thank you very much for the fast answer, my question became
over-simplified.
My source code is appended below. It uses two text files (L.txt and
GC.txt) as input and merges them. Please find these two files here:
http://kristonvizi.hu/L.txt
http://kristonvizi.hu/GC.txt
| |
by: Bob Stearns |
last post by:
I have a table t1(id, other stuff) with 4 dependent (unrelated) tables
ta(id, date, other stuff a), tb(id, date, other stuff b), tc(id, date,
other stuff c), td(id, date, other stuff d). Any or all of the dependent
tables may have rows associated with a given id from t1. What I would
like to do is create a result set with at least 1 row for...
|
by: =?Utf-8?B?U2hhcm9u?= |
last post by:
I'm using the VScrollBar and set it as follow:
m_vScrollBar.Minimum = -19602;
m_vScrollBar.Maximum = 0;
m_vScrollBar.SmallChange = 1;
m_vScrollBar.LargeChange = 1089;
m_vScrollBar.Value = m_vScrollBar.Maximum;
The scroll bar is set to start from the bottom.
|
by: Mr. DOS |
last post by:
IBM DB2 UDB
Is there a SQl query I can send from the web server that will return a
list of tables currently in the LOAD PENDING state?
LOAD QUERY is not quite waht I'm looking for.
Thanks!
|
by: adam.kleinbaum |
last post by:
Hi there,
I'm a novice C programmer working with a series of large (30,000 x
30,000) sparse matrices on a Linux system using the GCC compiler. To
represent and store these matrices, I'd like to implement the sparse
matrices as a doubly-linked list, in which each non-zero cell is
stored roughly as follows:
int rownum
int colnum
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |