Finding columns with null values in your Database


Please Share

SQL

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
	Begin
		CREATE TABLE #LocalTempTable(
			TableName varchar(150),
			ColumnName varchar(150))
		end
else 
	begin 
		truncate table #LocalTempTable; 
	end
-- 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')
from INFORMATION_SCHEMA.COLUMNS

OPEN check_cursor

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

WHILE @@FETCH_STATUS = 0
BEGIN
-- 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
end
CLOSE check_cursor;
DEALLOCATE check_cursor;


select distinct ColumnName, TableName from #LocalTempTable 

Conclusion

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.

Please Share


Linda Lawton

About Linda Lawton

My name is Linda Lawton I have more than 20 years experience working as a developer and a database expert. I have been working with several of the Google APIs, since 2012. I helping others in the On-Line community to develop with the Google APIs, by creating my own blog www.daimto.com. This and my presence on a number of On-Line developer forums lead me to be noticed by the Google Analytics API development team. I was nominated for and recently became one of the first Google Developer Experts for Google Analytics.

Leave a comment

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