如何查询数据库架构是否存在？

Question

在我们的构建过程中，我们会在将代码部署到4个不同环境时运行数据库更新脚本。此外，由于相同的查询将被添加直到我们发布到生产环境，因此它必须能够在给定的数据库上运行多次。就像这样：

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

目前，在部署/构建脚本中我有一个创建模式的语句。我应该在哪里查询模式是否存在？

- Pulsehead

---

请考虑更改已接受的答案。按照所写的方式，您接受的答案实际上不可能对您起作用。 - Aaron Bertrand

Answer 1

你是否正在寻找 sys.schemas？

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

请注意，CREATE SCHEMA 必须在其自己的批处理中运行（参见下面的答案）。

- bdukes

---

在SQL 2008中，这个方法不起作用，因为CREATE SCHEMA需要在批处理中的第一条语句，可查看vfilby的帖子获取解决方法。 - sergiom

您可以使用“Select 1 from sys.schemas”来提高性能。 - vijaysylvester

不，这是一个谬论。SQL Server会优化掉列列表，因此您放在那里的内容无关紧要。会被完全忽略。想要证明吗？尝试在查询中加入 SELECT 1/0...。 - Aaron Bertrand

我已经更新了这个答案，以免不正确（即使用下面的脚本https://dev59.com/tXVC5IYBdhLWcg3wpS3f#521271）。 - bdukes

使用 * 、1 和 1/0 这三种方法可能不会对性能产生影响，但在某些情况下会有所不同。从安全角度考虑，这三种选择都需要你访问所有的列，而指定特定列名只会检查查询中实际命名的列。因此，如果查询中任何表的任何列被拒绝访问，'select col-with-access...' 可以成功，而 'select 1 ...' 则不能。 - bielawski

Answer 2

@bdukes的方法在确定模式是否存在方面是正确的，但上面的语句在SQL Server 2005中不起作用。 CREATE SCHEMA <name> 需要在自己的批处理中运行。一个解决办法是在exec中执行CREATE SCHEMA语句。

这是我在构建脚本中使用的代码：

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

- vfilby

---

非常好用！这甚至让我可以放置我的打印语句和其他所有内容。 - Tony

Answer 3

这段内容有些陈旧，因此我想补充一下：对于SQL SERVER 2008及以上版本，以下所有语句均可使用（用于SELECT部分），然后使用EXECUTE('CREATE SCHEMA <name>')在无法创建时创建它。

DECLARE @schemaName sysname = 'myfunschema';
-- shortest
If EXISTS (SELECT 1 WHERE SCHEMA_ID(@schemaName) IS NOT NULL)
PRINT 'YEA'
ELSE
PRINT 'NOPE'

SELECT DB_NAME() AS dbname WHERE SCHEMA_ID(@schemaName) IS NOT NULL -- nothing returned if not there

IF NOT EXISTS ( SELECT  top 1 *
                FROM    sys.schemas
                WHERE   name = @schemaName )
PRINT 'WOOPS MISSING'
ELSE
PRINT 'Has Schema'

SELECT SCHEMA_NAME(SCHEMA_ID(@schemaName)) AS SchemaName1 -- null if not there otherwise schema name returned

SELECT SCHEMA_ID(@schemaName) AS SchemaID1-- null if not there otherwise schema id returned


IF EXISTS (
    SELECT sd.SchemaExists 
    FROM (
        SELECT 
            CASE 
                WHEN SCHEMA_ID(@schemaName) IS NULL THEN 0
                WHEN SCHEMA_ID(@schemaName) IS NOT NULL THEN 1
                ELSE 0 
            END AS SchemaExists
    ) AS sd
    WHERE sd.SchemaExists = 1
)
BEGIN
    SELECT 'Got it';
END
ELSE
BEGIN
    SELECT 'Schema Missing';
END

- Mark Schultheiss

---

IF schema_id ('MySchemaName') IS NULL 运行良好，似乎比被接受的答案更方便。 - BradC

