How to add a new schema to sql server 2008?

Solution 1:

Use the CREATE SCHEMA syntax or, in SSMS, drill down through Databases -> YourDatabaseName -> Security -> Schemas. Right-click on the Schemas folder and select "New Schema..."

Solution 2:

Here's a trick to easily check if the schema already exists, and then create it, in it's own batch, to avoid the error message of trying to create a schema when it's not the only command in a batch.

IF NOT EXISTS (SELECT schema_name 
    FROM information_schema.schemata 
    WHERE schema_name = 'newSchemaName' )
BEGIN
    EXEC sp_executesql N'CREATE SCHEMA NewSchemaName;';
END

Solution 3:

I use something like this:

if schema_id('newSchema') is null
    exec('create schema newSchema');

The advantage is if you have this code in a long sql-script you can always execute it with the other code, and its short.