MS SQL Server - Create and change Schema

A database schema could be think of as a container of objects. It is used to logically group objects such as tables, views, stored procedures etc. It serves the similar purpose as namespace in C# or other programming languages. It helps preventing name clashes between objects from different schemas, so you can have different objects with same name in different schemas. For example, Person table is created in schema hr, you can create another table with same name Preson in some other schema i.e. vendor.

Create Schema:

Using SQL Server Management Studio you can create schema by:

• Navigating to your DATABASE node > Security > Schemas.

• Right click on Schemas and select New Schema

  

• In the new Schema dialog, write the name for your schema, you can also specify schema owner, and then click OK button.

  

Or you can create schema using T-SQL:

CREATE SCHEMA hr;

Change Schema for an object:

There may be case when you want to transfer objects from one schema to another. Lets say you have table Person currently exists in schema hr, and you want to move this table to another schema vendor. You can use following T-SQL to change schema.

ALTER SCHEMA vendor TRANSFER hr.Person

Same command could be used to change schema for other objects like views, stored procedures, functions.