使用动态 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.

这是我到目前为止所拥有的......我是 SQL 的新手，所以请耐心等待 :)

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

硬编码:

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

尝试动态 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)

这两个都可以正常工作.我怎样才能让它循环遍历所有列?我不介意我是否必须对列名进行硬编码，它通过为 @column 插入每个列名来工作.

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.

这有意义吗?

谢谢大家！

推荐答案

您可以使用动态 SQL 并获取表的所有列名.然后构建脚本:

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)

将 @tablename 更改为您的表的名称(不包括数据库或架构名称).

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

这篇关于使用动态 SQL 循环遍历列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！