CODEWITHSUNDEEP
sqlsql-servert-sql
oshi oshi
oshi oshi

Reputation: 69

Get table's column names which are always null

I have a table with a lot of columns. Some of them are always NULL and does not contain any value.

Is there a way to list those columns using a SQL query instead testing them one by one ?
I'd like to avoid :

SELECT Col1 from MyTable where Col1 IS NOT NULL
SELECT Col2 from MyTable where Col2 IS NOT NULL
...

Upvotes: 3

Views: 1716

Answers (2)

Hadi
Hadi

Reputation: 37313

Assuming you are using SQL Server, just assign your table name to @strTablename

In this example I assumed that dbo.MyTable is the table name

DECLARE @strTablename  varchar(100) = 'dbo.MyTable'
DECLARE @strQuery  varchar(max) = ''
DECLARE @strUnPivot as varchar(max) = ' UNPIVOT ([Count] for [Column] IN ('

CREATE TABLE ##tblTemp([Column] varchar(50), [Count]  Int)

SELECT @strQuery = ISNULL(@strQuery,'') + 'Count([' + name + ']) as [' + name + ']  ,' from sys.columns where object_id = object_id(@strTablename) and is_nullable = 1
SELECT @strUnPivot = ISNULL(@strUnPivot,'') + '[' + name + '] ,' from sys.columns where object_id = object_id(@strTablename) and is_nullable = 1


SET @strQuery = 'SELECT [Column],[Count] FROM ( SELECT ' + SUBSTRING(@strQuery,1,LEN(@strQuery) - 1) + ' FROM ' + @strTablename + ') AS p ' + SUBSTRING(@strUnPivot,1,LEN(@strUnPivot) - 1) + ')) AS unpvt '

INSERT INTO ##tblTemp EXEC (@strQuery)

SELECT [Column] from ##tblTemp Where [Count] =0

DROP TABLE ##tblTemp

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

MAX(col) is null only when all rows are null for this column. So check this for every column and concatenate those names for which the expression is null.

  select 
  'null columns: ' +
  case when max(col1) is null then 'col1 ' else '' end +
  case when max(col2) is null then 'col2 ' else '' end +
  case when max(col3) is null then 'col3 ' else '' end +
  case when max(col4) is null then 'col4 ' else '' end +
  ...
from mytable;

Upvotes: 2

Related Questions