判断一个表是否有一些唯一的列

Question

我使用 SQL Server。

有人给了我一些大桌子，没有任何限制，没有钥匙，什么也没有。

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

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

SELECT COUNT(DISTINCT col) FROM table

可能可以让光标在所有列上循环，但想听听是否有人知道更智能或内置的功能。

Answer 1

这是一种基本上类似于 @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)< /code> 每列都带有 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(列） 和 COUNT(*)。它显示的结果与前一个解决方案类似，但对列的可能诊断更加多样化：

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

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

• UNIQUE with NULL – 没有重复项，两个或多个 NULL（如果您使用的是 SQL Server 2008，则可以仅对非 NULL 值使用条件唯一索引）；

• 空字符串 – 存在重复项，也可能为 NULL。

Answer 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

Answer 3

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

此博客条目逐步介绍了整个过程，非常简单：

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

Answer 4

我的代码做了几句话：

1. 读取所有表和列

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

3. 对于每个表/列，它运行一个查询。如果发现至少一个值的 count(*)>1
   它会向临时表中插入内容

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

   DECLARE @sql VARCHAR(max)
   声明 @table VARCHAR(100)
   声明@column VARCHAR(100)
   
   
   创建表 #temp (tname VARCHAR(100),cname VARCHAR(100))
   
   声明 mycursor CURSOR FOR
   选择 t.name,c.name
   来自 sys.tables t
   将 sys.columns c 加入 t.object_id = c.object_id
   其中 system_type_id 不在 (34,35,99) 中
   
   打开我的光标
   从 mycursor 获取下一个 INTO @table,@column
   
   当@@FETCH_STATUS = 0时
   开始
   SET @sql = 'INSERT INTO #temp SELECT DISTINCT '''+@table+''','''+@column+ ''' FROM ' + @table + ' GROUP BY ' + @column +' HAVING COUNT(*) ＞1'
   执行（@sql）
   从 mycursor 获取下一个 INTO @table,@column
   结尾
   
   选择 t.name,c.name
   来自 sys.tables t
   将 sys.columns c 加入 t.object_id = c.object_id
   在 t.name = #temp.tname 和 c.name = #temp.cname 上左连接 #temp
   其中 system_type_id 不在 (34,35,99) 中且 #temp.tname IS NULL
   
   删除表#temp
   
   关闭我的光标
   解除分配我的光标
   

Answer 5

简单的一行代码怎么样：

CREATE UNIQUE INDEX index_name ON table_name (column_name);

如果创建了索引，那么您的column_name只有唯一值。如果您的column_name 中有重复内容，您将收到一条错误消息。

Answer 6

调整了@Andriy M的解决方案，这对我有帮助。

我们的表列名称包含以数字值开头的字母数字字符，因此某些表的查询失败。因此，在@Andriy M 的解决方案中在列名称周围添加了方括号。

修改后的查询如下所示，（实现了 @TT 使用 QUOTENAME 而不是方括号的建议）

    DECLARE @table varchar(100), @sql varchar(max);
    SET @table = 'some table name';
    
    SELECT
  @sql = COALESCE(@sql + ', ', '') + ColumnExpression
FROM (
  SELECT
    ColumnExpression =
      'CASE COUNT(DISTINCT ' + QUOTENAME(COLUMN_NAME) + ') ' +
      'WHEN COUNT(1) THEN ''UNIQUE'' ' +
      'WHEN COUNT(1) - 1 THEN ' +
        'CASE COUNT(DISTINCT ' + QUOTENAME(COLUMN_NAME) + ') ' +
        'WHEN COUNT(' + QUOTENAME(COLUMN_NAME) + ') THEN ''UNIQUE WITH SINGLE NULL'' ' +
        'ELSE '''' ' +
        'END ' +
      'WHEN COUNT(' + QUOTENAME(COLUMN_NAME) + ') THEN ''UNIQUE with NULLs'' ' +
      'ELSE '''' ' +
      'END AS ' + QUOTENAME(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);