SQL query shorthand for not selecting null columns when doing select all

Question

Hi guys,

when I do:

SELECT *
 FROM SOMETABLE

I get all the columns from SOMETABLE, but I DON'T want the columns which are NULL (for all records). How do I do this?

Reason: this table has 20 columns, 10 of these are set but 10 of them are null for certain queries. And it is time consuming to type the columnnames....

Thanks,

Voodoo

Answer 1

SQL supports the * wildcard which means all columns. There is no wildcard for all columns except the ones you don't want.

Type out the column names. It can't be more work than asking questions on Stack Overflow. Also, copy & paste is your friend.

Another suggestion is to define a view that selects the columns you want, and then subsequently you can select * from the view any time you want.

Answer 2

It's possible to do, but kind of complicated. You can retrieve the list of columns in a table from INFORMATION_SCHEMA.COLUMNS. For each column, you can run a query to see if any non-null row exists. Finally, you can run a query based on the resulting column list.

Here's one way to do that, with a cursor:

declare @table_name varchar(256)
set @table_name = 'Airports'

declare @rc int
declare @query nvarchar(max)
declare @column_list varchar(256)
declare columns cursor local for select column_name 
    from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table_name
open columns
declare @column_name varchar(256)
fetch next from columns into @column_name
while @@FETCH_STATUS = 0
    begin
    set @query = 'select @rc = count(*) from ' + @table_name + ' where ' + 
        @column_name + ' is not null'

    exec sp_executesql @query = @query, @params = N'@rc int output', 
         @rc = @rc output

    if @rc > 0 
        set @column_list = case when @column_list is null then '' else 
            @column_list + ', ' end + @column_name


    fetch next from columns into @column_name
    end

close columns
deallocate columns

set @query = 'select ' + @column_list + ' from ' + @table_name
exec sp_executesql @query = @query

This runs on SQL Server. It might be close enough for Sybase. Hopefully, this demonstrates that typing out a column list isn't that bad :-)