# Miscellaneous > SQL Scripts >  count DATA in ALL columns

## oerjan

Hello,

I am pretty new in SQL an have a todo for work. Hopefully you understand my problem as I am not that fluent in english. Anyway:

What I got:
 1 table
 lots of columns

What I need:
 the sum of all different values in all columns 

What I found out:
 SELECT 
 COUNT (DISTINCT SATZ_ID),  
 COUNT (DISTINCT KONSTANTE_5)
 COUNT (...)
 ...
FROM INT_MA1AKI_MIG

## This is pretty much what I need, this shows all the number of different values in those columns BUT I don't want to write COUNT... for all of the columns, that would be too much

QUESTION:
 Is it possible to print out the above automatically without typing so much?

Tanks

----------


## rmiao

Not as I aware of.

----------


## MAK

declare @query varchar(8000)
declare @tablename varchar(128)
declare @col varchar(128)
declare @count1 int

declare @maxcount int

--ADD YOUR TABLENAME HERE
set @tablename ='customersummary'
set @count1=1
set @maxcount =(select count(*) from syscolumns where object_name(id)=@Tablename)
set @query = 'Select '
--print @maxcount

while @count1 <=@maxcount
begin
set @col=(select name from syscolumns where object_name(id)=@Tablename and colorder =@count1)
if @count1<@maxcount 
begin
set @query= @query +' count('+@col+'),'
end
if @count1=@maxcount 
begin
set @query= @query +' count('+@col+')'
end

set @count1=@count1+1
end
set @query = @query+' from '+@tablename
print @query

----------


## MAK

--with column alias

declare @query varchar(8000)
declare @tablename varchar(128)
declare @col varchar(128)
declare @count1 int

declare @maxcount int
--Change it to your table name
set @tablename ='customersummary'
set @count1=1
set @maxcount =(select count(*) from syscolumns where object_name(id)=@Tablename)
set @query = 'Select '
--print @maxcount

while @count1 <=@maxcount
begin
set @col=(select name from syscolumns where object_name(id)=@Tablename and colorder =@count1)
if @count1<@maxcount 
begin
set @query= @query +' count('+@col+') as '+@col+','
end

if @count1=@maxcount 
begin
set @query= @query +' count('+@col+') as '+@col
end

set @count1=@count1+1
end
set @query = @query+' from '+@tablename
print @query

----------

