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

How about this? Uncomment the WHERE clause to only work one Table. I altered the code to ignore empty tables since they generate false positives. Dial in the # of rows in case the table was just created.

DROP TABLE IF EXISTS #AllColumns;

DECLARE
    @All    SMALLINT,
    @Cur    SMALLINT,
    @Schema SYSNAME,
    @Table  SYSNAME,
    @Column SYSNAME,
    @SQL    NVARCHAR(4000);

CREATE TABLE #AllColumns
    (
        ID          SMALLINT        IDENTITY(1,1)                           NOT NULL,
        [Schema]    SYSNAME         COLLATE SQL_Latin1_General_CP1_CI_AS    NOT NULL,
        [Table]     SYSNAME         COLLATE SQL_Latin1_General_CP1_CI_AS    NOT NULL,
        [Column]    SYSNAME         COLLATE SQL_Latin1_General_CP1_CI_AS    NOT NULL,
        [Type]      NVARCHAR(50)    COLLATE SQL_Latin1_General_CP1_CI_AS    NOT NULL,
        ColumnID    SMALLINT                                                NOT NULL,
        AllNull     BIT                                                     NULL,
        [Rows]      INT                                                     NOT NULL
    );
DECLARE
    @Tables TABLE
    (
        [Schema]    SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS    NOT NULL,
        [Table]     SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS    NOT NULL,
        [Object_ID] INT                                             NOT NULL,
        [Rows]      INT                                             NOT NULL
    );

BEGIN TRY
    INSERT INTO @Tables( [Schema], [Table], [Object_ID], [Rows] )
    SELECT SCHEMA_NAME( ST.SCHEMA_ID ) AS [Schema], ST.[Name] AS [Table], ST.OBJECT_ID, SP.[Rows]
    FROM sys.tables AS ST -- 323
    INNER JOIN sys.indexes AS SI ON ST.OBJECT_ID = SI.OBJECT_ID
    INNER JOIN sys.partitions AS SP ON SI.OBJECT_ID = SP.OBJECT_ID AND SI.index_id = SP.index_id
    GROUP BY SCHEMA_NAME( ST.SCHEMA_ID ), ST.[Name], ST.OBJECT_ID, SP.[Rows]; -- 322

    INSERT INTO #AllColumns( [Schema], [Table], [Column], [Type], ColumnID, [Rows] )
    SELECT ST.[Schema], ST.[Table], SC.[Name] AS [Column], TP.[Name], SC.Column_ID, ST.[Rows]
    FROM @Tables AS ST
    INNER JOIN sys.columns AS SC ON ST.OBJECT_ID = SC.OBJECT_ID
    INNER JOIN sys.types AS TP ON SC.user_type_id = TP.user_type_id
    WHERE ST.[Rows] > 0 -- AND ST.[Table] = 'Table' AND ST.[Schema] = 'dbo'
    ORDER BY  ST.[Schema], ST.[Table], SC.column_id;

    SELECT @All = @@ROWCOUNT, @Cur = 1;

    WHILE @Cur <= @All
    BEGIN
        SELECT 
            @Schema = [Schema],
            @Table  = [Table],
            @Column = [Column]
        FROM #AllColumns
        WHERE ID = @Cur;

        SET @SQL = '
    UPDATE TC
        SET AllNull = ( CASE WHEN EXISTS( SELECT 1 FROM ' + @Schema + '.' + @Table + ' WHERE ' + QUOTENAME( @Column ) + ' IS NOT NULL ) THEN 0 ELSE 1 END )
    FROM #AllColumns AS TC
    WHERE [Table] = ''' + @Table + ''' AND [Schema] = ''' + @Schema + ''' AND [Column] = ''' + @Column + ''';';

        EXECUTE sys.sp_executeSQL @stmt = @SQL;

        SET @Cur += 1;
    END;

    SELECT [Schema], [Table], [Column], [Rows]
    FROM #AllColumns
    WHERE AllNull = 1
    ORDER BY [Schema], [Table], ColumnID;
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();
END CATCH;

--DROP TABLE IF EXISTS #AllColumns;

Answer 2

Here I have created a script for any kind of SQL table. please copy this stored procedure and create this on your Environment and run this stored procedure with your Table.

exec [dbo].[SP_RemoveNullValues] 'Your_Table_Name'

stored procedure

