Operating tables through stored procedures

--------------------------
/* Usage

1. Add Columns
Exec pro_addcolumn table name, column name ,''
Parameter table
@ Tablename varchar (100 ),
@ MSG varchar (50) Output
 
3. Delete
Exec pro_addcolumn table name ,''
Parameter table
@ Tablename varchar (100 ),
@ MSG varchar (50) Output
*/

-- 1. Add a new table

If exists (Select name from sysobjects where name = 'Pro _ createtable' and type = 'P ')
Drop procedure pro_createtable

Go

Create procedure pro_createtable
@ Tablename varchar (20 ),
@ MSG varchar (50) Output
As

Declare @ flag char (1)
Set @ flag = 0
Set @ MSG = 'table addition failed! '

/* If the table does not exist */
If not exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. ['+ @ tablename +'] ') and objectproperty (ID, N 'isusertable') = 1)
Begin
/* Create default table information */
Exec ('create table' + @ tablename +'
(Fd_no nvarchar (15 ),
Fd_xl nvarchar (10 ),
Fd_master nvarchar (10 ),
Fd_stdno nvarchar (3) primary key,
Fd_stdname nvarchar (20 ),
Fd_age nvarchar (4 ),
Fd_zoom nvarchar (15)
)')
Set @ flag = 1
Set @ MSG = 'table created successfully! '
End
Else
Begin
Set @ flag = 1
Set @ MSG = 'the table already exists! '
End
Select @ flag
Return @ flag

-- 2. delete a table

If exists (Select name from sysobjects where name = 'Pro _ droptable 'and type = 'P ')
Drop procedure pro_droptable

Go

Create procedure pro_droptable
@ Tablename varchar (20 ),
@ MSG varchar (50) Output
As

Declare @ flag char (1)
Set @ flag = 0
Set @ MSG = 'table addition failed! '
/* If the table exists */
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. ['+ @ tablename +'] ') and objectproperty (ID, N 'isusertable') = 1)
Begin
/* Create default table information */
Exec ('drop table' + @ tablename)
Set @ flag = 1
Set @ MSG = 'table deleted! '
End
Else
Begin
Set @ flag = 1
Set @ MSG = 'The table does not exist! '
End
Select @ flag
Return @ flag