问题 如何确定表是否有一些唯一的列


我使用的是MS SQL Server。

我已经交了一些没有约束的大桌子，没有钥匙也没有。

我知道有些列具有唯一值。对于给定的表，是否有一种聪明的方法来查找具有唯一值的cols？

现在，我通过计算是否存在与表中的行一样多的DISTINCT值来手动为每列执行此操作。

SELECT COUNT(DISTINCT col) FROM table

可能会让一个cusor循环遍历所有列，但想知道是否有人知道更聪明或内置函数。

谢谢。


5682
2017-08-04 12:00


起源

答案:

这是一种与@JNK基本类似的方法，但它不是打印计数，而是为每个列返回一个现成的答案，告诉您列是否仅包含唯一值：

DECLARE @table varchar(100), @sql varchar(max);
SET @table = 'some table name';

SELECT
  @sql = COALESCE(@sql + ', ', '') + ColumnExpression
FROM (
  SELECT
    ColumnExpression =
      'CASE COUNT(DISTINCT ' + COLUMN_NAME + ') ' +
      'WHEN COUNT(*) THEN ''UNIQUE'' ' +
      'ELSE '''' ' +
      'END AS ' + COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @table
) s

SET @sql = 'SELECT ' + @sql + ' FROM ' + @table;
PRINT @sql;  /* in case you want to have a look at the resulting query */
EXEC(@sql);

它只是比较 COUNT(DISTINCT column) 同 COUNT(*) 对于每一列。结果将是一个包含单行的表，其中每列都包含该值 UNIQUE 对于那些没有重复项的列，如果存在重复项则为空字符串。

但上述解决方案仅适用于那些没有NULL的列。应该注意，当您想要在列上创建唯一约束/索引时，SQL Server不会忽略NULL。如果一列只包含一个NULL且所有其他值都是唯一的，您仍然可以在列上创建一个唯一约束（但是，您不能将它作为主键，这需要值的唯一性和缺少NULL）。

因此，您可能需要对内容进行更全面的分析，您可以使用以下脚本获得：

DECLARE @table varchar(100), @sql varchar(max);
SET @table = 'some table name';

SELECT
  @sql = COALESCE(@sql + ', ', '') + ColumnExpression
FROM (
  SELECT
    ColumnExpression =
      'CASE COUNT(DISTINCT ' + COLUMN_NAME + ') ' +
      'WHEN COUNT(*) THEN ''UNIQUE'' ' +
      'WHEN COUNT(*) - 1 THEN ' +
        'CASE COUNT(DISTINCT ' + COLUMN_NAME + ') ' +
        'WHEN COUNT(' + COLUMN_NAME + ') THEN ''UNIQUE WITH SINGLE NULL'' ' +
        'ELSE '''' ' +
        'END ' +
      'WHEN COUNT(' + COLUMN_NAME + ') THEN ''UNIQUE with NULLs'' ' +
      'ELSE '''' ' +
      'END AS ' + COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @table
) s

SET @sql = 'SELECT ' + @sql + ' FROM ' + @table;
PRINT @sql;  /* in case you still want to have a look at the resulting query */
EXEC(@sql);

此解决方案通过检查三个值来考虑NULL： COUNT(DISTINCT column)， COUNT(column) 和 COUNT(*)。它显示的结果与前一种解决方案类似，但对列的可能诊断更加多样化：

  • UNIQUE 表示没有重复值且没有NULL（可以是PK或具有唯一约束/索引）;

  • UNIQUE WITH SINGLE NULL  - 可以猜到，没有重复，但有一个NULL（不能是PK，但可以有一个唯一的约束/索引）;

  • UNIQUE with NULLs  - 没有重复项，两个或多个NULL（如果您使用的是SQL Server 2008，则只能为非NULL值设置条件唯一索引）;

  • 空字符串 - 有重复项，也可能是NULL。


8
2017-08-04 14:20


给出错误“警告：聚合或其他SET操作消除了空值。”不能从应用程序使用。 - ratneshsinghparihar
@ user998660：“从对警告特别敏感的应用程序”，你的意思是？在那种情况下，不，它当然不能。不是所有的申请 是 但那很敏感。例如，我不记得在Delphi中遇到过产生此类警告的聚合问题。无论如何，我的印象是OP想要一个他们可以“手动”使用的解决方案，例如，在查询的情况下，在SSMS之类的工具中调用它。 - Andriy M

答案:

这是一种与@JNK基本类似的方法，但它不是打印计数，而是为每个列返回一个现成的答案，告诉您列是否仅包含唯一值：

DECLARE @table varchar(100), @sql varchar(max);
SET @table = 'some table name';

SELECT
  @sql = COALESCE(@sql + ', ', '') + ColumnExpression
FROM (
  SELECT
    ColumnExpression =
      'CASE COUNT(DISTINCT ' + COLUMN_NAME + ') ' +
      'WHEN COUNT(*) THEN ''UNIQUE'' ' +
      'ELSE '''' ' +
      'END AS ' + COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @table
) s

