Looping through column names with dynamic SQL

dynamic, loops, sql

Solution

You can use dynamic SQL and get all the column names for a table. Then build up the script:

Declare @sql varchar(max) = ''
declare @tablename as varchar(255) = 'test'

select @sql = @sql + 'select [' + c.name + '],count(*) as ''' + c.name +  ''' from [' + t.name + '] group by [' + c.name + '] order by 2 desc; ' 
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name = @tablename

EXEC (@sql)

Change `@tablename` to the name of your table (without the database or schema name).

Problem

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!

Original source