sql - How to find out whether a table has some unique columns

Question

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 ?

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

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

Thanks.

Answer 1

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);

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.

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);

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 means no duplicate values and no NULLs (can either be a PK or have a unique constraint/index);

• 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 – 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);

• empty string – there are duplicates, possibly NULLs too.