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!

Consternation

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

Procne

I tried this code... It seemed to run but am I supposed to add something? I don't see a loop in there? Cheers, – Consternation

You don't need a loop, all the queries for all columns are in @sql variable. – Procne

Hey Szymon. Okay I think I get it... haha. I get 'command completed succesfully' but no result is shown... What part of your code do I have to change to my own values? – Consternation

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

Ah right. So copy your code exactly... Nothing happens unfortunately. :( The results should show up in the results window right? – Consternation

Can you run just `select * from sys.columns c inner join sys.tables t on c.object_id = t.object_id where t.name = @tablename'? – Procne

Okay. Got a list of column headings. They aren't the same headers as my 'test' file though. They include things like: lock_escalation_desc,object_id, system_type_id and collation_name – Consternation

Ahh. I had to add the line 'Use [BT]' at the top. It wasn't referencing the right database. Oops. It worked. Showing all the results in different windows. Nice :D I guess it would be nice if they were side-by-side but I could imagine that being quite tricky. I guess mass copy paste will have to suffice. Thanks :D – Consternation

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.

Rickrack

Answer 3

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)

Nucleotidase

Answer 4

The code snippet created by @Szymon above is really working.

For those who run the code and did not see anything. Simply study the term "dynamic SQL", it took me only 5 minutes. And another 3 minutes to modify the code run.

Brianabriand