Schema in sql server: Creating adding altering default schema finding in a database

In this article we will walk through Create or adding schema, Default schema, Alter or change schema, show the schema, Drop schema, change schema owner,find schema etc in sql server 2005,2012, 2014.

What is schema in sql server?

Schema is container of database objects like table, view, stored procedures etc.

 

In simple word a database can have more than schema and a schema can have more than one tables, view, procedures etc.

Create or adding schema in a database sql server:

We can create schema by using SSMS or by T-Sql

Using T-Sql

CREATE SCHEMA Exact_Help 

Using Sql server management studio (SSMS)

1. Go to Databases -> Your Database -> Security -> Schemas

2. Right click on schemas and choose "New Schemas ..."

3. Write any schema name and click on OK button.

Default schema sql server:

Default schema means if schema name has not specified in the object name then in which schema sql server will search first.

Default schema depends upon sql server user. Sql server will search the objects in schema dbo if it doesn't find it in default schema of user. 

Good example:

Creating a user named manish with default schema Exact_Help

--Creating login

CREATE LOGIN Manish

    WITH PASSWORD = 'manish';

--Creating user with default schema "Exact_Help"

CREATE USER Manish

FOR LOGIN Manish

    WITH DEFAULT_SCHEMA = Exact_Help

Creating two tables in the schema dbo and Exact_Help respectively and inserting some records into it.

--Creating the table in schema dbo   

CREATE TABLE dbo.tblEmployee(

    ntID BIGINT PRIMARY KEY IDENTITY,

    vcName  VARCHAR(100)

)

--Inserting record into it

INSERT INTO dbo.tblEmployee VALUES('Scott')

Sql server add table to schema:

--Creating the table in schema exact_Help

CREATE TABLE Exact_Help.tblEmployee(

    ntID BIGINT PRIMARY KEY IDENTITY,

    vcName  VARCHAR(100)

)

--Inserting record into it

INSERT INTO Exact_Help.tblEmployee VALUES('Greg')    

Now I have logged in  the sql server with user name Manish and password manish. Now if we will execute following sql query:

SELECT * FROM tblEmployee

In which schema it will search the table tblEmployee?

First it will search in the default schema of the user Manish which is Exact_Help. And we will get output:

ntID	vcName
1	Greg

This is equivalent to write:

SELECT * FROM Exact_Help.tblEmployee

What will happen if I will drop the table tblEmployee from schema Exact_Help that is:

DROP TABLE Exact_Help.tblEmployee

Now if we will execute this sql query:

SELECT * FROM tblEmployee

Will we get error message?

In this case sql server will first search in the default schema of user Manish which Exact_Help. Since, it is not present here. So it will search in the schema dbo and we will get output:

ntID	vcName
1	Scott

This is equivalent to write:

SELECT * FROM dbo.tblEmployee

Performance Tips:

Always write full qualified object name. That is object name with schema name. Since sql server will search the object only in that schema. For example:

SELECT * FROM Exact_Help.tblEmployee

Or

SELECT * FROM dbo.tblEmployee

How to move or transfer table from one schema to other ? 

Sql server 2012 change table schema without dropping table in server explorer 

Syntax:

ALTER SCHEMA <Destination_Schema> TRANSFER <Source_Schema>.<Object_Name>

For example I want to move tblStudent from schema Exact_Help to dbo:

ALTER SCHEMA dbo TRANSFER Exact_Help.tblStudent

Where to find schema in sql 2012  

By following script you can all the schema in a database:

SELECT name 

FROM sys.schemas 

By using ssms we can get it from:

Go to Databases -> Your Database -> Security -> Schemas 

How to compile any query without executing it in sql server
How to perform case sensitive comparison in sql server
Sql server string comparison is case sensitive or not
Insert default value when data or parameter is null in sql server
SQL SERVER PPT