During this post we will discuss about how to find Columns having NULL values in a table. Recently working on SIEBEL migration project ,Client has reported that values are blank or null for most of the columns in Base tables. After the analysis we found the source system has fed the empty values for major list of the columns. Now Business has asked before importing this data into SIEBEL we need to identify the Nullify columns. Though we can run the query on each and every individual columns. But the person who are from SIEBEL background they know the EIM (Enterprise Integration Manager) Tables have long list of vanilla columns along with custom columns as well. So we thought of creating a procedure to check all the columns and identify the list where we have null values for all records.
On the similar lines we thought if we need to implement the same requirement in snowflake. So I have developed the below stored procedure to check the count of each column in table and returns an array for Empty columns.
Currently in learning phase so though of implementing the same with multiple ways. So developed the Javascript procedure and SQL scripting procedure as well. Also I developed the procedure via Python but not including here as would be basic for Python folks.
We will be running the procedure on following table where CRID and SSN are null:
Javascript procedure:
Call the procedure:
call NULL_IDENTIFY(‘EMP’);
Sql Scripting Procedure:
Now call the procedure:
Note: There can be better way but I tried to implement it with Snowflake JS and Sql scripting approach and suggestions are welcome.