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.