abs786123 abs786123 - 26 days ago 5
SQL Question

SQL loop for each column in a table

I have changed my question to show a better example of what I am after.

Say I have a table called:

TableA


The following columns exist in the table are:

Column1, Column2, Column3


what I am trying to accomplish is to see how many records are not null.

to do this I have the following case statement:

sum(Case when Column1 is not null then 1 else 0 end)


What I want is the above case statement for every table that exists from a list provided and to be run for each columns that exists in the table.

So for the above example the case statment will run for Column1, Column2 and Column3 as there are 3 columns in that particular table etc

But I want to specfiy a list of tables to loop through executing the logic above

Answer
create procedure tab_cols (@tab nvarchar(255))
as
begin

    declare     @col_count  nvarchar(max) = ''
               ,@col        nvarchar(max) = ''

    select      @col_count += case ORDINAL_POSITION when 1 then '' else ',' end + 'count(' + COLUMN_NAME + ') as ' + COLUMN_NAME
               ,@col       += case ORDINAL_POSITION when 1 then '' else ',' end + COLUMN_NAME
    from        INFORMATION_SCHEMA.COLUMNS
    where       TABLE_NAME = @tab
    order by    ORDINAL_POSITION

    declare     @stmt nvarchar(max) = 'select * from (select ' + @col_count + ' from ' + @tab + ') t unpivot (val for col in (' + @col + ')) u'

    exec sp_executesql @stmt
end