同意@BradC。对于那些遇到异常的人：IF SCHEMA_ID('out') IS NULL EXEC('CREATE SCHEMA [out] AUTHORIZATION [out]'); - Juozas

Answer 4

如果组件的布局允许，这也可以使用。

IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'myschema') SET NOEXEC ON 
go
CREATE SCHEMA myschema
GO 
SET NOEXEC OFF -- 如果需要进一步处理。
GO

- benik9

Answer 5

为了更加“严谨”，以下版本会生成类型转换错误，以应对可能（虽然不太可能）存在的 >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

- Tom

---

我想当你使用区分大小写的排序规则时，可能会有多个匹配模式，但你的“错误处理”将导致以下错误：在将varchar值“ERROR”转换为int数据类型时转换失败。 - user247702

@Stijn：这就是“按设计”（By Design），类似于验证代码经常有意地Throw Exception。正如你所说，这种情况不太可能发生，因此在我看来，它不值得花费整个Try-Catch或单独的Count检查来生成更用户友好的致命错误，但无论如何，我可能会想要一个致命错误。我相信并认为，考虑所有可能的返回结果，即使不太可能，甚至只是为了生成致命异常，也是“最佳实践”，因为停止处理的已知影响通常比未捕获错误的未知级联影响要好。 - Tom

这听起来都很好，我不确定这是否是有意为之的 :) 你的回答可能需要一些额外的解释，就像你在评论中刚刚给出的那样。 - user247702

@Stijn：我最讨厌的是常见的不太“最佳实践”，即不检查Select、Insert、Update或Delete语句是否返回/影响了比预期少或多的行数，尽管这种情况可能很少发生。即使当前有“唯一”“索引”确保了预期数量（即1）的行数被返回/影响，但这种情况可能会在未来发生变化（无意或（短视地）“有意”）。 - Tom

Answer 6

IF NOT EXISTS (SELECT TOP (1) 1 FROM [sys].[schemas] WHERE [name] = 'Person')
BEGIN
    EXEC ('CREATE SCHEMA [Person]')
END

IF NOT EXISTS (SELECT TOP (1) 1 FROM [sys].[tables] AS T
               INNER JOIN [sys].[schemas] AS S ON S.schema_id = T.schema_id
               WHERE T.[name] = 'Guests' AND S.[name] = 'Person')
BEGIN
    EXEC ('CREATE TABLE [Person].[Guests]
           (
                [GuestId] INT IDENTITY(1, 1) NOT NULL,
                [Forename] NVARCHAR(100) NOT NULL,
                [Surname] NVARCHAR(100) NOT NULL,
                [Email] VARCHAR(255) NOT NULL,
                [BirthDate] DATETIME2 NULL,
                CONSTRAINT [PK_Guests_GuestId] PRIMARY KEY CLUSTERED ([GuestId]),
                CONSTRAINT [UX_Guests_Email] UNIQUE([Email])
           )')
END

注意：执行CREATE SCHEMA和CREATE TABLE需要完全分离的批处理。

欲了解更多信息，请访问微软文档网站 :)

- Mohammad Sadeq Sirjani

---

在Microsoft SQL Azure（RTM）-12.0.2000.8 May 12 2022上测试并使用SSMS v18时，将创建表放在自己的批处理中是不必要的。 - David Sopko

Answer 7

从SQL Server 2005版本9.0开始，您可以使用INFORMATION_SCHEMA.SCHEMATA视图来检查模式是否存在：

IF NOT EXISTS (
SELECT  SCHEMA_NAME
FROM    INFORMATION_SCHEMA.SCHEMATA
WHERE   SCHEMA_NAME =  '<schema name>' )
 
BEGIN
    EXEC sp_executesql N'CREATE SCHEMA <schema name>'  
END
GO

INFORMATION_SCHEMA视图是ISO标准，通常更可取；这些被采用是为了使不同的SQL数据库平台之间的语法更加一致。

请注意，CREATE SCHEMA必须在自己的批处理中运行。

- David Sopko