Looping through column names with dynamic SQL

Question

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!

Answer 1

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).

Answer 2

This is a bit of an XY answer, but if you don't mind hardcoding the column names, I suggest you do just that, and avoid dynamic SQL - and the loop - entirely. Dynamic SQL is generally considered the last resort, opens you up to security issues (SQL injection attacks) if not careful, and can often be slower if queries and execution plans cannot be cached.

If you have a ton of column names you can write a quick piece of code or mail merge in Word to do the substitution for you.

---

However, as far as how to get column names, assuming this is SQL Server, you can use the following query:

SELECT c.name
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.test')

Therefore, you can build your dynamic SQL from this query:

SELECT 'select ' 
    + QUOTENAME(c.name) 
    + ',count(*) from [BT].[dbo].[test] group by ' 
    + QUOTENAME(c.name)  
    + 'order by 2 desc'
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.test')

and loop using a cursor.

Or compile the whole thing together into one batch and execute. Here we use the FOR XML PATH('') trick:

DECLARE @sql VARCHAR(MAX) = (
    SELECT ' select ' --note the extra space at the beginning
        + QUOTENAME(c.name) 
        + ',count(*) from [BT].[dbo].[test] group by ' 
        + QUOTENAME(c.name)  
        + 'order by 2 desc'
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('dbo.test')
    FOR XML PATH('')
)

EXEC(@sql)

Note I am using the built-in QUOTENAME function to escape column names that need escaping.