CODEWITHSUNDEEP
sql
Drigan
Drigan

Reputation: 200

Count the number of nulls in each column

I've run into a DB that has tables that are excessively wide. (600+ columns) Even asking for the top 100 rows with no parameters takes 4 seconds. I'd like to slim these tables down a bit.

To figure out which columns can be most easily moved to new tables, or removed entirely, I would like to know how many nulls are in each column. This should tell me what information is likely to be least important.

How would I write a query that can find all columns and count the nulls inside those columns?

Edit The DB is SQL server 2008. I'm really hoping not to type each of the columns individually. It looks like sys.columns could help with this?

Edit2 The columns are all different types.

Upvotes: 5

Views: 5584

Answers (2)

roman
roman

Reputation: 117571

Try this

declare @Table_Name nvarchar(max), @Columns nvarchar(max), @stmt nvarchar(max)

declare table_cursor cursor local fast_forward for
    select
        s.name,
        stuff(
            (
                select
                    ', count(case when ' + name + 
                    ' is null then 1 else null end) as count_' + name
                from sys.columns as c
                where c.object_id = s.object_id
                for xml path(''), type
            ).value('data(.)', 'nvarchar(max)')
        , 1, 2, '')
    from sys.tables as s

open table_cursor
fetch table_cursor into @Table_Name, @Columns

while @@FETCH_STATUS = 0
begin
    select @stmt = 'select ''' + @Table_Name + ''' as Table_Name, ' + @Columns + ' from ' + @Table_Name

    exec sp_executesql
        @stmt = @stmt

    fetch table_cursor into @Table_Name, @Columns
end

close table_cursor
deallocate table_cursor

Upvotes: 6

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171579

select count(case when Column1 is null then 1 end) as Column1NullCount,
    count(case when Column2 is null then 1 end) as Column2NullCount,
    count(case when Column3 is null then 1 end) as Column3NullCount,
    ...
from MyTable

Upvotes: 3

Related Questions