SCHEMA Creation, TRANSFER and DROP

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

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.