Categories
dynamic loops sql

Looping through column names with dynamic SQL

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!