How to check a condition against all the columns of a table?

Question

I have a table which has more than 30 columns(all are varchar). I need to list out all the columns which contains blank i.e.' ' values.

I tried using 'coalesce' but it is only for NULL.

Answer 1

The following query will give you all the columns in a table that might have null or '' values. It is written so that you can run it for all tables in your database but you can limit it to a single table, as I have done for this specific example, checking a table called testingNulls:

--two variables needed for table name and column name, when looping through all tables
declare @table varchar(255), @col varchar(255), @sql varchar(max)

--this will be used to store the result, to have one result set instead of one row per each cursor cycle
if object_id('tempdb..#nullcolumns') is not null drop table #nullcolumns
create table #nullcolumns (tablename varchar(255), columnname varchar(255))

declare getinfo cursor for
select t.name tablename, c.name 
from sys.tables t join sys.columns c on t.object_id = c.object_id 
where t.name = 'testingnulls' --here the condition for the table name
open getinfo
fetch next from getinfo into @table, @col
while @@fetch_status = 0
begin
  select @sql = 'if exists (select top 1 * from [' + @table + '] where [' + @col + '] is null or [' + @col + '] like '''' ) begin insert into #nullcolumns select ''' + @table + ''' as tablename, ''' + @col + ''' as all_nulls end'
  print(@sql)
  exec(@sql)
  fetch next from getinfo into @table, @col
end
close getinfo
deallocate getinfo

--this should be the result you need:
select * from #nullcolumns

You can see a working example here. I hope this is what you need.

Answer 2

List all columns that contain a blank in some record? You'd use a query per column and collect the results with UNION ALL:

select 'COL1' where exists (select * from mytable where col1 like '% %')
union all
select 'COL2' where exists (select * from mytable where col2 like '% %')
union all
...
union all
select 'COL30' where exists (select * from mytable where col30 like '% %');