How to Get a count of NULL s in Every Column in a Table | MS SQL Server | TSQL #null #sql

Join this channel to get access to perks:

Join the Telegram Group for the scripts and assistance:

You Can Also Download the scripts from below folder
We strongly believe there is always chance of betterment, so suggestions are most welcome.

Happy learning, and All the Best in your professional journey!

The journey of improvement is ongoing and never be an end.

Connect With me,

#azuresql #azure #sqldba #sqlserverdba #sql #sqlserver #sqlserverdeveloper #performance #performancetuning #performanceoptimization #mssql #mssqlserver #mssqlserverdba

Thank you!
MS SQL DBA Tech Support

[MSSQLDBATechSupport]

DECLARE @schema NVARCHAR(128) = 'myschema'
DECLARE @table NVARCHAR(128) = 'MyTable'

DECLARE @sumCols NVARCHAR(MAX) = ''
DECLARE @unpivotCols NVARCHAR(MAX) = ''
DECLARE @sql NVARCHAR(MAX) = ''

SELECT
@sumCols += '
, SUM(CASE WHEN [' + COLUMN_NAME + '] IS NULL THEN 1 ELSE 0 END) AS [' + COLUMN_NAME + ']'
, @unpivotCols += '
, [' + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table

SET @sql = 'WITH cte AS (
SELECT
, COUNT(*) AS [ROWS IN TABLE]
FROM [' + @schema + '].[' + @table + ']
)
SELECT
'', ['' + ColumnName + '']'' AS ColumnName
, NullCount
FROM cte UNPIVOT (
NullCount FOR ColumnName IN (
, [ROWS IN TABLE]
)
) upvt
ORDER BY
CASE ColumnName WHEN ''ROWS IN TABLE'' THEN 1 ELSE 2 END
, NullCount DESC'

PRINT @sql