How do I query if a database schema exists

Question

As part of our build process we run a database update script as we deploy code to 4 different environments (due to crazy business rules, but that's another post). Further, since the same query will get added to until we drop a release into production (yet a few more domains to add to... frustrating); 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 1

Are you looking for sys.schemas?

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

Answer 2

bdukes is right on the money for determining if the schema exists, but the statement above won't work in SQL Server 2005. CREATE SCHEMA needs to run in it's own batch. A work around is to execute the CREATE SCHEMA statement in an exec.

Here is what I used in my build scripts:

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