GO
/****** Object:  StoredProcedure [dbo].[SP_RemoveNullValues]    Script Date: 09/09/2019 11:26:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- akila liyanaarachchi
Create procedure [dbo].[SP_RemoveNullValues](@PTableName Varchar(50) ) as 
begin


DECLARE Cussor CURSOR FOR 
SELECT COLUMN_NAME,TABLE_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @PTableName  

OPEN Cussor;

Declare @ColumnName Varchar(50)
Declare @TableName  Varchar(50)
Declare @DataType Varchar(50)
Declare @Flage  int 

FETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType
WHILE @@FETCH_STATUS = 0
BEGIN

set @Flage=0


If(@DataType in('bigint','numeric','bit','smallint','decimal','smallmoney','int','tinyint','money','float','real'))
begin
set @Flage=1
end 
If(@DataType in('date','atetimeoffset','datetime2','smalldatetime','datetime','time'))
begin
set @Flage=2
end 
If(@DataType in('char','varchar','text','nchar','nvarchar','ntext'))
begin
set @Flage=3
end 

If(@DataType in('binary','varbinary'))
begin
set @Flage=4
end 



DECLARE @SQL VARCHAR(MAX) 

if  (@Flage in(1,4))
begin 

SET @SQL ='  update ['+@TableName+'] set ['+@ColumnName+']=0 where ['+@ColumnName+'] is null'
end 

if  (@Flage =3)
begin 

SET @SQL ='  update ['+@TableName+'] set ['+@ColumnName+'] = '''' where ['+@ColumnName+'] is null '
end 

if  (@Flage =2)
begin 

SET @SQL ='  update ['+@TableName+'] set ['+@ColumnName+'] ='+'''1901-01-01 00:00:00.000'''+' where ['+@ColumnName+'] is null '
end 


EXEC(@SQL)



FETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType
END

CLOSE Cussor
DEALLOCATE Cussor

END

Answer 3

Not actually sure about 2005, but 2008 ate it:

USE [DATABASE_NAME] -- !
GO

DECLARE @SQL NVARCHAR(MAX)
DECLARE @TableName VARCHAR(255)

SET @TableName = 'TABLE_NAME'   -- !

SELECT @SQL = 
(
    SELECT 
        CHAR(10)
        +'DELETE FROM ['+t1.TABLE_CATALOG+'].['+t1.TABLE_SCHEMA+'].['+t1.TABLE_NAME+'] WHERE '
        +(
            SELECT  
            CASE t2.ORDINAL_POSITION 
                WHEN (SELECT MIN(t3.ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS t3 WHERE t3.TABLE_NAME=t2.TABLE_NAME) THEN ''
                ELSE  'AND '
            END
            +'['+COLUMN_NAME+'] IS NULL' AS 'data()'
            FROM INFORMATION_SCHEMA.COLUMNS t2 WHERE t2.TABLE_NAME=t1.TABLE_NAME FOR XML PATH('')
         )  AS 'data()'
    FROM INFORMATION_SCHEMA.TABLES t1 WHERE t1.TABLE_NAME = @TableName FOR XML PATH('')
)

SELECT @SQL -- EXEC(@SQL)

Answer 4

An updated version of 'user2466387' version, with an additional small test which can improve performance, because it's useless to test non nullable columns:

AND IS_NULLABLE = 'YES'

The full code:

SET NOCOUNT ON;

DECLARE
 @ColumnName sysname
,@DataType nvarchar(128)
,@cmd nvarchar(max)
,@TableSchema nvarchar(128) = 'dbo'
,@TableName sysname = 'TableName';

DECLARE getinfo CURSOR FOR
SELECT
     c.COLUMN_NAME
    ,c.DATA_TYPE
FROM
    INFORMATION_SCHEMA.COLUMNS AS c
WHERE
    c.TABLE_SCHEMA = @TableSchema
    AND c.TABLE_NAME = @TableName
    AND IS_NULLABLE = 'YES';

OPEN getinfo;

FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = N'IF NOT EXISTS (SELECT * FROM ' + @TableSchema + N'.' + @TableName + N' WHERE [' + @ColumnName + N'] IS NOT NULL) RAISERROR(''' + @ColumnName + N' (' + @DataType + N')'', 0, 0) WITH NOWAIT;';
    EXECUTE (@cmd);

    FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;
END;

CLOSE getinfo;
DEALLOCATE getinfo;

Answer 5

SELECT  t.column_name
FROM    user_tab_columns t
WHERE   t.nullable = 'Y' AND t.table_name = 'table name here' AND t.num_distinct = 0;

Answer 6

Here is an updated version of Bryan's query for 2008 and later. It uses INFORMATION_SCHEMA.COLUMNS, adds variables for the table schema and table name. The column data type was added to the output. Including the column data type helps when looking for a column of a particular data type. I didn't added the column widths or anything.

For output the RAISERROR ... WITH NOWAIT is used so text will display immediately instead of all at once (for the most part) at the end like PRINT does.

SET NOCOUNT ON;

DECLARE
 @ColumnName sysname
,@DataType nvarchar(128)
,@cmd nvarchar(max)
,@TableSchema nvarchar(128) = 'dbo'
,@TableName sysname = 'TableName';

DECLARE getinfo CURSOR FOR
SELECT
     c.COLUMN_NAME
    ,c.DATA_TYPE
FROM
    INFORMATION_SCHEMA.COLUMNS AS c
WHERE
    c.TABLE_SCHEMA = @TableSchema
    AND c.TABLE_NAME = @TableName;

OPEN getinfo;

FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = N'IF NOT EXISTS (SELECT * FROM ' + @TableSchema + N'.' + @TableName + N' WHERE [' + @ColumnName + N'] IS NOT NULL) RAISERROR(''' + @ColumnName + N' (' + @DataType + N')'', 0, 0) WITH NOWAIT;';
    EXECUTE (@cmd);

    FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;
END;

CLOSE getinfo;
DEALLOCATE getinfo;

Answer 7

SELECT cols
FROM table
WHERE cols IS NULL

Answer 8

Try this -

DECLARE @table VARCHAR(100) = 'dbo.table'

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + 'IF NOT EXISTS(SELECT 1 FROM ' + @table + ' WHERE ' + c.name + ' IS NOT NULL) PRINT ''' + c.name + ''''
FROM sys.objects o
JOIN sys.columns c ON o.[object_id] = c.[object_id]
WHERE o.[type] = 'U'
    AND o.[object_id] = OBJECT_ID(@table)
    AND c.is_nullable = 1

EXEC(@sql)

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.

Answer 10

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 11

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 12

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 13

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 14

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 15

You'll have to loop over the set of columns and check each one. You should be able to get a list of all columns with a DESCRIBE table command.

Pseudo-code:

foreach $column ($cols) {
   query("SELECT count(*) FROM table WHERE $column IS NOT NULL")
   if($result is zero)  {
      # $column contains only null values"
      push @onlyNullColumns, $column;
   } else {
      # $column contains non-null values
   }
}
return @onlyNullColumns;

I know this seems a little counterintuitive but SQL does not provide a native method of selecting columns, only rows.

Answer 16

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