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.
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
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) + '.'
WHERE table_name = 'your table name';
-- 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
select @SQL = 'select ''['+@column_name +']'',count(['+@column_name +']) from '+ QUOTENAME(@table_name)
exec( @SQL )
FETCH NEXT FROM db_cursor INTO @column_name
SET @SQL = ''
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.