Iam going to give an overview for SCHEMAs in SQL Server, by explaining CREATE , ALTER, DROP schemas.
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
The schema is used
by the Information Schema views, which provide ANSI standard access to
- sys: The sys database schema is reserved by SQL
Server for system objects such as system tables and views.
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.
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.
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
CREATE SCHEMA [audit] AUTHORIZATION [dbo]
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
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;
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
TestTable ( ID INT IDENTITY (1,1) , Name Varchar(10))
SELECT * FROM dbo.TestTable
TestSchema AUTHORIZATION dbo
-- Gives Error because Emp table is not present under
--Move dbo.Emp to TestSchema
TestSchema transfer dbo.emp
--Check the EMP table in TestSchema
-- Before dropping a schema, it must be empty...
-- Move back dependent table to dbo schema or drop the table
-- To move back
ALTER schema dbo transfer TestSchema.emp
-- To DROP TABLE under TestSchema, DROP TABLE TestSchema.Emp
DROP SCHEMA TestSchema
The below are the advantages of Schemas in SQL Server:
database become easier and use of schemas is recommended if you’re
working with more than 20 tables.
- 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
- 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.