How do I query if a database schema exists

Pulsehead_2156 0
Viewed 104228

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?

sql-server-2005 schema
7 Answers

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)

bdukes_26880

@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 <name> needs to run in its 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
vfilby_242790

This is old so I feel compelled to add: For SQL SERVER 2008+ These all work (for the select part), then use EXECUTE('CREATE SCHEMA <name>') to actually create it on negative results.

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
user_1259810

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
user_59193210 
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

NOTICE: CREATE SCHEMA AND CREATE TABLE NEED COMPLETLY SEPARATED BATCH TO EXECUTE

TO MORE DESCRIPTION VISIT MICROSOFT DOCS WEBSITE :)

user_115079960

As of SQL Server 2005 version 9.0 you can use the INFORMATION_SCHEMA.SCHEMATA view to check if the schema exists:

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.

Note that the CREATE SCHEMA must be run in its own batch

user_11975530
Related