Как узнать, имеет ли таблица несколько уникальных столбцов

Ответ 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) с COUNT(*) для каждого столбца. Результатом будет таблица с одной строкой, где каждый столбец будет содержать значение UNIQUE для тех столбцов, у которых нет дубликатов, и пустая строка, если присутствуют дубликаты.

Но приведенное выше решение будет корректно работать только для тех столбцов, у которых нет NULL. Следует отметить, что SQL Server не игнорирует NULL, если вы хотите создать уникальное ограничение/индекс для столбца. Если столбец содержит только один NULL, а все остальные значения уникальны, вы все равно можете создать уникальное ограничение для столбца (вы не можете сделать его первичным ключом, хотя это требует как uniquness значений, так и отсутствия NULL).

Поэтому вам может потребоваться более тщательный анализ содержимого, которое вы можете получить со следующим 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(*). Он отображает результаты аналогично предыдущему решению, но возможные диагнозы для столбцов более разнообразны:

• UNIQUE означает отсутствие повторяющихся значений и отсутствие NULL (может быть PK или иметь уникальное ограничение/индекс);

• UNIQUE WITH SINGLE NULL - как можно догадаться, нет дубликатов, но есть один NULL (не может быть PK, но может иметь уникальное ограничение/индекс);

• UNIQUE with NULLs - нет дубликатов, двух или более NULL (в случае, если вы находитесь на SQL Server 2008, вы можете иметь условный уникальный индекс только для значений, отличных от NULL);

• пустая строка - есть дубликаты, возможно, NULL тоже.

Ответ 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

Ответ 3

Если вы используете 2008, вы можете использовать задачу профилирования данных в SSIS для возврата ключей-кандидатов для каждой таблицы.

Эта запись в блоге проходит через процесс, это довольно просто:

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

Ответ 4

Несколько слов, что делает мой код:

• Читать все таблицы и столбцы

• Создает временную таблицу для хранения таблицы/столбцов с дублирующимися ключами

• Для каждой таблицы/столбца выполняется запрос. Если он найдет счетчик (*) > 1 для хотя бы одного значения он вставляет в таблицу temp

• Выберите столбец и значения из системных таблиц, которые не соответствуют таблицам/столбцам, у которых обнаружены дубликаты

  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 '''[email protected]+''','''[email protected]+ ''' 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
  

Ответ 5

Как насчет простой одной строки кода:

CREATE UNIQUE INDEX index_name ON table_name (column_name);

Если индекс создан, то ваше имя_столбца имеет только уникальные значения. Если в вашем имени столбца есть дубликаты, вы получите сообщение об ошибке.