logo标签列表

查找表是否具有唯一列

sqlsql-serverunique
15

我使用SQL Server。

我手头有一些没有任何约束条件的大型表，没有键也没有其他任何东西。

我知道其中一些列具有唯一值。有没有一种聪明的方法可以查找具有唯一值的列？

现在，我为每个列手动执行此操作，通过计算是否有与表中行数相同数量的DISTINCT值。

SELECT COUNT(DISTINCT col) FROM table

或许可以创建一个游标来遍历所有列，但我想听听是否有更聪明或内置的方法。

5个回答
9
这里有一种方法，基本上类似于@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。

出现错误：“警告：空值被聚合或其他SET操作消除。” 无法从应用程序中使用。 - ratneshsinghparihar
“特别敏感于警告的应用程序”，您是这个意思吗？如果是这样，当然不行。但并非所有应用程序都那么敏感。例如，在Delphi中，我从未遇到过产生此类警告的聚合问题。无论如何，我的印象是OP想要一个他们可以“手动”使用的解决方案，例如在查询时，在诸如SSMS之类的工具中调用它。 - Andriy M
2

这里我认为可能是最简单的方法。只需使用动态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
1
假设列中没有 NULL 值，这将非常有帮助。在创建唯一约束/索引时，SQL Server 会考虑到 NULL 值。 - Andriy M
1
我的代码是做什么的几句话：

  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
谢谢您的回答，但是我还没有尝试它，因为它会运行在所有表上，而这不是我针对这个特定问题所需要的。 - zmaster
您可以在游标查询定义中添加一个附加子句：AND tname = 您的表名 - niktrs
1

关于简单的一行代码怎么样：

CREATE UNIQUE INDEX index_name ON table_name (column_name);

如果索引已创建，则您的列名只具有唯一值。如果您的列名中存在重复项，则会收到错误消息。
1
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接