如何找出一个表是否有一些唯一的列 [英] How to find out whether a table has some unique columns

问题描述

我使用 MS SQL Server.

I use MS SQL Server.

我收到了一些没有限制的大桌子，没有钥匙，没有任何东西.

Ive been handed some large tables with no constrains on them, no keys no nothing.

我知道某些列具有唯一值.对于给定的表，是否有一种聪明的方法可以找到具有唯一值的列?

I know some of the columns have unique values. Is there a smart way for a given table to finde the cols that have unique values ?

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

Right now I do it manually for each column by counting if there is as many DISTINCT values as there are rows in the table.

SELECT COUNT(DISTINCT col) FROM table

可以让 cusor 循环遍历所有列，但想听听是否有人知道更智能或内置的函数.

Could prob make a cusor to loop over all the columns but want to hear if someone knows a smarter or build-in function.

谢谢.

推荐答案

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

Here's an approach that is basically similar to @JNK's but instead of printing the counts it returns a ready answer for every column that tells you whether a column consists of unique values only or not:

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，如果存在重复，则为空字符串.

It simply compares COUNT(DISTINCT column) with COUNT(*) for every column. The result will be a table with a single row, where every column will contain the value UNIQUE for those columns that do not have duplicates, and empty string if duplicates are present.

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

But the above solution will work correctly only for those columns that do not have NULLs. It should be noted that SQL Server does not ignore NULLs when you want to create a unique constraint/index on a column. If a column contains just one NULL and all other values are unique, you can still create a unique constraint on the column (you cannot make it a primary key, though, which requires both uniquness of values and absence of NULLs).

因此，您可能需要对内容进行更彻底的分析，您可以使用以下脚本进行分析:

Therefore you might need a more thorough analysis of the contents, which you could get with the following script:

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(*).它显示的结果与前一个解决方案类似，但列的可能诊断更加多样化:

This solution takes NULLs into account by checking three values: COUNT(DISTINCT column), COUNT(column) and COUNT(*). It displays the results similarly to the former solution, but the possible diagnoses for the columns are more diverse:

• UNIQUE 表示没有重复值和 NULL(可以是 PK 或具有唯一约束/索引)；

• UNIQUE means no duplicate values and no NULLs (can either be a PK or have a unique constraint/index);

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

UNIQUE WITH SINGLE NULL – as can be guessed, no duplicates, but there's one NULL (cannot be a PK, but can have a unique constraint/index);

UNIQUE with NULLs – 没有重复，两个或多个 NULL(如果你使用的是 SQL Server 2008，你可以有一个只有非 NULL 值的条件唯一索引)；

UNIQUE with NULLs – no duplicates, two or more NULLs (in case you are on SQL Server 2008, you could have a conditional unique index for non-NULL values only);

空字符串——有重复的，也可能是 NULL.

empty string – there are duplicates, possibly NULLs too.

这篇关于如何找出一个表是否有一些唯一的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！