Quoting column names using dynamic SQL

Question

I just came up with the idea of ​​a code snippet to show all the individual values ​​for each column and count how many records there are for each. I want the code to go through all the columns.

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

Hard code:

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

Dynamic SQL Attempt:

 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 works are beautiful. How can I do this across all columns? I don't mind if I have to hardcode the column names and it works its way through subbing in each of them for @column.

It makes sense?

Thanks everyone!

Answer 1

You can use dynamic SQL and get all the column names for the table. Then create a 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 name of the database or schema).

Answer 2

This is a bit of an XY answer, but if you don't mind hard coding for column names, I suggest you do just that and avoid dynamic SQL - and the loop - completely. Dynamic SQL, generally considered the last, opens up security problems (SQL injection attacks) if they are 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 code snippet or merge in Word to make a replacement for you.

---

However, how to get the column names, assuming it 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 with the cursor.

Or compile it all together in 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 use the built-in QUOTENAME function to avoid column names that require escaping.