CODEWITHSUNDEEP
sql-server-2008
Shahista Gilani
Shahista Gilani

Reputation: 21

How to get column names where column value is null in SQL Server 2008?

For a given table, I need an SQL (SQL Server 2008 Query) which can get all the column names having column value as NULL.

Upvotes: 2

Views: 5998

Answers (1)

Ivan Golović
Ivan Golović

Reputation: 8832

Following should work, it is filtered by database, schema and table. It retrieves column name when a NULL value exists in any row of a column (but that's just a minor consideration that you can play with and set it any way you want in a query contained in an @sql variable by changing EXISTS/NOT EXISTS conditions):

DECLARE @dbname VARCHAR(100) = 'yourDB'
DECLARE @schemaName VARCHAR(100) = 'yourSchema'
DECLARE @tableName VARCHAR(100) = 'yourTable'
DECLARE @result TABLE (col VARCHAR(4000))

SELECT  @dbname dbname
        ,t.name tbl
        ,c.name col
INTO    #temp
FROM    sys.columns c
JOIN    sys.tables t ON 
        t.object_id = c.object_id
WHERE   c.is_nullable = 1
AND     t.name = @tableName

DECLARE @sql NVARCHAR(MAX) =
STUFF(
(
    SELECT  'UNION ALL SELECT CASE WHEN EXISTS (SELECT 1 FROM ' + @dbname + '.' + @schemaName + '.' + tbl + ' WHERE ' + col + ' IS NULL) THEN '''+ @schemaName  + '.' + tbl + '.' + col+''' END AS NULL_Value_Exists '
    FROM    #temp
    FOR     XML PATH('')
), 1, 10, '   ')

INSERT @result
EXEC(@sql)

SELECT  *
FROM    @result
WHERE   col IS NOT NULL

DROP TABLE #temp

If you would like to get all the columns of all tables from a certain schema that satisfy given conditions you wouldn't even have to specify table name and you would simply join sys.schemas. That would give you a list of all columns from all tables on a specified schema that contain NULL in any row, only thing required would be to change the first query into:

SELECT  @dbname dbname
        ,t.name tbl
        ,c.name col
INTO    #temp
FROM    sys.columns c
JOIN    sys.tables t ON 
        t.object_id = c.object_id
JOIN    sys.schemas s ON 
        s.schema_id = t.schema_id
WHERE   c.is_nullable = 1
AND     s.name =@schemaName

Upvotes: 1

Related Questions