Creating a Schema (Database)

Overview

Schema is a blueprint of how database is constructed and also it's design view. It also defines how entities going to work in real world scenario and how they are modelled. Schema are generally stored in DATA Dictionary.

Methods

There are two ways of creating schema's:

SQl Server Management Tool
Transact SQL

SQL Server Management Tool

To create a schema using SQL server management tool, functionality goes something like that:

Expand the DATABASE option in the tool
Right click on SECURITY TAB
From the options select NEW option
Click on SELECT SCHEMA
A DIalog Box will come to your home screen
In the GENERAL BOX, enter the name in Schema name Box
Now, in Schema Name Box enter DATABASE USER or ROLE
(you can also do that by click on search button for roles)
Atlast, click 'OK'.

Transact SQL

To create a schema using Transact SQL tool, functionality goes something like that:

Go to OBJECT EXPLORER
Connect to an Instance of database
Select STANDARD BAR
Click on NEW QUERY
COPY and PASTE following snippet

[ USE AdventureWorks2012;
GO
-- Creates the schema Sprockets owned by Annik that contains table NineProngs.
-- The statement grants SELECT to Mandar and denies SELECT to Prasanna.

CREATE SCHEMA Sprockets AUTHORIZATION Annik
CREATE TABLE NineProngs (source int, cost int, partnumber int)
GRANT SELECT ON SCHEMA::Sprockets TO Mandar
DENY SELECT ON SCHEMA::Sprockets TO Prasanna;
GO