About Schema In SQL Server

This article will help you to know about Schema and view the Schema changes history in SQL Server.

About Schema

A database Schema is defined as a concept to logically group objects such as tables, views, stored procedures etc. Schema is a layout of the tables, data types, relations etc. act as a container of objects.

All objects within a schema must have a unique name and the name of the schema should also be unique in the database.

How to Create Schema:

Syntax:

  1. Create Schema Schema_Name <Schema element>Data types  
Example
  1. CREATE TABLE Product1    
  2. (    
  3.    iProductID INT IDENTITY(1,1)     
  4.    ,vProductName VARCHAR(100) NOT NULL    
  5.    ,vManfacturer VARCHAR(100) NOT NULL    
  6.    ,LastUpdated DATETIME DEFAULT GETDATE() ,    
  7.    PRIMARY KEY (iProductID, vManfacturer, vProductName)    
  8.     
  9. )  
Example of Schema Created : A schema is created as in the following,

f1

Once schema is created you can perform any SQL query like Update, Alter, Insert, Delete or Drop Schema.

Example: Here I used Insert Query.
  1. INSERT INTO Product1(vProductName, vManfacturer) VALUES('Television','Samsung')  

f2

How To Drop Schema
: To drop schema follow the given syntax:

Syntax:
  1. DROP Schema Schema_Name  

To view the Schema Change History follow the below steps:

Firstly, go to your Server Object Explorer, then right click on selected server. Now select Reports, after that select Standard Reports, then click on Schema Change History option as in the following screenshot,

f3

A new query tab will be opened with all DDL Statements executed status with Server Name, Database Name, History (Since date & time), Object Name, Type, DDL Operation detail with Time as given below:

f4