0

I have a table with 78 columns and 100k rows. Is there a way to find all the blank columns in the table without querying on each column to find their counts?

Running a not null query is time consuming and not feasible for whatever I am trying to do when the table has 100+ rows!! I did run a count query on the column, which resulted in no. of blank values VS no.of other values in the column but running such a county query on all 78 columns is not a feasible option.

Any other easy ways to do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vinney_143
  • 23
  • 11
  • Could you post your solution? Do you want to find specific values (blank / NULL value) without searching for it? That might be tricky, not just in database systems. – data_henrik Aug 23 '20 at 06:14
  • The term "to find all blank columns" needs exact clarification. Consider the following table columns: `PK (primary key), COLA, COLB`, and a couple of rows there. Please, rewrite your question with **the exact result in a table form** you need on such a table. – Mark Barinstein Aug 23 '20 at 08:10
  • Say a total of 5 rows in the table with COLA has got 3 blank meaning no data in it and rest 2 has some data and COLB has all blanks, Is there a query to list below information, COLA - 3 blank/Null values COLB - 5 blan/Null values ( I couldn't get to add a table format for better visibility ) – Vinney_143 Aug 24 '20 at 16:48
  • `running such a county query on all 78 columns is not a feasible option`. Why it's not a feasible option? You can easily generate a single sql query counting rows with null/blank value for every string & clob column in the table, and it doesn't matter, how many such columns your table contains. Do you need such a "query-generator" select statement on SYSCAT.COLUMNS? – Mark Barinstein Aug 25 '20 at 10:59
  • Would you be able to provide a sample query? I can run a count query on each column to find out how many blank values vs values in a column but to run it for each of 78 columns is not a feasible option, right? If your referring to a different approach, please help me understand with a sample query. – Vinney_143 Sep 11 '20 at 23:39

1 Answers1

1

Run runstats on the table

After that examine SYSCAT.COLUMNS and check out NUMNULLS

SELECT HIGH2KEY, LOW2KEY, NUMNULLS
FROM SYSCAT.COLUMNS
WHERE TABNAME = '<yourtable>'

Details for these columns can be found here

MichaelTiefenbacher
  • 3,805
  • 2
  • 11
  • 17
  • It requires admin access, which I don't have. Thanks for the response! you say examine SYSCAT.COLUMNS but the query given is on SYSCAT.TABLES. Is that right? I am not getting to connect between the comment and the actual query posted here. – Vinney_143 Aug 24 '20 at 16:50
  • Sorry my fault SYSCAT.COLUMNS is correct - you need access to it and some admin needs to run runstats - it is always recommended to have a good connection to te DBA - runstats is a regular utility that should run when needed. – MichaelTiefenbacher Aug 24 '20 at 16:56
  • Thanks! Makes sense!! Will see if I can ask my DBAs. – Vinney_143 Aug 24 '20 at 17:06