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

User · Answer

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

User · Answer

SELECT cols FROM table WHERE cols IS NULL

User · Answer

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

User · Answer

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  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can do    select    count  lt columnName gt   from    lt tableName gt    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  lt columnName gt    when 0 then  Nulls Only  else  Some Values  end from       lt tableName gt    If you want to automate it you can use system tables to iterate the column names in the table you are interested in

User · Answer

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

User · Answer

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

User · Answer

SELECT cols FROM table WHERE cols IS NULL

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT cols FROM table WHERE cols IS NULL

User · Answer

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

User · Answer

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

User · Answer

You can do    select    count  lt columnName gt   from    lt tableName gt    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  lt columnName gt    when 0 then  Nulls Only  else  Some Values  end from       lt tableName gt    If you want to automate it you can use system tables to iterate the column names in the table you are interested in

User · Answer

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  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can do    select    count  lt columnName gt   from    lt tableName gt    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  lt columnName gt    when 0 then  Nulls Only  else  Some Values  end from       lt tableName gt    If you want to automate it you can use system tables to iterate the column names in the table you are interested in

User · Answer

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

User · Answer

You can do    select    count  lt columnName gt   from    lt tableName gt    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  lt columnName gt    when 0 then  Nulls Only  else  Some Values  end from       lt tableName gt    If you want to automate it you can use system tables to iterate the column names in the table you are interested in

User · Answer

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

User · Answer

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

User · Answer

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  gt  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

User · Answer

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

User · Answer

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

User · Answer

SELECT cols FROM table WHERE cols IS NULL

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] SQL: Select columns with NULL values only

Examples related to sql-server