SQL: select columns with NULL values ​​only

Question

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

Answer 1

Here is sql version 2005 or later. Replace ADDR_Address with your name.

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 2

 SELECT cols FROM table WHERE cols IS NULL 

Answer 3

This should give you a list of all the columns in the Person table, which has only NULL values. You will get the results as multiple result sets that are either empty or contain the name of a single column. You need to replace "Person" in two places in order 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 4

Or do you want to just see if the column is only NULL (and therefore probably not used)?

Further clarification may help.

EDIT: Okay .. here is some really rude 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 simpler ways, but I have a meeting to go now. Good luck

Answer 5

Here is an updated version of Bryan's request for 2008 and later. It uses INFORMATION_SCHEMA.COLUMNS, adds variables for the table schema and table name. The column data type has been added to the output. Enabling a column data type helps in finding a column for a specific data type. I have not added column width or anything else.

RAISERROR ... WITH NOWAIT is used for output, so the text will be displayed immediately, and not immediately (for the most part) at the end, as 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 6

You can do:

 select count(<columnName>) from <tableName> 

If count returns 0, it means that all rows in this column are NULL (or the table does not have all rows)

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 of interest to you.

Answer 7

I would also recommend looking for fields that all have the same value, not just NULL.

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

 SELECT COUNT(DISTINCT field) FROM tableName 

and focus on those that return 1 as a result.

Answer 8

If you need to list all the rows where all the column values ​​are NULL , I would use the COLLATE function. This takes a list of values ​​and returns the first nonzero value. If you add all column names to the list, use IS NULL , you should get all rows containing only zeros.

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

You should not have tables with ALL columns null, as this means that you do not have a primary key (it is not allowed to be NULL ). The lack of a primary key is something to avoid; this violates the first normal form.

Answer 9

Not sure in 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 10

You will need to iterate over the set of columns and check them. You should be able to get a list of all columns using the 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 bit inconsistent, but SQL does not provide its own method for selecting columns, only rows.

Answer 11

Try it -

 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 12

 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 13

An updated version of "user2466387" with an additional small test that can improve performance because it is useless to test columns with a null value:

 AND IS_NULLABLE = 'YES' 

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 14

Here I created a script for any kind of SQL table. copy this stored procedure, create it in 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 15

You may need to clarify a bit. What are you really trying to achieve? If you really want to find out the names of columns that contain only null values, you will have to scroll through the diagram and perform a dynamic query based on this.

I don’t know which DBMS you are using, so I will put the pseudocode here.

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