MSSQL: изменить столбец, не ноль, в общем

В Microsoft SQL Server 2008 R2 я хотел бы изменить обнуляемый столбец на ненулевой. Очевидно, я мог бы сделать это, перезапустив тип данных, как

alter table t alter column c int not null

если столбец tc имеет тип данных int, например. Но как насчет вообще, не перезагружая существующий тип данных? Я ищу какой-то эквивалент

alter table t alter column c not null

где существующий тип данных остается на месте, и только обнуляемость отключается.

Фон

Я провел аудит своей базы данных и обнаружил много случаев, когда столбец указан как обнуляемый, но на практике пустых значений не возникает. Я хотел бы сжать схему, чтобы запретить нулевые значения в этих столбцах. Ручная запись DDL в "alter column" для каждого из них подвержена ошибкам, потому что я могу ошибиться в типе данных. Я мог бы автоматически сгенерировать код, используя программу-дампер схемы, которая выводит существующий тип данных каждого столбца, но это также сопряжено с риском, если дамперная программа не знает о последних типах данных и выводит что-то еще (например, предположим, что это не знает о datetime2 и записывает вместо него datetime).

SQL-сервер уже знает, какой тип столбца, поэтому, безусловно, есть способ сказать ему, чтобы он оставался таким и просто отключал обнуляемость.

Если на самом деле нет никакого способа сделать это, кроме как найти существующий тип данных для помещения его в DDL, возможно, вы могли бы порекомендовать подходящий инструмент для использования? Я знаю о dbschema.pl из дней Sybase, но может быть что-то более современное или какой-то известный фрагмент SQL, который выводит необходимые операторы из представлений схемы.

Источник

2 ответа

Решение

Предложенный Аноном ответ "два подхода" полезен. В поле для комментариев на сайте недостаточно текста, поэтому я опубликую свой окончательный ответ здесь.

В связанном ответе есть специальное условие для пользовательских типов данных, которых нет в моей базе данных, поэтому я использую type_name встроенный вместо. Этот запрос пытается перепроектировать тип для каждого столбца:

select t.name,
       c.name,
       case
         when type_name(c.system_type_id) in (
             'int', 'real', 'float', 'date', 'time', 'datetime', 'datetime2',
             'tinyint', 'smallint', 'smalldatetime', 'bit', 'bigint', 'timestamp',
             'image'
           ) then type_name(c.system_type_id)
         else type_name(c.system_type_id) + '('
           + case
               when precision = 0 then convert(varchar(10), c.max_length)
               else convert(varchar(10), precision) + ', ' + convert(varchar(10), scale)
             end
           + ')'
         end as ty
from sys.tables t
join sys.columns c
  on t.object_id = c.object_id
where c.is_nullable = 1
and   c.is_computed = 0
and   t.schema_id = 1
order by t.name,
         c.name

Затем вы можете взять каждую строку из этого запроса и сделать проверку на отсутствие нулей перед запуском 'alter table'. Я делаю что-то вроде следующего:

select case when
  exists (select 0 from TABLE)
  and not exists (select 0 from TABLE tablesample (1000 rows) where COLUMN is null)
then 1 else 0 end

для каждого TABLE - COLUMN, возвращаемая первым запросом. Если второй запрос возвращает 1, то вы, вероятно, можете внести изменения в "alter table". Я использую приведенный выше пример таблицы, чтобы избежать слишком большой нагрузки на базу данных, поскольку я планирую регулярно выполнять проверку; если размер таблицы, возвращаемой sp_spaceused, составляет менее 100 килобайт, тогда я опускаю предложение tableample.

Или, если вы чувствуете себя смелым, вы можете просто выполнить все операторы 'alter table' и позволить им потерпеть неудачу, если столбец содержит нули.

Как ни странно, у меня нет прав доступа к базе данных для щелчка правой кнопкой мыши в Management Studio и "Сценарий базы данных как", хотя я могу сделать это для отдельных объектов.

Источник

Два подхода:

1) Разверните этот ответ, чтобы включить рассмотрение max_length,precision,scale и collation_name. Если у вас есть несколько схем, вам нужно будет это учитывать.

SELECT
  'ALTER TABLE '
    +QUOTENAME(aud.[table_name])
    +' ALTER COLUMN '
    +QUOTENAME(aud.[column_name])
    +TYPE_NAME([system_type_id])
    +' NOT NULL;'
FROM MyColumnAuditList aud
INNER JOIN sys.columns col ON (
  col.[object_id] = OBJECT_ID(aud.[table_name]) AND
  col.[name] = aud.[column_name]
)

2) В SSMS щелкните правой кнопкой мыши на базе данных и выберите "Сценарий базы данных как". Используйте ваши инструменты разбора текста, чтобы извлечь определения столбцов из результата.

Источник
Другие вопросы по тегам