Oct 28, 2013

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

12 comments:

arshiya fouzia said...

Thanks for sharing good content
Plsql training

Aishwariya said...

This Was An Amazing ! I Haven't Seen This Type of Blog Ever ! Thankyou For Sharing Primavera Online Training | Primavera Course in Chennai

thagukavi said...

Amazing Post. keep update more information.
Web Designing Course in chennai
web Designing Training in Chennai
Web Designing Course Online

Shiva Shakthi said...

Really wonderful blog! Thanks for taking your valuable time to share this with us. Keep us updated with more such blogs.
AWS Certification in Chennai
AWS Online Training
AWS Training in Coimbatore

Vijayakash said...

This blog is a great source of information which is very useful for me.

graphic design courses in tambaram
graphic design courses in Porur
graphic design courses in Chennai

Vijayakash said...

it was so good to read and useful to improve my knowledge as updated one, keep blogging…

Java Training in Tambaram
java course in tambaram
java training in velachery
java training institute in velachery
java training in anna nagar
java training in t nagar
java training in porur
java training in OMR
Java training in chennai

malathy mine said...

Great post. It is really helpful for me.keep sharing such a worthy information..
Web Development courses in Chennai

Keerthi55 said...

MuleSoft training
MuleSoft online training

Unknown said...

I was looking at a portion of your posts on this site and I consider this site is really enlightening! Keep posting.
Servicenow Training In Hyderabad

Unknown said...

Thanks for sharing nice content
Servicenow Training In Hyderabad

Jothi said...

Nice blog to read. Thanks.
Full Stack Developer Online Training
Full Stack Online Course

Dettifoss IT Solutions said...

Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
servicenow training in Bangalore