How do I query if a database schema exists

sql-server-2005 schema
Pulsehead picture Pulsehead · Oct 22, 2008 · Viewed 82.1k times · Source

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?

Answer

bdukes picture bdukes · Oct 22, 2008

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)

Related questions

What good are SQL Server schemas?

I'm no beginner to using SQL databases, and in particular SQL Server. However, I've been primarily a SQL 2000 guy and I've always been confused by schemas in 2005+. Yes, I know the basic definition of a schema, but what are they …

Read More
SQL server schema and default schema

I have a schema define in my database. Except now everytime I do a sql statement I have to provide the schema ... SELECT * FROM [myschema].table I set the default schema for my user using management studio and also ran …

Read More
SQL Server: How to permission schemas?

Inspired by various schema related questions I've seen... Ownership chaining allows me to GRANT EXECUTE on a stored procedure without explicit permissions on tables I use, if both stored procedure and tables are in the same schema. If we use …

Read More