Find all the columns which are having null values

Question

I am novice in sql , I'm trying to find all the columns which are having null values in my database. i have searched a lot answers but not satisfied

have you tried `SELECT * FROM tablename WHERE col IS NULL` ? — Kristian, Mar 07 '22 at 06:27

score 1 · Answer 1 · answered Mar 07 '22 at 06:33

Here is the example with MS SQL with later version:

declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'YOUR_TABLE_NAME'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM YOUR_TABLE_NAME WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

score 0 · Answer 2 · answered Mar 07 '22 at 06:37

0

you can try this

SELECT cols
FROM table
WHERE cols IS NULL

answered Mar 07 '22 at 06:37

Shadiqur

490
1
5
18

Please read "[answer]". It helps more if you supply an explanation why this is the preferred solution and explain how it works. We want to educate, not just provide code. – the Tin Man Mar 11 '22 at 05:57

Find all the columns which are having null values

2 Answers2