How do I query if a database schema exists

作为构建过程的一部分，我们在将代码部署到4个不同的环境时运行一个数据库更新脚本。此外，由于相同的查询将被添加到，直到我们将一个版本投入生产，所以 已经能够在给定的数据库上运行多次。像这样:

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

Currently I have a create schema statement in the deployment/build script. Where do I query for the existence of a schema?

106847 次浏览
小开
最佳答案

你在找 sys.schemas吗？

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

请注意，CREATE SCHEMA必须在它自己的批处理中运行(每个 下面的答案)

小开

@ bdukes 在确定模式是否存在方面是正确的，但是上面的语句在 SQLServer2005中不起作用。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
小开

为了使 extra具有“防御性”，下面的版本生成了一个类型转换错误来解释可能(尽管不太可能) > 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
小开

这是旧的，所以我觉得有必要添加: 对于 SQL SERVER 2008 + 这些都工作(为选择部分) ，然后使用 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
小开

If the layout of components allows it, this works too. 

IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'myschema') SET NOEXEC ON
go
CREATE SCHEMA myschema
GO
SET NOEXEC OFF -- if any further processing is needed.
GO
小开 
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需要完全分开的批次执行

更多描述请访问微软文档网站:)

小开

从 SQL Server 2005 version 9.0开始，您可以使用 INFORATION _ 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 views are the ISO standard and are generally preferable; these were adopted to make the syntax more consistent across different SQL database platforms.

注意，CREATESCHEMA 必须在其自己的批处理中运行


提交答案