sql-server-2005schema

How do I query if a database schema exists

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?

Solution

  • Are you looking for sys.schemas?

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

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