I need to get counts based on each column, has 300 columns in a table

1

I want to give stats on this encounters table, which has close to 300 columns, need to give counts based on each column how many rows are used.

this table has close to 800k rows. need to provide field1 has 700krows, field 2 has 645k rows, like that column by column counts. is it possible to run a query which automatically calculates counts based on each field.

select count(*) from tab_pat_encounters.

thank you very much for the helpful info.

3

So what do you mean column1 has 700k rows and column2 has 645k. Should we assume some columns have null value? Bcs a table with 800k rows means all columns have 800k

4

hi

what yosaisz is saying is true !!
you can modify the query to where column is not not null below

this in one way of doing this
if the nvarchar(max) limit is reached because there are too many columns
then use cursor ..

DECLARE @sql2 NVARCHAR(max)=''; 

SELECT @sql2 += ' select ''[' + column_name + ']'',count([' 
                + column_name + ']) from  ' 
                + Quotename(table_schema) + '.' 
                + Quotename(table_name) 
FROM   information_schema.columns 
WHERE  table_name = 'your table name'; 

EXEC Sp_executesql 
  @sql2

-- cursor method

DECLARE @table_name VARCHAR(50) =''
DECLARE @column_name VARCHAR(50) =''
DECLARE @SQL NVARCHAR(500) =''

SET @table_name = 'TEMP'

DECLARE db_cursor CURSOR FOR 
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME =  @table_name

OPEN db_cursor  FETCH NEXT FROM db_cursor INTO @column_name

WHILE @@FETCH_STATUS = 0  
BEGIN  
      select @SQL =  'select ''['+@column_name +']'',count(['+@column_name +']) from  '+ QUOTENAME(@table_name) 

	  exec( @SQL )

      FETCH NEXT FROM db_cursor INTO @column_name 
	  SET @SQL = ''
END 

CLOSE db_cursor  
DEALLOCATE db_cursor
1 Like
5

Thank you very much Harish, i tried the cursor based script it worked, will make few changes to fit to my requirement. thank you sir.