System database     are the type of database that exist in each and every instance of SQL server 2005 or higher.It contains table that store  Server configurations,Templates etc.

System database as :-

(a) Master database:    - This database keeps all the information of server - specific configuration , authorized user , System configuration setting. It also keep the record of 

metadata like endpoints , logon account and System configuration settings.

(b) Tempdb database      :- It is just like a temporary database that contain all the temporary table and also store some procedure.

(c) The model database     :- This Type of database is used as a template for the new database.whenever we try to create new database , The content of this database is supplied 

to new database.

(d) Msdb  database      :- It support SQL server agent.It contain task Scheduling and Exception handling.

(e) Resources database    :- This database is read only database .It does not contain the properties like user data or user meta data.

How to create a user-Defined database.

Other that the System database, The SQL server 2005 or higher also support user defined databases. you can use the CREATE DATABASE statement for crating the user defined

The syntax are :-

CREATE DATABASE database_name

For example :-


It create the database with name microid.

To see the details of created database you must use this as given below.

sp_helpdb [ database name ].

Modifying the database.

Rename a  database created by you.

If you are the owner of the database then you can easily rename it with a different name as your choice.The Syntax which is used to rename a database is as follows:-

sp_ renamedb old_database_name , new_ database_name.

For example.

sp_renamedb personal , employee

It rename a database name from personal to employee.

Deleting a  database created by you .

If you not required any database then you simply delete it .The database owner have right to delete the database creted by him.You can use the DROP DATABASE statement.


DROP DATABASE database name .

Example :-


It will delete the database name employees.

Creating a table .

You can easily create a table in a database and store you data in that can create table by using the following Syntax.Ypu can use the CREATE TABLE statement
to create a table .



schema_name it indicate the schema name where a new table belongs.

database_name indicate the name of the database.

table_name - name f the table.

For example.

CREATE TABLE sank.chandaid

It create a table chandanid in the database sank.

After creating table you may want to see the details of the table by using this as given below:-

sp_help [ table name ].

Point to be rember  while creating a table in a database.

(a) The coloumn name must be unique . for diffeent table you can use the same name.

(b) Name of the table cannot exceed more than 128 Characters.

Modifying a table :-

Adding a coloum  to a table.

After creating a table if you want to add one or more coloumn in a table which is created earlier by you then you use the syntax given below :-

Syntax are :-

ALTER TABLE [ database _name . [ schema_name]. ] tble _ name

ALTERCOLOUMN coloumn_name



ADD CONSTRAINT constraint _ name constraint _ type.

schema_name it indicate the schema name where a new table belongs.

database_name indicate the name of the database.

table_name - name f the table that is going to be altered.

alter coloumn indicate the name of the coloumn that is going to be altered.

add coloumn indicate the name of the coloumn that is going to be added in a table.

For example :-

ALTER TABLE sank.chandanid

by the above example you are able to add the masterid coloumn to the table chandanid.

Renaming the tabble.

Whit your requirement you can rename a table as required. You can use the sp_rename store procedure to rename a table.

Syntax are :-

sp_rename old_name, new_name

Example are :-

sp_rename [ sank.chandanid],[ sank.masterid ].

This statement will change table name from chandanid to masterid.

Dropping a table.

You can also delete unnecessary table by using a syntax given below:-

The syntax are:-

DROP TABLE [ database_name .[ schema_name ] . ] table_name.

schema_name - It Will indicate the schema name where a new table belongs.

database_name - It will indicate the name of the database.

table_name - It will indicate the name of the table.

Example are:- DROP TABLE sank.masterid.

It will delete the table who name is masterid.


Like it on Facebook, +1 on Google, Tweet it or share this article on other bookmarking websites.

Written by
chandan kumar
  • No comments found

We are one community is a community driven articles and knowledge sharing portal. Members can share their knowledge with other members and visitors of the website and make them-self popular in the massive Internet world. is used not only as a platform to share the content and knowledge, but also a useful tool to learn from the vast amount of knowledge shared by thousands of registered users of the website. The content directory is a host of all the articles posted by the community and is available for the visitors of to read and learn from the extensive knowledge,