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!