Looping through column names with dynamic SQL

user_3059768 0
Viewed 80436

I just came up with an idea for a piece of code to show all the distinct values for each column, and count how many records for each. I want the code to loop through all columns.

Here's what I have so far... I'm new to SQL so bear with the noobness :)

Hard code:

  select [Sales Manager], count(*)
  from  [BT].[dbo].[test]
  group by [Sales Manager]
  order by 2 desc

Attempt at dynamic SQL:

Declare @sql varchar(max),
@column as varchar(255)

    set @column = '[Sales Manager]'
    set @sql = 'select ' + @column + ',count(*) from [BT].[dbo].[test] group by ' + @column + 'order by 2 desc'

    exec (@sql)

Both of these work fine. How can I make it loop through all columns? I don't mind if I have to hard code the column names and it works its way through subbing in each one for @column.

Does this make sense?

Thanks all!

dynamic sql loops
3 Answers

You want to know the distinct coulmn values in all the columns of the table ? Just replace the table name Employee with your table name in the following code:

declare @SQL nvarchar(max)
set @SQL = ''
;with cols as (
select Table_Schema, Table_Name, Column_Name, Row_Number() over(partition by Table_Schema, Table_Name
order by ORDINAL_POSITION) as RowNum
from INFORMATION_SCHEMA.COLUMNS
)

select @SQL = @SQL + case when RowNum = 1 then '' else ' union all ' end
+ ' select ''' + Column_Name + ''' as Column_Name, count(distinct ' + quotename (Column_Name) + ' ) As DistinctCountValue, 
count( '+ quotename (Column_Name) + ') as CountValue FROM ' + quotename (Table_Schema) + '.' + quotename (Table_Name)
from cols
where Table_Name = 'Employee' --print @SQL

execute (@SQL)
user_124201580
Related