Schema in SQL Server 2012

In this article, I describe schemas in SQL Server 2012. A SQL 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.

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.
 

SQL 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  
  1. create schema ss1    
  2. go     
  3. create table ss1.emp(empId int,empname varchar(15))    
  4. go     
  5. insert into ss1.emp values(1,'d')    
  6. go     
  7. select * from ss1.emp  
Output
 
schema in sql server
 
schema in sql server
 

Creation of table in schema

  1. create table ss1.emp2(empAdd varchar(15))  
Output
 
schema_in_sql_serve.jpg 
 

Creation of view in schema

  1. create view ss1.v  
  2. as select * from ss1.emp  
Output
 
schema_in_sql_serve_2012.jpg
  1. select * from ss1.v
schema_in_sql_serve_2012.jpg 
 

Alter SQL Schema

 
First of all we are creating a schema and then we alter that schema
  1. create schema ss2  
  2. go  
  3. alter schema ss2 transfer ss1.emp   
  4. go  
  5. select * from ss2.emp  
Output
 
Alter SQL Schema

Dropping a SQL Schema

  1. drop table ss2.emp  
  2. go  
  3. drop schema ss2  
Output
 
Dropping a SQL Schema

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.