Creating schemas in SQL Server

Schema is a container or work area, that can store all the objects of database in SQL Server.

Schema is a collection of objects under a database principal.

In SQL Server by default schema is dbo.

We can create our own schema using the following queries.

Syntax:

Creating schema

create schema <schema-name>

Creating schema to specified login

create  schema <schema-name> authorization <login-name>


Example1:

create schema schema1

Example2:

create schema schema2 authorization login1

// Before that we have to create login1 and mapping to particular database.


Schema is referred with dot (.) notation.

Accessing table under schema notation.

Syntax:    

databasename.schemaname.objectname 

Example:

mydb.schema1.emp              // mydb is database, schema1 is schema and emp is table.

Note: If object is not part of default schema, then we can access like

Syntax:

 databasename..objectname   // we can specify the double dots(..)

Example:

dbo..student.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s