Friday, September 11, 2009

Find out what colums of a rows have null values - SQL Server

Ever wondered if u cud find out what columns of a row (of a table) have null values. This might help

DECLARE @Table TABLE
(
Id INT IDENTITY(1, 1),
FieldName VARCHAR(100)
)

INSERT INTO @Table (FieldName)
SELECT Column_Name FROM Information_Schema.Columns
WHERE Table_Name = 'Users'
AND Column_Name IN ('Zip', 'Phone', 'DateOfBirth')

CREATE TABLE #Values
(
FieldName VARCHAR(200),
FieldValue VARCHAR(200)
)

DECLARE @RowCount INT, @iLoop INT
DECLARE @FieldName VARCHAR(200)

SET @iLoop = 1
SELECT @RowCount = Count(*) FROM @Table

WHILE (@iLoop <= @RowCount)
BEGIN
SELECT @FieldName = FieldName FROM @Table WHERE Id = @iLoop
EXEC ('INSERT INTO #Values SELECT ''' + @FieldName + ''', ' + @FieldName + ' FROM Users WHERE UserKey = 4')
SET @iLoop = @iLoop + 1
END
SELECT * FROM #Values WHERE FieldValue IS NULL
-- SELECT * FROM #Values WHERE FieldValue IS NOT NULL AND FieldValue <> ''

DROP TABLE #Values

No comments: