Sql server part 1
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
databases.
The syntax are :-
CREATE DATABASE database_name
For example :-
CREATE DATABASE microid.
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.
sYNTAX ARE :-
DROP DATABASE database name .
Example :-
DROP DATABASE employees
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 table.you can create table by using the following Syntax.Ypu can use the CREATE TABLE statement
to create a table .
Syntax:-
CREATE TABLE [ DATABASE _NAME. [SCHEMA_NAME].] table name.
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
{
[ NULL | NOT NULL ]
}
| [ WITH { CHECK | NOCHECK } ] ADD COLOUMN
{
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
ADD MASTERID char (30) NOT NULL
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, Tweet it or share this article on other bookmarking websites.