Как выполнить запрос, если существует схема базы данных

В рамках нашего процесса сборки мы запускаем обновление базы данных script по мере развертывания кода в 4 разных средах. Кроме того, поскольку тот же запрос будет добавлен до тех пор, пока мы не отпустим выпуск в производство, он должен иметь возможность запускать несколько раз в данной базе данных. Вот так:

IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[Table]'))
BEGIN
  CREATE TABLE [Table]
  (...)
END

В настоящее время у меня есть инструкция создания схемы в развертывании/сборке script. Где я запрашиваю существование схемы?

Ответ 1

Вы ищете sys.schemas?

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'jim')
BEGIN
EXEC('CREATE SCHEMA jim')
END

Обратите внимание, что CREATE SCHEMA должен запускаться в своей собственной партии (за ответ ниже)

Ответ 2

@bdukes прав на деньги, чтобы определить, существует ли схема, но вышеприведенный оператор не будет работать в SQL Server 2005. CREATE SCHEMA <name> должен запускаться в своей собственной партии. Обходной задачей является выполнение инструкции CREATE SCHEMA в exec.

Вот что я использовал в сценариях сборки:

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = '<name>')
BEGIN
    -- The schema must be run in its own batch!
    EXEC( 'CREATE SCHEMA <name>' );
END

Ответ 3

Просто для того, чтобы быть дополнительным "защитным", следующая версия генерирует ошибку преобразования типа для учета возможности (хотя и маловероятной) из > 1 соответствия Schema, аналогичной тому, как код проверки часто преднамеренно выбрасывает исключение, потому что я считаю, что это хорошо для и я считаю, что "наилучшей практикой" является учет всех возможных результатов возврата, но маловероятных, и даже если он просто генерирует фатальное исключение, потому что известные эффекты остановки обработки обычно лучше, чем неизвестные каскадные эффекты ошибок с нелокализованными ошибками. Поскольку это очень маловероятно, я не думал, что это стоит проблемы отдельной проверки Count + Throw или Try - Catch - Throw, чтобы генерировать более удобную для пользователя фатальную ошибку, но все же фатальную ошибку тем не менее.

SS 2005-:

declare @HasSchemaX bit
set @HasSchemaX = case (select count(1) from sys.schemas where lower(name) = lower('SchemaX')) when 1 then 1 when 0 then 0 else 'ERROR' end

SS 2008+:

declare @HasSchemaX bit = case (select count(1) from sys.schemas where lower(name) = lower('SchemaX')) when 1 then 1 when 0 then 0 else 'ERROR' end

Тогда:

if @HasSchemaX = 1
begin
   ...
end -- if @HasSchemaX = 1