SQL COUNT(column_name) from table name for all columns

Question

I have around 1000 columns. Can we get numbers records of each column in a table, by excluding null values of each field. SQL COUNT(column_name) from table name query excludes null values. But doing this manually for 1000 columns is not possible. Please let me know if there is any other procedure to do it.

Answer 1

Here's an example with dynamic SQL:

/* Param var of the table to be queried */
DECLARE @Table VARCHAR(50) = 'Misc';

/* Build a list of column names to count */

DECLARE @sql VARCHAR(MAX) = ''; -- Important to initialize to an empty string.

SELECT @sql = @sql 
    + CASE WHEN LEN( @sql ) > 0 THEN ', ' ELSE '' END
    + CASE TYPE_NAME( system_type_id )
        WHEN 'text' THEN 'COUNT( CAST([' + [name] + '] AS varchar(1) ) ) AS [' + [name] + '_count]'
        ELSE 'COUNT( [' + [name] + '] ) AS [' + [name] + '_count]'
    END
FROM [sys].[columns] WHERE 
    [object_id] = OBJECT_ID ( @Table );

/* Complete and execute dynamic statement */

SET @sql = 'SELECT ' + @sql + ' FROM ' + @Table + ';';
EXEC( @sql );

Teradata conversion: https://www.jooq.org/translate/

declare Table varchar(50) default 'tblOrders';
declare sql varchar(32000) default '';
select ((@sql + case
  when length(@sql) > 0 then ', '
  else ''
end) + case TYPE_NAME(system_type_id)
  when 'text' then (('COUNT( CAST([' + "name" + '] AS varchar(1) ) ) AS [') + "name" + '_count]')
  else (('COUNT( [' + "name" + '] ) AS [') + "name" + '_count]')
end) @sql
from "sys"."columns"
where "object_id" = OBJECT_ID(@Table);
set sql = (('SELECT ' + @sql + ' FROM ') + @Table + ';');