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?