SET @sql = 'SELECT ' + @sql + ' FROM ' + @table;
PRINT @sql;  /* in case you want to have a look at the resulting query */
EXEC(@sql);

它只是比较 COUNT(DISTINCT column) 同 COUNT(*) 对于每一列。结果将是一个包含单行的表，其中每列都包含该值 UNIQUE 对于那些没有重复项的列，如果存在重复项则为空字符串。

但上述解决方案仅适用于那些没有NULL的列。应该注意，当您想要在列上创建唯一约束/索引时，SQL Server不会忽略NULL。如果一列只包含一个NULL且所有其他值都是唯一的，您仍然可以在列上创建一个唯一约束（但是，您不能将它作为主键，这需要值的唯一性和缺少NULL）。

因此，您可能需要对内容进行更全面的分析，您可以使用以下脚本获得：

DECLARE @table varchar(100), @sql varchar(max);
SET @table = 'some table name';

SELECT
  @sql = COALESCE(@sql + ', ', '') + ColumnExpression
FROM (
  SELECT
    ColumnExpression =
      'CASE COUNT(DISTINCT ' + COLUMN_NAME + ') ' +
      'WHEN COUNT(*) THEN ''UNIQUE'' ' +
      'WHEN COUNT(*) - 1 THEN ' +
        'CASE COUNT(DISTINCT ' + COLUMN_NAME + ') ' +
        'WHEN COUNT(' + COLUMN_NAME + ') THEN ''UNIQUE WITH SINGLE NULL'' ' +
        'ELSE '''' ' +
        'END ' +
      'WHEN COUNT(' + COLUMN_NAME + ') THEN ''UNIQUE with NULLs'' ' +
      'ELSE '''' ' +
      'END AS ' + COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @table
) s

SET @sql = 'SELECT ' + @sql + ' FROM ' + @table;
PRINT @sql;  /* in case you still want to have a look at the resulting query */
EXEC(@sql);

此解决方案通过检查三个值来考虑NULL： COUNT(DISTINCT column)， COUNT(column) 和 COUNT(*)。它显示的结果与前一种解决方案类似，但对列的可能诊断更加多样化：

  • UNIQUE 表示没有重复值且没有NULL（可以是PK或具有唯一约束/索引）;

  • UNIQUE WITH SINGLE NULL  - 可以猜到，没有重复，但有一个NULL（不能是PK，但可以有一个唯一的约束/索引）;

  • UNIQUE with NULLs  - 没有重复项，两个或多个NULL（如果您使用的是SQL Server 2008，则只能为非NULL值设置条件唯一索引）;

  • 空字符串 - 有重复项，也可能是NULL。


8
2017-08-04 14:20


给出错误“警告：聚合或其他SET操作消除了空值。”不能从应用程序使用。 - ratneshsinghparihar
@ user998660：“从对警告特别敏感的应用程序”，你的意思是？在那种情况下，不，它当然不能。不是所有的申请 是 但那很敏感。例如，我不记得在Delphi中遇到过产生此类警告的聚合问题。无论如何，我的印象是OP想要一个他们可以“手动”使用的解决方案，例如，在查询的情况下，在SSMS之类的工具中调用它。 - Andriy M

我想这可能是最干净的方式。只需使用动态sql和单个select语句来创建一个查询，该查询为每个字段提供总行数和不同值的计数。

在顶部填写数据库名称和表名。数据库名称部分非常重要 OBJECT_NAME 仅适用于当前数据库上下文。

use DatabaseName

DECLARE @Table varchar(100) = 'TableName'

DECLARE @SQL Varchar(max)

SET @SQL = 'SELECT COUNT(*) as ''Total'''

