Re: Joining data tables and system tables

From: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 27 Dec 2002 16:54:40 -0800
Message-ID: <bdf69bdf.0212271654.62c172f1_at_posting.google.com>

pbrazier_at_cosmos-uk.co.uk (Paul) wrote in message news:<51d64140.0212270750.236c1839_at_posting.google.com>...
> Can I express this using purely relational operators?:
>
> I want to list all relations (tables) in my database, together with
> the total number of tuples (rows) in each relation.
>
> I can get the list of relations from the system catalog.
> I can use an COUNT aggregate to count the numnber of rows in a given
> relation.
>
> But how do I link the two so the DBMS does the hard work of counting
> each table for me? I know how to use procedural code, "dynamic SQL" or
> other tricks for specific DBMSs; I'm more interested from a
> theoretical viewpoint.
>
> I don't think it's possible in SQL.

It's not a SQL problem, but rather a deficiency of a specific catalog model. Suppose that you have a RDBMS designed like this:

table COLUMNS

---

COL_ID COL_NAME TAB_NAME
------ -------- --------

1      ENAME    EMP 
2      DEPTNO   EMP 
3      DEPTNO   DEPT

table CELLS

---

ROW_ID COL_ID VALUE
------ ------ -----

1      1      SMITH
1      2      10
2      1      KING
2      2      20

Now, the query you are after is:

select table, count(1)
from COLUMNS, CELLS
where columns.col_id = cells.col_id
and columns.col_id in (select max(col_id)

                       from COLUMNS
                       group by tab_name)

group by tab_name

The CELLS table is especially handy when you want to query a rare value, but have no idea what column&table might it contain. Why all values in the database can't be combined into a single table/view? Received on Sat Dec 28 2002 - 01:54:40 CET