Revisions to How do I query if a database schema exists

add a link to a quoted answer

Source Link

edited Jul 2, 2024 at 12:28

3.8k
6
54
79

@bdukes@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

@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

@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

Couple of minor typos and a bump because this should be the accepted answer.

Source Link

edited Sep 5, 2014 at 0:53

Aaron Bertrand

282.4k
37
471
469

bdukes@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 needsCREATE SCHEMA <name> needs to run in it'sits own batch. A work around is to execute the CREATE SCHEMACREATE 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

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

@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

Source Link

answered Feb 6, 2009 at 17:32

vfilby

10k
9
52
62

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

Collectives™ on Stack Overflow

Return to Answer

Post Timeline