使用SQL Server从每个表和每个列中获取不同的值 [英] Get distinct values from each table and each column with SQL Server
本文介绍了使用SQL Server从每个表和每个列中获取不同的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想遍历给定数据库的每个表,以获得每个char字段的唯一值。
I would like to loop through each table for a given database to get unique values for each char field.
我的查询可能类似于:
for each table:
for each row in table:
select distinct char_field
from table
loop
loop
我该怎么做?
推荐答案
像这样尝试
DECLARE cur CURSOR FOR
SELECT 'SELECT DISTINCT ' + QUOTENAME(c.COLUMN_NAME) + 'AS ' + QUOTENAME(TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME) + ' FROM ' + QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.DATA_TYPE LIKE '%char%'
DECLARE @cmd VARCHAR(MAX);
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @cmd
EXEC(@cmd);
FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;
此过程将打开一个游标,其中包含所有表的所有列,其中数据类型包含 char
。它将创建一条语句以选择所有不同的值,然后一个接一个地执行。
This process opens a cursor with all columns of all tables, where the data-type contains char
. It will create a statement to select all distinct values and executes this one after the other.
这篇关于使用SQL Server从每个表和每个列中获取不同的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文