ARTICLE

Schema in SQL Server 2012

Posted by Deepak Arora Articles | SQL Server December 03, 2012
In this article I describe schemas in SQL Server 2012.
Reader Level:

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.

 

COMMENT USING

Trending up