SELECT @SQL = @SQL + ',COUNT(DISTINCT ' + name + ') as ''' + name + ''''
FROM sys.columns c
WHERE OBJECT_NAME(object_id) = @Table

SET @SQL = @SQL + ' FROM ' + @Table

exec @sql

2
2017-08-04 12:39


假设列中没有NULL，这将非常有用。在创建唯一约束/索引时，SQL Server会考虑NULL。 - Andriy M

如果您使用的是2008，则可以使用SSIS中的数据概要分析任务返回每个表的候选密钥。

这篇博客文章介绍了这个过程，它非常简单：

http://consultingblogs.emc.com/jamiethomson/archive/2008/03/04/ssis-data-profiling-task-part-8-candidate-key.aspx


1
2017-08-04 12:13


非常有用的工具 - 感谢提示 - zmaster

我的代码做了几句话：

  1. 读取所有表格和列

  2. 创建临时表以保存具有重复键的表/列

  3. 对于每个表/列，它运行查询。如果它找到至少一个值的计数（*）> 1 它插入临时表

  4. 从系统表中选择与发现具有重复项的表/列不匹配的列和值 

    DECLARE @sql VARCHAR(max)
DECLARE @table VARCHAR(100)
DECLARE @column VARCHAR(100)


CREATE TABLE #temp (tname VARCHAR(100),cname VARCHAR(100))

DECLARE mycursor CURSOR FOR
select t.name,c.name
from sys.tables t
join sys.columns c on t.object_id = c.object_id
where system_type_id not in (34,35,99)

OPEN mycursor
FETCH NEXT FROM mycursor INTO @table,@column

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'INSERT INTO #temp SELECT DISTINCT '''+@table+''','''+@column+ ''' FROM ' + @table + ' GROUP BY ' + @column +' HAVING COUNT(*)>1 '
EXEC (@sql)
FETCH NEXT FROM mycursor INTO @table,@column
END

select t.name,c.name
from sys.tables t
join sys.columns c on t.object_id = c.object_id
left join #temp on t.name = #temp.tname and c.name = #temp.cname
where system_type_id not in (34,35,99) and #temp.tname IS NULL

DROP TABLE #temp

CLOSE mycursor
DEALLOCATE mycursor

1
2017-08-04 12:24


谢谢你的回答，我没有试过，因为它遍布所有表格，这不是我对这个具体问题所需要的。 - zmaster
您可以在游标查询定义中放置一个附加子句：AND tname = your_table_name - niktrs

简单的一行代码怎么样：

CREATE UNIQUE INDEX index_name ON table_name (column_name);

如果创建了索引，则column_name仅包含唯一值。如果column_name中存在欺骗，则会收到错误消息。


0
2017-12-04 14:41



热门问题

不使用eval / new函数的JavaScript模板库 当涉及内部类时，Java继承如何工作 .NET Windows服务的奇怪问题 在.ipa或.app下查找App ID 快速入门XSLT参考[关闭] 如何找出Android应用程序中未使用的资源 Ruby中并发的同步方法[重复] 将std :: chrono :: system_clock :: time_point转换为struct timeval并返回 Google Drive API V3（javascript）更新文件内容 Bootstrap 3.0 - 将元素推到底部 受密码保护的.NET ClickOnce部署？ 如何用postgresql安装wordpress coq Set或Type如何成为命题 硒滚动元素进入（中心）视图 在Spring Transaction JUnit测试中自动装配Hibernate会话的正确方法 Git的Dockerfile策略 如何在FOS_PICKFOLDER中使用IFileDialog，同时仍在对话框中显示文件名 在Firefox扩展中复制Google Chrome浏览器操作弹出效果 CakePHP找到MAX 芹菜 - 完成任务的召唤功能 从使用fmemopen创建的流中读取宽字符 .NET是否为每个程序集创建一个字符串实习池？ DefaultModelBinder不绑定嵌套模型 Navigator.MediaDevices.getUserMedia（）使用了哪些相机通信标准？ 选择命名空间名称时应该知道什么？ cout Swagger Codegen CLI Java客户端 - 如何正确使用它 一个很好的哈希函数用于采访整数，字符串？ Maven 3 ciManagement配置的目的是什么？ 如何通过语言文化获取代码页？
licensed under cc by-sa 3.0 with attribution.
隐私政策 往来