使用动态 SQL 循环遍历列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20463365/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Looping through column names with dynamic SQL
提问by Lucas
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 :)
到目前为止，这是我所拥有的......我是 SQL 的新手，所以请忍受 noobness :)
Hard code:
硬编码：
select [Sales Manager], count(*)
from [BT].[dbo].[test]
group by [Sales Manager]
order by 2 desc
Attempt at dynamic SQL:
尝试动态 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.
这两个工作正常。我怎样才能让它循环遍历所有列？我不介意我是否必须对列名进行硬编码，它通过为@column 插入每个列名来工作。
Does this make sense?
这有意义吗？
Thanks all!
谢谢大家！
采纳答案by Szymon
You can use dynamic SQL and get all the column names for a table. Then build up the script:
您可以使用动态 SQL 并获取表的所有列名。然后构建脚本：
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 @tablenameto the name of your table (without the database or schema name).
更改@tablename为您的表的名称（没有数据库或架构名称）。
回答by lc.
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.
这有点像 XY 答案，但如果您不介意对列名进行硬编码，我建议您这样做，并完全避免动态 SQL 和循环。动态 SQL 通常被认为是最后的手段，如果不小心就会导致安全问题（SQL 注入攻击），如果查询和执行计划无法缓存，通常会变慢。
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.
如果您有大量的列名，您可以在 Word 中快速编写一段代码或邮件合并来为您进行替换。
However, as far as how to get column names, assuming this is SQL Server, you can use the following query:
但是，至于如何获取列名，假设这是SQL Server，则可以使用以下查询：
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:
因此，您可以从此查询构建动态 SQL：
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:
或者将整个内容一起编译为一批并执行。这里我们使用FOR XML PATH('')技巧：
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 QUOTENAMEfunctionto escape column names that need escaping.
注意我使用内置QUOTENAME函数来转义需要转义的列名。
回答by Piyush Kulkarni
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:
您想知道表中所有列的不同值吗？只需在以下代码中将表名 Employee 替换为您的表名：
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)
