SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE StudentScore
(
  Student_ID INT PRIMARY KEY,
  Student_Name NVARCHAR (50),
  Student_Score INT
) 



INSERT INTO StudentScore VALUES (1,'Ali', NULL)
INSERT INTO StudentScore VALUES (2,'Zaid', 770)
INSERT INTO StudentScore VALUES (3,'Mohd', 1140)
INSERT INTO StudentScore VALUES (4,NULL, 770)
INSERT INTO StudentScore VALUES (5,'John', 1240)
INSERT INTO StudentScore VALUES (6,'Mike', 1140)
INSERT INTO StudentScore VALUES (7,'Goerge', NULL)
7 rows affected

SELECT 
  FORMATMESSAGE('SELECT col = ''%s.%s.%s'' FROM %s.%s HAVING COUNT(*) != COUNT(%s)', 
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME)
  )
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES';


(No column name)


SELECT col = '[dbo].[StudentScore].[Student_Name]' FROM [dbo].[StudentScore] HAVING COUNT(*) != COUNT([Student_Name])

SELECT col = '[dbo].[StudentScore].[Student_Score]' FROM [dbo].[StudentScore] HAVING COUNT(*) != COUNT([Student_Score])


SELECT col = '[dbo].[StudentScore].[Student_Name]' FROM [dbo].[StudentScore] HAVING COUNT(*) != COUNT([Student_Name])
SELECT col = '[dbo].[StudentScore].[Student_Score]' FROM [dbo].[StudentScore] HAVING COUNT(*) != COUNT([Student_Score])


col


[dbo].[StudentScore].[Student_Name]


Warning: Null value is eliminated by an aggregate or other SET operation.




col


[dbo].[StudentScore].[Student_Score]


Warning: Null value is eliminated by an aggregate or other SET operation.


DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
  FORMATMESSAGE('SELECT table_schema = ''%s''
                        ,table_name = ''%s''
                        ,table_col_name = ''%s'' 
                        ,row_num = COUNT(*)
                        ,row_num_non_nulls = COUNT(%s)
                        ,row_num_nulls = COUNT(*) - COUNT(%s)
                 FROM %s.%s', 
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME)), ' UNION ALL' + CHAR(13)
               ) WITHIN GROUP(ORDER BY TABLE_SCHEMA, TABLE_NAME)

FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES';

SELECT @sql;
EXEC(@sql);


(No column name)


SELECT table_schema = '[dbo]'
                        ,table_name = '[StudentScore]'
                        ,table_col_name = '[Student_Name]' 
                        ,row_num = COUNT(*)
                        ,row_num_non_nulls = COUNT([Student_Name])
                        ,row_num_nulls = COUNT(*) - COUNT([Student_Name])
                 FROM [dbo].[StudentScore] UNION ALL
SELECT table_schema = '[dbo]'
                        ,table_name = '[StudentScore]'
                        ,table_col_name = '[Student_Score]' 
                        ,row_num = COUNT(*)
                        ,row_num_non_nulls = COUNT([Student_Score])
                        ,row_num_nulls = COUNT(*) - COUNT([Student_Score])
                 FROM [dbo].[StudentScore]




table_schema
table_name
table_col_name
row_num
row_num_non_nulls
row_num_nulls


[dbo]
[StudentScore]
[Student_Name]
7
6
1

[dbo]
[StudentScore]
[Student_Score]
7
5
2


Warning: Null value is eliminated by an aggregate or other SET operation.
table_schema	table_name	table_col_name	row_num	row_num_non_nulls	row_num_nulls
[dbo]	[StudentScore]	[Student_Name]	7	6	1
[dbo]	[StudentScore]	[Student_Score]	7	5	2