Identify Null Columns and Data Null Columns

I saw an interesting question in Facebook group. It’s all related to NULL values.. How to identify NULLable columns in a table and column with data as NULL. I thought of searching it in internet and pointing him to the link but I couldn’t get a reliable post to point it to the user so I finally wrote it. This script will just give you information about nullable columns in a table and whether the nullable column has null values. You can either scan NULL data or you can skip it.

Syntax

EXEC Identify_Null_Column @TableName, @DataNull (Optional)

Script

IF OBJECT_ID('Identify_Null_Column') IS NOT NULL
DROP PROCEDURE Identify_Null_Column
GO
CREATE PROCEDURE Identify_Null_Column
(@TableName VARCHAR(100),@NullData VARCHAR(100)=1)
AS
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM tempdb.sys.tables WHERE name like '#nulltable%')
DROP TABLE #nulltable
CREATE TABLE #nulltable(Table_Name VARCHAR(100), Column_Name VARCHAR(100), Nullable_Column BIT, Data_Null VARCHAR(5))
INSERT INTO #nulltable (Table_Name , Column_Name , Nullable_Column)
SELECT OBJECT_NAME(object_id) 'Table Name',name 'Column Name',is_nullable
FROM sys.columns WHERE object_id = OBJECT_ID (@TableName)
IF @NullData=1
BEGIN
IF EXISTS (SELECT 1 FROM #nulltable  WHERE Nullable_Column = 1)
BEGIN
DECLARE @SQL nvarchar(max);
DECLARE @column sysname;
DECLARE col_cursor CURSOR FOR
SELECT Column_Name     FROM #nulltable  WHERE Nullable_Column = 1
OPEN col_cursor;
FETCH NEXT FROM col_cursor INTO @column;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQL='IF EXISTS (SELECT 1 FROM '+ @TableName +' WHERE ' + @column + ' IS NULL)'
SET @SQL=@SQL+' UPDATE #nulltable SET Data_Null=''Yes'' WHERE Column_Name='''+@column+''''
EXEC (@SQL);
FETCH NEXT FROM col_cursor INTO @column;
END
CLOSE col_cursor;
DEALLOCATE col_cursor;
END
UPDATE #nulltable SET Data_Null = 'No' WHERE Data_Null IS NULL
SELECT Table_Name , Column_Name
, CASE Nullable_Column WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END Nullable_Column
,Data_Null FROM #nulltable
END
ELSE
BEGIN
SELECT Table_Name , Column_Name
, CASE Nullable_Column WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END Nullable_Column FROM #nulltable
END
SET NOCOUNT OFF;
DROP TABLE #nulltable
RETURN;

Executing for a single table with Null data check

EXEC Identify_Null_Column 'TestTableSize'

Executing for a single table without Null data check

EXEC Identify_Null_Column 'TestTableSize',0

Null data check across all table

DECLARE @t TABLE (Table_Name VARCHAR(100), Column_Name VARCHAR(100), Nullable_Column VARCHAR(3), Data_Null VARCHAR(5))
INSERT INTO @t
EXEC sp_MSforeachtable "EXEC Identify_Null_Column '?'"
SELECT * FROM @t

We do have multiple solutions for a single need. This is one way of doing it, If you know a better approach then please share with us in the comments section.