In this post I demo a script I put together to count NULLs in all columns of a given table in SQL Server and store the results in a table.
Why?
I’ve seen variations of the question “how to count all NULLs in all columns of a table” pop up on reddit once every couple of months, and I figured I’d give it a shot and post here in case anyone else might need it in the future.
The script
Note: as with any script that reads an entire table multiple times, I recommend you avoid running it on busy production systems.
Just provide the table name in any of the following formats:
- TableName
- SchemaName.TableName
- [SchemaName].[TableName]
Every execution of the script has its own timestamp so you can run it multiple times for the same table if you want to track NULL counts over time.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 | /* Get NULL counts for all columns in a table Author: Vlad Drumea More info: From https://github.com/VladDBA/SQL-Server-Scripts/ License https://github.com/VladDBA/SQL-Server-Scripts/blob/main/LICENSE.md */ DECLARE @TabName NVARCHAR(261); /* Set the target table name Valid formats: TableName SchemaName.TableName [SchemaName].[TableName] */ SET @TabName = N'Person.Person'; /*Make sure the table name is valid*/ IF OBJECT_ID(@TabName, N'U') IS NULL BEGIN RAISERROR('Please provide a valid table name',11,1) WITH NOWAIT; RETURN; END; /*Create results table*/ IF OBJECT_ID(N'null_cols_table', N'U') IS NULL BEGIN CREATE TABLE [null_cols_table] ( [id] INT IDENTITY(1, 1) NOT NULL, [table_name] NVARCHAR(128), [column_name] NVARCHAR(128), [column_id] INT, [data_type] NVARCHAR(128), [nullable] BIT, [null_count] BIGINT, [record_count] BIGINT, [non_null_records] AS ( [record_count] - [null_count] ) PERSISTED, [time_of_check] DATETIME2(3) ); /*Create a clustered index on it*/ CREATE CLUSTERED INDEX cix_null_cols_table ON null_cols_table([id]); /*Create index to support the WHERE in the updates*/ CREATE INDEX ncix_null_cols_table ON null_cols_table([table_name], [column_name], [time_of_check]); END; DECLARE @SQL NVARCHAR(MAX), @ParmDefInsert NVARCHAR(500), @ParmDefUpdate NVARCHAR(500), @ColName NVARCHAR(128), @ColID INT, @DataType NVARCHAR(128), @NullCount BIGINT, @TimeStamp DATETIME2(3), @LineFeed NVARCHAR(5); SET @LineFeed = CHAR(13) + CHAR(10); SET @TimeStamp = GETDATE(); SET @ParmDefInsert = N'@TabNameIn NVARCHAR(261), @ColNameIn NVARCHAR(128), @DataTypeIn NVARCHAR(128),'; SET @ParmDefInsert += N' @ColIDIn NVARCHAR(128), @TimeStampIn DATETIME2(3)'; SET @ParmDefUpdate = N'@TabNameIn NVARCHAR(261), @ColNameIn NVARCHAR(128), @TimeStampIn DATETIME2(3)'; /*Cursor for null columns*/ DECLARE NullColl CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT [name], [column_id], TYPE_NAME([system_type_id]) AS [data_type] FROM sys.[all_columns] WHERE [object_id] = OBJECT_ID(@TabName) AND [is_nullable] = 1; OPEN NullColl; FETCH NEXT FROM NullColl INTO @ColName, @ColID, @DataType; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN SET @SQL = N'INSERT INTO null_cols_table ([table_name], [column_name], [data_type],[column_id] ,[nullable],[time_of_check])' SET @SQL += @LineFeed + N'VALUES (@TabNameIn, @ColNameIn,@DataTypeIn, @ColIDIn, 1,@TimeStampIn);' EXECUTE sp_executesql @SQL, @ParmDefInsert, @TabNameIn = @TabName, @ColNameIn = @ColName, @DataTypeIn = @DataType, @ColIDIn = @ColID, @TimeStampIn= @TimeStamp; END; BEGIN SET @SQL = N'UPDATE null_cols_table SET [null_count] = (SELECT COUNT(*) FROM ' + @TabName + N' WITH(NOLOCK) WHERE ' + @ColName + N' IS NULL) ' + @LineFeed + N'WHERE [table_name] = @TabNameIn' + @LineFeed + N'AND [column_name] = @ColNameIn' + @LineFeed + N'AND [time_of_check] = @TimeStampIn;'; EXECUTE sp_executesql @SQL, @ParmDefUpdate, @TabNameIn = @TabName, @ColNameIn = @ColName, @TimeStampIn= @TimeStamp; END; FETCH NEXT FROM NullColl INTO @ColName, @ColID, @DataType; END; CLOSE NullColl; DEALLOCATE NullColl; /*Populate result table with non-nullable columns*/ DECLARE NotNull CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT [name], [column_id], TYPE_NAME([system_type_id]) AS [data_type] FROM sys.[all_columns] WHERE [object_id] = OBJECT_ID(@TabName) AND [is_nullable] = 0; INSERT INTO [null_cols_table] ([table_name], [column_name], [data_type], [column_id], [null_count], [nullable], [time_of_check]) SELECT @TabName, [name], TYPE_NAME([system_type_id]) AS [data_type], [column_id], 0, 0, @TimeStamp FROM sys.[all_columns] WHERE [object_id] = OBJECT_ID(@TabName) AND [is_nullable] = 0; SET @SQL = N'UPDATE null_cols_table SET record_count = (SELECT COUNT(*) FROM ' + @LineFeed + @TabName + N' WITH(NOLOCK))' + @LineFeed + N'WHERE [table_name] = @TabNameIn' + @LineFeed + N'AND [time_of_check] = @TimeStampIn;'; EXECUTE sp_executesql @SQL, @ParmDefUpdate, @TabNameIn = @TabName, @ColNameIn = @ColName, @TimeStampIn= @TimeStamp; /*Return results from this run*/ SELECT * FROM [null_cols_table] WHERE [table_name] = @TabName AND [time_of_check] = @TimeStamp; |
You can also find this script in my SQL Server GitHub repo.
NULL count result set
The result set is pretty self explanatory.
null_count shows the number of records that are NULL for that specific column.
The result set returned is only for the current execution. Data from previous executions can be returned with:
| 1 2 | SELECT * FROM [null_cols_table]; |
Conclusion
This was more of query exercise for myself, but figured I’ll share for anyone else interested in having a count of the nulls in all columns of a table in SQL Server.
If you’re curios about other NULL-related stuff, fell free to check out this post about multiple NULLs in unique constraints