Thursday, 3 November 2016

Find all columns with only nulls in SQL Server table

Find all column fields with only nulls in SQL Server table


The piece of code below will give list of all columns that have only nulls in a give table. Replace YourTableName with your tablename in the code below. This is variation of code that I found here (
http://stackoverflow.com/questions/63291/sql-select-columns-with-null-values-only) 

CREATE TABLE #NullColumns (ColumnName Varchar(100))
DECLARE @TableName Varchar(100)
SET @TableName='YourTableName'
DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID(@TableName)
OPEN crs
DECLARE @name sysname
FETCH NEXT FROM crs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('INSERT INTO #NullColumns (ColumnName) SELECT ''' + @name + ''' WHERE NOT EXISTS (SELECT * FROM ' + @TableName + ' WHERE ' + @name + ' IS NOT NULL)')
    FETCH NEXT FROM crs INTO @name
END
CLOSE crs
DEALLOCATE crs
SELECT * FROM #NullColumns
DROP TABLE #NullColumns

0