• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

query all fields that are completely NULL

Hi Folks,
I have a table with a few hundred fields. Is there a way to drop all fields that have NULL values?
0
mrosier
Asked:
mrosier
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'drop fields'.  Delete the row?  Delete the column?
0
 
mrosierAuthor Commented:
delete the columns which contain all NULL values. Or as it turns out in the case of this data where = 'NULL'
0
 
LowfatspreadCommented:
before you get to the stage of actually removing data from your system...

can you tell us why you feel it necessary to do this?

have you considered just creating a view over the table which ignores the "empty" columns for your current requirement?
0
 
mrosierAuthor Commented:
taking up space when trying to look at the table. I mean if I can generate a view that ignores all columns that = 'NULL' (because that is how the table is working with it), and make it a table, that would be perfect
0
 
Scott PletcherSenior DBACommented:
Script below will gen SQL to give you the NULL column counts for the table name specified.  You can then just check that table to determine which cols have NULL totals that equal the total rows count.

DECLARE @table_name varchar(200)

SET @table_name = 'dbo.<your_table_name_goes_here>' --<--

DECLARE @sql varchar(max)

SET @sql = ''
SELECT @sql = (
    SELECT CHAR(10) + REPLACE(',SUM(CASE WHEN [$column$] IS NULL THEN 1 ELSE 0 END) AS [$column$_null_count]',
        '$column$', CAST(c.name AS varchar(128)))
    FROM sys.columns c
    WHERE
        c.object_id = OBJECT_ID(@table_name)
    ORDER BY c.column_id
    FOR XML PATH('')
    )

SELECT @sql = 'SELECT SUM(1) AS all_count' +
    @sql + CHAR(10) +
    ' INTO [' + @table_name + '__Col_Counts]' + CHAR(10) +
    ' FROM [' + @table_name + '] WITH (NOLOCK) '
PRINT @sql
--EXEC(@sql)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now