Finding columns with null values in your Database


There are a lot of reasons to want to find the columns with nulls in your SQL Server Database.   For me the main reason is that when processing a SQL Server Analysis Services cube with null values can cause some big problems.      Are you also been trying to find the NULL values in your SQL Server Database?  Well I have just created a quick script that finds them for us.

Find Nulls Script

set nocount on
declare @columnName nvarchar(500)
declare @tableName nvarchar(500)
declare @select nvarchar(500)
declare @sql nvarchar(500)
-- check if the Temp table already exists
if OBJECT_ID('tempdb..#LocalTempTable') is  null
		CREATE TABLE #LocalTempTable(
			TableName varchar(150),
			ColumnName varchar(150))
		truncate table #LocalTempTable; 
-- Build a select for each of the columns in the database.  That checks for nulls
DECLARE check_cursor CURSOR FOR 
select column_name, table_name, concat(' Select ''',column_name,''',''',table_name,''' from ',table_name,' where [',COLUMN_NAME,'] is null')

OPEN check_cursor

FETCH NEXT FROM check_cursor 
INTO @columnName, @tableName,@select

-- Insert it if there if it exists.
set @sql = 'insert into #LocalTempTable (ColumnName, TableName)' + @select
print @sql

-- Run the statment
exec( @sql)

FETCH NEXT FROM check_cursor 
INTO @columnName, @tableName,@select
CLOSE check_cursor;
DEALLOCATE check_cursor;

select distinct ColumnName, TableName from #LocalTempTable 


There are a lot of reasons to want to find the Nulls in your SQL Server Database, this quick script will make that easier.    If you are wondering, yes I am mostly posting this here because I know i will need to do this again someday.

About Linda Lawton

My name is Linda Lawton I have more than 20 years experience working as an application developer and a database expert. I have also been working with Google APIs since 2012 and I have been contributing to the Google .Net client library since 2013. In 2013 I became a a Google Developer Experts for Google Analytics.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.