This question is the exact opposite of SQL: Select columns with NULL values only.
Given a table with 1024 columns, how to find all columns WITHOUT null values?
Input:a table with 1024 columns
Output:col1_name(no null values) col2_name(no null values)...
If you want to avoid using a CURSOR, this method will simply list out the column names of any columns that have no NULL values in them anywhere in the table... just set the @TableName at the top:
DECLARE @tableName sysname;
DECLARE @sql nvarchar(max);
SET @sql = N'';
SET @tableName = N'Reports_table';
SELECT @sql += 'SELECT CASE WHEN EXISTS (SELECT 1 FROM ' + @tableName + ' WHERE '+ COLUMN_NAME + ' IS NULL) THEN NULL ELSE ''' + COLUMN_NAME +
''' END AS ColumnsWithNoNulls UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName
SELECT @sql = SUBSTRING(@sql, 0, LEN(@sql) - 10);
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results;
CREATE TABLE #Results (ColumnsWithNoNulls sysname NULL);
INSERT INTO #Results EXEC(@sql);
SELECT * FROM #Results WHERE ColumnsWithNoNulls IS NOT NULL
As a bonus, the results are in a temp table, #Results, so you can query to get any information you want... counts, etc.
i modified the Select columns with NULL values only.
to work for your case :
SET ANSI_WARNINGS OFF
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 = 'Reports_table'
OPEN getinfo
FETCH NEXT FROM getinfo into @col
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cmd = 'IF (SELECT sum(iif([' + @col + '] is null,1,null)) FROM Reports_table) is null BEGIN print ''' + @col + ''' end'
exec(@cmd)
FETCH NEXT FROM getinfo into @col
END
CLOSE getinfo
DEALLOCATE getinfo
SET ANSI_WARNINGS on
countexpressions as you have columns and see which are0.