Friday, May 10, 2013

Create Rename and Drop tables in SQL

Introduction

These statements are part of the Data Definition Language (DDL) of the SQL specification. We can use them to create other database objects like that function, triggers, events, views or procedures. The CREATE, Rename and DROP statements are not limited to tables.

Create Table

The CREATE statement is used to create tables in Database. When we create a table, we give a name to a table and to its columns. Each column has a data type. CREATE statement is also used to create indexes, views, events, routines and triggers.

Query

create table EmpInfo
(
Emp_Id int,
F_Name varchar(50),
L_Name varchar(50),
Emp_Add varchar(150),
City varchar(150),
Phone int
)
Now insert some data in the table with below query
insert into EmpInfo values
(
1,'Arvind','Kumar','B1-269','Noida',1234567890
)
Now see the table below query



Rename Table

Renames a table in the current database. Suppose I created a table name called "EmpInfo" with records. If we want to rename this table with new name then execute these query.

sp_rename 'EmpInfo','Emp_Information'

Now again see the table but given different name in "Select" query



Drop Table
If you want to remove a table definition and all data, indexes, constraints, and permission specifications for that table. then we will use Drop.


After Drop table you will again see the table with "select" query. it generated Error.