9/24/2015

Count all Null values from all columns for each row of the table


Today one of my friend asked me to count null values from all columns of a table for each row so first question which I asked to him: are we confirmed about the number of columns we have in the table? He said what script we need to write if we are not confirmed about the numbers of columns of the table or if we are confirmed about the number of columns so for this I have created a table where I have some user details as:

UserId
UserName
CreatedDate
CreatedBy
ModifiedDate
ModifiedBy
1249
Jhon.K
1/13/2015
Sunil
NULL
NULL
1302
Jenifer.Goldberg
11/11/2014
 
NULL
NULL
2260
Raju.Singh
1/13/2015
Avanish
NULL
NULL
8746
Avanish.Tomar
NULL
Sunil
NULL
NULL
15305
Sonu.Sharma
11/11/2014
Sammy
NULL
NULL
18222
Prateek.Borsi
NULL
Jonny
NULL
NULL
19228
Kavita.Sharma
11/17/2014
NULL
NULL
NULL
19583
Erica.James
NULL
Avanish
NULL
NULL
22698
Alina.Pointing
1/13/2015
Avanish
NULL
NULL
28463
James.Jhon
1/13/2015
Sunil
NULL
NULL

For first scenario: if we know columns of the table than we can use below script to get total count of nulls for each record as:
Select UserID,UserName,CreatedDate,CreatedBy,ModifiedDate, ModifiedBy,

 Case  When UserId is Null Then 1 else 0 end +

 Case  When UserName is Null Then 1 else 0 end +

 Case  When CreatedBy is Null Then 1 else 0 end +

 Case  When CreatedDate is Null Then 1 else 0 end +

 Case  When ModifiedBy is Null Then 1 else 0 end +

 Case  When ModifiedDate is Null Then 1 else 0 end

 as TotalNulls

From Users
For second scenario where we are not confirmed about the numbers of columns we need to fetch columns name from INFORMATION_SCHEMA.COLUMNS table and than we can design a dynamic query to create a case statement for each column to identify null value and at the last we can concatenate this case statement with select statement to get result for each row, as in below SQL script I have fetched all columns from INFORMATION_SCHEMA.COLUMNS table with row number for looping on each column to generate case statement and in the last I have concatenate case statement which is stored in @CaseVal variable with select statement to get total count of all nulls for each row so we need to provide only table name in the @TableName variable and this code will automatically identify the columns for counting nulls for each row:
DECLARE @TableName Nvarchar(100)

SET @TableName='Users'  --->> Enter table Name

IF OBJECT_ID('tempdb.[dbo].[#TT]') IS NOT NULL DROP TABLE [dbo].[#TT]

IF OBJECT_ID('tempdb.[dbo].[#DB]') IS NOT NULL DROP TABLE [dbo].[#DB]

SELECT TABLE_NAME, COLUMN_NAME

, ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY COLUMN_NAME) as RowNo into #TT

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TableName

DECLARE @CaseVal varchar(Max)

SET @CaseVal=' '

Declare @ColumnName varchar(max)

Declare @i int

Set @i=1

While(@i<=(Select Max(RowNo ) From #TT))

Begin

       set @ColumnName=(Select COLUMN_NAME from #TT where RowNo= @i)

       If(@i<(Select Max(RowNo ) From #TT))

       Begin

       Set @CaseVal=@CaseVal+

       'Case  When '+@ColumnName+' is Null Then 1 else 0 end +'     

       END

       If(@i=(Select Max(RowNo ) From #TT))

       BEGIN

       Set @CaseVal=@CaseVal+

       'Case  When '+@ColumnName+' is Null Then 1 else 0 end '      

       END

       Set @i=@i+1

              End

Print 'Select *, '+@CaseVal+ ' as TotalNulls from '+@TableName

               Exec ('Select *, '+@CaseVal+ ' as TotalNulls from '+@TableName)

Bothe scripts result will be as:
UserId
UserName
CreatedDate
CreatedBy
ModifiedDate
ModifiedBy
TotalNulls
1249
Jhon.K
1/13/2015
Sunil
NULL
NULL
2
1302
Jenifer.Goldberg
11/11/2014
NULL
NULL
NULL
3
2260
Raju.Singh
1/13/2015
Avanish
NULL
NULL
2
8746
Avanish.Tomar
NULL
Sunil
NULL
NULL
3
15305
Sonu.Sharma
11/11/2014
Sammy
NULL
NULL
2
18222
Prateek.Borsi
NULL
Jonny
NULL
NULL
3
19228
Kavita.Sharma
11/17/2014
NULL
NULL
NULL
3
19583
Erica.James
NULL
Avanish
NULL
NULL
3
22698
Alina.Pointing
1/13/2015
Avanish
NULL
NULL
2
28463
James.Jhon
1/13/2015
Sunil
1/18/2015
NULL
1

If you have any thoughts or suggestion, feel free to post in the below comment section.
Thanks!