Hi all,

I want to analyse a 9i table in a way that will provide me with summary
information about the contents of each column, like this:

col_1 : this column contains a null in 1000 rows
col_2 : this column contains a null in 500 rows
col_3 : this column contains a null in 0 rows

That's all I need - either the total number of rows where the column is
null, or the total number of rows where it is not null. I'm hoping
there's a portable method that will work for any table, without me
having to name each table / column individually. Does anyone know of a
way to achieve it?

TIA
Tarby

tarbster (AT) yahoo (DOT) com wrote:
The SQL statement is SELECT COUNT(*) but the implementation will require
native dynamic SQL.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

The dba_tab_columns dictionary view contains an estimated count of
nulls in each column based on the optimizer statistics gathering
process. The view also identifies how many unique values exist for
each column, if is is nullable, and a few other interesting facts.
Depending on why you want to create your summary this view may be
useful.

Otherwise as Daniel said select count(*) from x where col1 is null.
You will only need dynamic SQL if you are trying to write a generic
routine that you can use on any of your tables.

HTH -- Mark D Powell --