create schema

See more:

SQL-Server-2005

SQL-Server

I want to add some stuff (tables, stored procedures, etc.) to an existing sqlserver 2005 database. To avoid potential name conflicts I want to put them all in my own schema. But I am running into a problem. I am using SQL Server Management Studio to run the following code.

USE AdventureWorks
GO

CREATE SCHEMA [pja]
GO

SELECT * FROM sys.schemas
GO

This works great as the schema is created and I can use it. But it produces an error if the schema is already present:

Msg 2714, Level 16, State 6, Line 2
There is already an object named 'pja' in the database.
Msg 2759, Level 16, State 0, Line 2
CREATE SCHEMA failed due to previous errors.

I want to avoid the error so I wanted to check for the existance of the schema before I call CREATE SCHEMA:

USE AdventureWorks
GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'pja')
BEGIN
    PRINT '--- Creating schema "pja"'
    CREATE SCHEMA [pja]
END
GO

SELECT * FROM sys.schemas
GO

The schema is not created and the error message is now:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SCHEMA'.

What is the proper way to attempt to create a schema only if it does not already exist?