How to add a new schema to sql server 2008?

How do you add a new schema to a database? I am creating a new table and would like to select my own schema from the 开发者_开发问答properties list, but I don't know how to create it. I am using SQL Server Management 2008.

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..."

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

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.

Best way to add schema to your existing table: Right click on the specific table-->Design --> Under the management studio Right sight see the Properties window and select the schema and click it, see the drop down list and select your schema. After the change the schema save it. Then will see it will chage your schema.

You can try this:

use database
go

declare @temp as int
select @temp = count(1) from sys.schemas where name = 'newSchema'

if @temp = 0 
begin
    exec ('create SCHEMA temporal')
    print 'The schema newSchema was created in database'
end 
else 
print 'The schema newSchema already exists in database'
go

In SQL Server 2016 SSMS expand 'DATABASNAME' > expand 'SECURITY' > expand 'SCHEMA' ; right click 'SCHEMAS' from the popup left click 'NEW SCHEMAS...' add the name on the window that opens and add an owner i.e dbo click 'OK' button