{"id":273,"date":"2020-07-09T14:53:25","date_gmt":"2020-07-09T13:53:25","guid":{"rendered":"https:\/\/www.bowlerblue.com\/site\/?p=273"},"modified":"2020-09-22T10:39:03","modified_gmt":"2020-09-22T09:39:03","slug":"find-columns-with-non-null-data","status":"publish","type":"post","link":"https:\/\/www.bowlerblue.com\/site\/find-columns-with-non-null-data\/","title":{"rendered":"Find columns with non-null data"},"content":{"rendered":"\n<p>Have tables with loads of columns and lots of data &#8211; mostly null &#8211; and want to know which columns across them actually has any data?  Try this.  Oracle specific though.<\/p>\n\n\n\n<p>First, create a table that contains all the tables and columns you want to check &#8211; and some columns for processing output.  You&#8217;ll monitor this table to check progress, and get the output from here in the end.  Lets call this table COLS_TO_CHECK.  You must have dba rights for this.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>create table COLS_TO_CHECK as (\n   select\n      col.owner as schema_name,\n      col.table_name,\n      col.column_name,\n      col.nullable,\n      'N' as processed,\n      ' ' as not_null\n   from\n      sys.dba_tab_columns col\n   where\n      col.owner = 'SCHEMA_OWNER_OF_TABLES_TO_CHECK'\n      and col.table_name in ('LIST','OF','TABLES')\n);<\/code><\/pre>\n\n\n\n<p>You can check progress later in this table &#8211; it will set &#8220;processed&#8221; to &#8220;Y&#8221; when done, and &#8220;not_null&#8221; to either &#8220;Y&#8221; or &#8220;N&#8221;, depending on whether that column has any data in it (&#8220;Y&#8221;) or whether all rows are null (&#8220;N&#8221;).<\/p>\n\n\n\n<p>To actually check the data, run this PL\/SQL (its going to be slow!).  If it dies or gets killed, you can just re-run &#8211; it will carry on from where it last checked.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>declare\n   l_test_statement varchar2(32767);\n   l_contains_value pls_integer;\n\n   cursor c is\n      select schema_name, table_name, column_name, nullable\n      from COLS_TO_CHECK -- the table created above\n      where processed = 'N';\n\nbegin\n   for r in c\n   loop\n      if r.nullable = 'N'\n      then\n         update COLS_TO_CHECK set processed='Y', not_nul ='Y'\n         where schema_name=r.schema_name\n            and table_name=r.table_name\n            and column_name=r.column_name;\n         commit;\n      else\n         begin\n            l_test_statement := 'select 1 from dual where exists (select 1 from ' || r.schema_name || '.' || r.table_name || ' where ' || r.column_name || 'is not null)';\n            execute immediate l_test_statement\n               into l_contains_value;\n            update COLS_TO_CHECK set processed='Y', not_null='Y'\n            where schema_name=r.schema_name\n               and table_name=r.table_name\n               and column_name=r.column_name;\n            commit;\n         exception\n            when no_data_found then\n               update COLS_TO_CHECK set processed='Y', not_null='N'\n               where schema_name=r.schema_name\n                  and table_name=r.table_name\n                  and column_name=r.column_name;\n               commit;\n         end;\n      end if;\n   end loop;\nend;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Have tables with loads of columns and lots of data &#8211; mostly null &#8211; and want to know which columns across them actually has any data? Try this. Oracle specific though. First, create a table that contains all the tables and columns you want to check &#8211; and some columns for processing output. You&#8217;ll monitor [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[12,13],"class_list":["post-273","post","type-post","status-publish","format-standard","hentry","category-database","tag-database","tag-oracle"],"_links":{"self":[{"href":"https:\/\/www.bowlerblue.com\/site\/wp-json\/wp\/v2\/posts\/273","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bowlerblue.com\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bowlerblue.com\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bowlerblue.com\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bowlerblue.com\/site\/wp-json\/wp\/v2\/comments?post=273"}],"version-history":[{"count":2,"href":"https:\/\/www.bowlerblue.com\/site\/wp-json\/wp\/v2\/posts\/273\/revisions"}],"predecessor-version":[{"id":279,"href":"https:\/\/www.bowlerblue.com\/site\/wp-json\/wp\/v2\/posts\/273\/revisions\/279"}],"wp:attachment":[{"href":"https:\/\/www.bowlerblue.com\/site\/wp-json\/wp\/v2\/media?parent=273"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bowlerblue.com\/site\/wp-json\/wp\/v2\/categories?post=273"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bowlerblue.com\/site\/wp-json\/wp\/v2\/tags?post=273"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}