Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Joining data tables and system tables

Re: Joining data tables and system tables

From: Steve Kass <skass_at_drew.edu>
Date: Sat, 28 Dec 2002 00:15:25 -0500
Message-ID: <aujbts$obn$1@slb2.atl.mindspring.net>


Mikito,

  If the dba doesn't know the names of the tables in the database he or she is administrating, it's a big problem. But the questions you propose don't belong in production code, so the dba could use something like the sp_MSforeachtable that is undocumented but defined in the install directory of SQL Server - with the information_schema views or whatever system tables might exist, a similar procedure could be defined for any RDBMS. It's not important for administrative things like this to be portable, and the questions a dba might ask probably vary depending on the strengths and weaknesses of a particular RDBMS.

  For a dba to check these things out, its probably sufficient to select 'select count(*) from ''' + replace(table_name,'''','''''') + '''' from information_schema.tables

and then run the result as a batch.

SK

Mikito Harakiri wrote:

>You highlighted the end user perspective. DBA, however, doesn't really care
>about application schema; his perspective might be:
>
>"what table contains maximum number of rows"?
>
>If there is a statistics table, then:
>
>"how big are discrepancies between estimated and actual number of rows?"
>
>Somewhat more fancy query:
>
>"Give me all pairs of columns such that column A contains more duplicates of
>each value than column B and there is no foreign key constraint between
>them".
>
>"Steve Kass" <skass_at_drew.edu> wrote in message
>news:auiuiu$nmn$1_at_slb0.atl.mindspring.net...
>
>
>>Paul,
>>
>> The relational model is all about asking questions about data, and
>>it's designed for queries against a known model. If the model is
>>known, then all the table names are known, and you can ask how many
>>rows there are in any table easily
>>
>>select count(*) as numEmployees
>>from Employees
>>
>>You want to be able to ask what entities and relationships are in the
>>model (what are all the tables?). This is metadata, and SQL does
>>provide information_schema views to give you this information, but
>>those aren't really intended for someone who stumbles into a database
>>and asks what planet he is on. They are intended to allow someone to
>>verify that a query is well-formed, or that a proposed entity name is not
>>already in use, and so on.
>>
>>I think it's generally a bad idea when a query returns information from
>>tables that are not hard-coded into the query, because it means the
>>answer will contain information about things the person asking the
>>question doesn't even know exist. What kind of database user would
>>that be? In a typical business, a human relations employee who did not
>>know that the business had employees, insurance carriers, dependents, and
>>so on would not be employeed for long.
>>
>>So if you want to list all the tables and the number of rows in each,
>>put the
>>table names in explicitly.
>>
>>select 'Employees' as TableName, (select count(*) from Employees) as
>>'numRows'
>>union all
>>select 'Dependents', (select count(*) from Dependents)
>>union all
>>...
>>
>>If this doesn't seem right, tell me why it would be the case that someone
>>did not know the names of the tables, yet needed to know how many rows
>>were in each?
>>
>>Steve Kass
>>Drew University
>>
>>Paul wrote:
>>
>>
>>
>>>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.
>>>Is it possible in general relational algebra?
>>>Is it possible in Dataphor or other "next generation" RDBMSs?
>>>
>>>It would seem quite a reasonable thing to want to do.
>>>
>>>Paul.
>>>
>>>
>>>
>>>
>
>
>
>
Received on Fri Dec 27 2002 - 23:15:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US