T-SQL How To Count Of Records For Each Column

Question

I have a table with over 120 columns and need to determine which column is used the least. I tried using sql queries to do this, but found T-SQL a bit simpler.

I tried the following but my count comes out as 0 for every column.

Declare  data1 Cursor for select column_name 
        from information_schema.columns 
        where table_name = 'repository' 

Declare @mField nvarchar(255)
Declare @count int

Open data1;

fetch next from  data1 into @mField;

set @count = -1;

while @@fetch_status = 0  
    begin
         select   @count = count(@mField)
                    from  repository where tablereference = 
                    'central' and ( @mField!= null )

        print @mField+' ' ; 
                  print @count;
        Fetch next from  data1 into @mField;
    end

close data1;
deallocate data1;

Answer 1

You need to use some dynamic sql in the middle to acheive your aim here.

    Declare  data1 Cursor for select column_name 
            from information_schema.columns 
            where table_name = 'repository' 

    Declare @mField nvarchar(255)


    Open data1;

    fetch next from  data1 into @mField;


    while @@fetch_status = 0  
    begin
        exec ('

            declare @count int

            select @count = count([' + @mField + '])
                        from  repository  where tablereference = 
                    ''central'' and ( [' + @mField + '] is not null)                                    

            if @count < 10
            begin
                print ''' + @mField + ' '' ; 
                print @count;
            end         
        ')
        Fetch next from  data1 into @mField;
    end

close data1;
deallocate data1;

Answer 2

You can't count values like this because you are only testing if @mField is NULL. The column name isn't substituted.

COUNT ignores NULLs anyway so if you want to count non-null values, do this:

DECLARE @sql varchar(4000)

SET @sql = 'SELECT COUNT(*) AS Total '

SELECT @sql = @sql + ', COUNT(' +  QUOTENAME(column_name) + ') AS ' + QUOTENAME(column_name)
from information_schema.columns 
where table_name = 'repository'

SET @sql = @sql + ' FROM repository'

EXEC (@sql)

This queries the table once for all columns

Answer 3

Your count(@mField) is a count of the litteral value that happens to be in @mField, its not resolving the field name into COUNT(fldBlah), you would need to use dynamic SQL for that.

Declare data1 Cursor FAST_FORWARD for select column_name 
        from information_schema.columns where table_name = 'repository' 

Declare @mField nvarchar(255)
Declare @SQL varchar(1024)
Declare @results table (col_name varchar(128), non_nulls int)

Open data1;
fetch next from data1 into @mField;

while (@@FETCH_STATUS = 0) begin
    set @SQL = 'SELECT ''' + @mField + ''', count(' + @mField + ') from repository where tablereference =  ''central'''
    insert @results
               exec(@SQL)
    Fetch next from data1 into @mField;
end

close data1;
deallocate data1;

select * from @results

For an output like;

col_name            non_nulls
[tablereference]    5
[another_col]       1

Where there are 5 non-null values in column tablereference etc

Answer 4

You need to use IS NOT NULL instead of != NULL