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