SCHEMA Creation, TRANSFER and DROP

Bandi
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 3231 red flag

Iam going to give an overview for SCHEMAs in SQL Server, by explaining CREATE , ALTER, DROP schemas.

Introduction

There are 4 built-in database schema which cannot be dropped.

  • dbo: The default database schema for new objects created by users having the db_owner or db_ddl_admin roles
  • guest: The guest schema is used to contain objects that would be available to the guest user. This schema is rarely used
  • INFORMATION_SCHEMA: The schema is used by the Information Schema views, which provide ANSI standard access to metadata.
  • sys: The sys database schema is reserved by SQL Server for system objects such as system tables and views.


Create Schema

We can create user-defined schemas in a database to categorize the set of tables/views/objects. Schema logically groups similar objects into single unit.

Every database schema must be owned by exactly one authorized database user. That database
schema owner can then grant permissions to other users regarding the objects in this schema.

Suppose you wanted to log the DML changes ( audit insert/update/delete operations), we can create one schema ‘audit’ and then move all audit related tables into this schema.

CREATE SCHEMA [audit] AUTHORIZATION [dbo]
GO

The schema named audit is actually owned by the user named as dbo, not by the dbo database schema. This allows one user (for example, dbo) to own many different database schemas.

Alter Schema Transfer

We can move the objects from one schema to another by using ALTER SCHEMA TRANSFER option. this statement moves the Schema2.TestTable object/table to Schema1.

ALTER SCHEMA Schema1 TRANSFER Shema2.TestTable;


Drop Schema

If you think the defined schema is no longer needed, you can drop the schema (if there are no objects under it).

DROP SCHEMA SchemaName;

--Sample Script for SCHEMA creation, transfer and drop commands

-- The default schema is dbo

CREATE TABLE TestTable ( ID INT IDENTITY (1,1) , Name Varchar(10))

SELECT * FROM dbo.TestTable

GO

CREATE SCHEMA TestSchema AUTHORIZATION dbo

GO

SELECT * FROM TestSchema.emp -- Gives Error because Emp table is not present under TestSchema

--Move dbo.Emp to TestSchema

ALTER schema TestSchema transfer dbo.emp

--Check the EMP table in TestSchema

SELECT * FROM TestSchema.emp

-- Before dropping a schema, it must be empty...

-- Move back dependent table to dbo schema or drop the table in TestSchema

-- To move back

ALTER schema dbo transfer TestSchema.emp

-- To DROP TABLE under TestSchema, DROP TABLE TestSchema.Emp

DROP SCHEMA TestSchema


Conclusion

The below are the advantages of Schemas in SQL Server:

  • Objects are not attached to any specific user account. So if the user account needs to be deleted we don’t have to worry about changing the objects owners.
  • It simplifies managing permissions on Schema objects. If the schema owner’s account is to be removed from the database, the ownership of the schema can be transferred to other user without breaking any code.

The maintenance of database become easier and use of schemas is recommended if you’re working with more than 20 tables.

Page copy protected against web site content infringement by Copyscape

About the Author

Bandi
Full Name: Chandrika Bandi
Member Level: Platinum
Member Status: Member,MVP
Member Since: 7/23/2013 5:52:37 AM
Country: India
[B]Mark This Response as Answer[/B] -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Sheonarayan on: 12/29/2014 | Points: 25
Good to know about it, its very simple however unknown to many. Good read.

Thanks and keep it up!

Login to post response

Comment using Facebook(Author doesn't get notification)