SQL: Select columns with NULL values only

Question

How do I select all the columns in a table that only contain NULL values for all the rows? I'm using MS SQL Server 2005. I'm trying to find out which columns are not used in the table so I can delete them.

Answer 1

SELECT cols FROM table WHERE cols IS NULL

Answer 2

You might need to clarify a bit. What are you really trying to accomplish? If you really want to find out the column names that only contain null values, then you will have to loop through the scheama and do a dynamic query based on that.

I don't know which DBMS you are using, so I'll put some pseudo-code here.

for each col
begin
  @cmd = 'if not exists (select * from tablename where ' + col + ' is not null begin print ' + col + ' end'
exec(@cmd)
end

Answer 3

Or did you want to just see if a column only has NULL values (and, thus, is probably unused)?

Further clarification of the question might help.

EDIT: Ok.. here's some really rough code to get you going...

SET NOCOUNT ON
DECLARE @TableName Varchar(100)
SET @TableName='YourTableName'
CREATE TABLE #NullColumns (ColumnName Varchar(100), OnlyNulls BIT)
INSERT INTO #NullColumns (ColumnName, OnlyNulls) SELECT c.name, 0 FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id AND o.name = @TableName AND o.xtype = 'U'
DECLARE @DynamicSQL AS Nvarchar(2000)
DECLARE @ColumnName Varchar(100)
DECLARE @RC INT
    SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0
    WHILE @@ROWCOUNT > 0
    BEGIN
        SET @RC=0
        SET @DynamicSQL = 'SELECT TOP 1 1 As HasNonNulls FROM ' + @TableName + ' (nolock) WHERE ''' + @ColumnName + ''' IS NOT NULL'
        EXEC sp_executesql @DynamicSQL
        set @RC=@@rowcount
        IF @RC=1
        BEGIN
            SET @DynamicSQL = 'UPDATE #NullColumns SET OnlyNulls=1 WHERE ColumnName=''' + @ColumnName + ''''
            EXEC sp_executesql @DynamicSQL
        END
        ELSE
        BEGIN
            SET @DynamicSQL = 'DELETE FROM #NullColumns WHERE ColumnName=''' + @ColumnName+ ''''
            EXEC sp_executesql @DynamicSQL
        END
    SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0
    END

SELECT * FROM #NullColumns

DROP TABLE #NullColumns
SET NOCOUNT OFF

Yes, there are easier ways, but I have a meeting to go to right now. Good luck!

Answer 4

I would enhance this question. Is there any database independent solution (only with usage of basic SQL).

Answer 5

You can do:

select 
  count(<columnName>)
from
  <tableName>

If the count returns 0 that means that all rows in that column all NULL (or there is no rows at all in the table)

can be changed to

select 
    case(count(<columnName>)) when 0 then 'Nulls Only' else 'Some Values' end
from 
    <tableName>

If you want to automate it you can use system tables to iterate the column names in the table you are interested in

Answer 6

I would also recommend to search for fields which all have the same value, not just NULL.

That is, for each column in each table do the query:

SELECT COUNT(DISTINCT field) FROM tableName

and concentrate on those which return 1 as a result.

Answer 7

This should give you a list of all columns in the table "Person" that has only NULL-values. You will get the results as multiple result-sets, which are either empty or contains the name of a single column. You need to replace "Person" in two places to use it with another table.

DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('Person')
OPEN crs
DECLARE @name sysname
FETCH NEXT FROM crs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('SELECT ''' + @name + ''' WHERE NOT EXISTS (SELECT * FROM Person WHERE ' + @name + ' IS NOT NULL)')
    FETCH NEXT FROM crs INTO @name
END
CLOSE crs
DEALLOCATE crs

Answer 8

Here is the sql 2005 or later version: Replace ADDR_Address with your tablename.

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 = 'ADDR_Address'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

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

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

Answer 9

If you need to list all rows where all the column values are NULL, then i'd use the COLLATE function. This takes a list of values and returns the first non-null value. If you add all the column names to the list, then use IS NULL, you should get all the rows containing only nulls.

SELECT * FROM MyTable WHERE COLLATE(Col1, Col2, Col3, Col4......) IS NULL

You shouldn't really have any tables with ALL the columns null, as this means you don't have a primary key (not allowed to be null). Not having a primary key is something to be avoided; this breaks the first normal form.