Reader Level:
Article

Schema in SQL Server 2012

By Deepak Middha on Dec 03, 2012
In this article I describe schemas in SQL Server 2012.

Introduction

In this article I describe schemas in SQL Server 2012. A schema is like a container that contains database objects like tables, views etc. So let us learn about schemas, creation of schemas, how to alter schemas and how to drop schemas.

Schema:

A schema is like a namespace in C# that contains database objects. All objects within a schema must have a unique name. And the name of the schema should also be unique in the database. When we create a table, view or other object then by default it goes in the dbo schema.

Syntax:

create schema schema_name
go
<schema element>
{table defenation, view defenation etc}

Example:


create
schema ss1
go

create table ss1.emp(empId int,empname varchar(15))
go

insert
into ss1.emp values(1,'d')
go
select * from ss1.emp

Output:

schema_in_sql_serve_2012.jpg

schema_in_sql_serverr.jpg

Creation of table in schema:

create table ss1.emp2(empAdd varchar(15))

Output:

schema_in_sql_serve.jpg

Creation of view in schema:


create
view ss1.v
as
select * from ss1.emp

Output:



select * from ss1.v

schema_in_sql_serve_2012.jpg

Alter schema:


First of all we are creating a schema and than we alter that schema:


create
schema ss2
go
alter
schema ss2 transfer ss1.emp
go
select
* from ss2.emp

Output:

schema_in_sql_serve_2012.jpg

Dropping a Schema:


drop
table ss2.emp
go
drop
schema ss2

Output:



Summary:

In this article I described schemas in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.

 

Deepak Middha

Deepak Middha is one time Microsoft MVP(Client App Dev) , working as Sr. Software Developer with lots of hands on experience using ASP.NET 2.0/3.5, AJAX, MVC, C#,  SQL Server 2005/2008/2012, WCF, JQuery and Silverl... Read more

COMMENT USING