如何查询数据库模式是否存在 [英] How do I query if a database schema exists

问题描述

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

As part of our build process we run a database update script as we deploy code to 4 different environments. Further, since the same query will get added to until we drop a release into production it has to be able to run multiple times on a given database. Like this:

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?

推荐答案

您是否正在寻找 系统架构?

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

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

Note that the CREATE SCHEMA must be run in its own batch (per the answer below)

这篇关于如何查询数据库模式是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！