Select all columns except those that have only null values

Question

Is there a way to select column names for a specific table other than those columns with null values ​​without knowing how many columns the table has.

-------------------------
| col1  | col2   | col3 |
------------------------
| val1  | null   | val2 |
| val1  | null   | null |
| null  | null   | val2 |
-------------------------

It should turn out:

------------------------------------
| cols_except_those_with_null_only |
-----------------------------------
| col1                             |
| col3                             |
------------------------------------

Thank!

Answer 1

Create a stored procedure with the following contents:

create table #cols (colname varchar(255), nullCount int)

insert into #cols (colname)
select name from syscolumns where id = object_id('tblTest')

declare @c varchar(255)

declare curCols cursor for select colname from #cols
open curCols
fetch next from curCols into @c
while @@fetch_status = 0 begin
  exec ('update #cols set nullCount = (select count(*) from tblTest where ' + @c + ' is not null) where colname = ''' + @c + '''')
  fetch next from curCols into @c
end
close curCols
deallocate curCols

declare @rv table (cols_expect_those_with_null_only varchar(255))

insert into @rv (cols_expect_those_with_null_only)
select colname from #cols
where nullCount > 0

drop table #cols

select * from @rv

Answer 2

Try this, it is not the most accurate, but it will work, just set it @Tableto the name of your table.

DECLARE @Table AS VARCHAR(100)
SET @Table = 'Example'

DECLARE @TempColumn VARCHAR(100)
DECLARE @Sql NVARCHAR(300)
DECLARE @HasNoNulls INT

CREATE TABLE #Columns (
ColumnName VARCHAR(100)
)

DECLARE ColumnCursor CURSOR FOR 
SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.Columns 
WHERE TABLE_NAME = @Table

OPEN ColumnCursor

FETCH NEXT FROM ColumnCursor
INTO @TempColumn

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'SELECT @HasNoNullsOut = COUNT(*) FROM ' + @Table + ' WHERE ' + @TempColumn + ' IS NOT NULL'
PRINT @SQL
EXECUTE sp_executesql @SQL, N'@HasNoNullsOut int OUTPUT', @HasNoNullsOut=@HasNoNulls OUTPUT

IF @HasNoNulls > 0
BEGIN
    INSERT INTO #Columns
    VALUES(@TempColumn)
END

FETCH NEXT FROM ColumnCursor
INTO @TempColumn
END

CLOSE ColumnCursor
DEALLOCATE ColumnCursor

SELECT * FROM #Columns

DROP TABLE #Columns

Answer 3

, , ,

SELECT a.[name] as 'Table',
  b.[name] as 'Column'
FROM  sysobjects a
INNER JOIN syscolumns b
ON  a.[id] = b.[id]
where table='yourtable'