Dynamically operate columns in a table through stored procedures

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

1. Add Columns
Exec pro_addcolumn table name, column name, type/attribute ,''
Parameter table
@ Tablename varchar (100 ),
@ Columnname varchar (100 ),
@ Dbtype varchar (32 ),
@ MSG varchar (50) Output
2. Modify
Exec pro_addcolumn table name, column name, type/attribute ,''
Parameter table
@ Tablename varchar (100 ),
@ Columnname varchar (100 ),
@ Dbtype varchar (32 ),
@ MSG varchar (50) Output
 
3. Delete
Exec pro_addcolumn table name, column name
Parameter table
@ Tablename varchar (100 ),
@ Columnname varchar (100 ),
@ MSG varchar (50) Output
*/
----------------------------
-- 1. Add a stored procedure

If exists (Select name from sysobjects where name = 'Pro _ addcolumn' and type = 'P ')
Drop procedure pro_addcolumn
Go
Create procedure pro_addcolumn
@ Tablename varchar (100), @ columnname varchar (100), @ dbtype varchar (32), @ MSG varchar (50) Output
As
Declare @ flag char (1)
Set @ flag = 0
Set @ MSG = 'column addition failed! '
/*
Add columns to a specified table
*/
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. ['+ @ tablename +'] ') and objectproperty (ID, N 'isusertable') = 1)

If (not exists (select * From DBO. syscolumns where name = @ columnname and ID in
(Select ID from DBO. sysobjects where id = object_id (n' [DBO]. ['+ @ tablename +'] ') and objectproperty (ID, N 'isusertable') = 1 ))
)
Begin
-- Select 'not exist, now is adding... 'does not exist, add
Declare @ brief AddColumn varchar (100)
Select @ override AddColumn = 'alter table' + @ tablename + 'add' + @ columnname + ''+ @ dbtype
Exec (@ brief AddColumn)
Set @ flag = 1
Set @ MSG = 'column added successfully! '
End
Else
Begin
-- Select 'existed! Now is deleteing ......'
-- Exec pro_dropcolumn @ tablename, @ columnname
Set @ flag = 1
Set @ MSG = 'the column already exists! '
End
Select @ flag
Return @ flag

-- 2. delete a stored procedure

If exists (Select name from sysobjects where name = 'Pro _ dropcolum' and type = 'P ')
Drop procedure pro_dropcolumn
Go

Create procedure pro_dropcolumn
@ Tablename varchar (100), @ columnname varchar (100), @ MSG varchar (50) Output
As
/*
Deletes a specified column from a specified table.
*/
Begin
Declare @ flag char (1)

Set @ flag = 0
Set @ MSG = 'column deletion failed! '
------------------------------- The deletion process starts ------------------------------------------------------
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. ['+ @ tablename +'] ') and objectproperty (ID, N 'isusertable') = 1)

If (exists (select * From DBO. syscolumns where name = @ columnname and ID in
(Select ID from DBO. sysobjects where id = object_id (n' [DBO]. ['+ @ tablename +'] ') and objectproperty (ID, N 'isusertable') = 1 ))
)
Begin -- if this table and this column exist
-------------------------------- Start of deleting constraints -----------------------------------------------------
Declare @ tab varchar (100)
Declare @ defname varchar (100)
Declare @ cmd varchar (100)
Declare @ mongodel varchar (100)
Select @ defname = Name
From sysobjects so
Join sysconstraints SC
On so. ID = SC. constid
Where object_name (so. parent_obj) = @ tablename
And so. xtype = 'D'
And SC. colid =
(Select colid from syscolumns
Where id = object_id (@ tablename) and
Name = @ columnname)

Select @ cmd = 'alter table' + @ tablename + 'drop constraint' + @ defname
If @ CMD is null print 'no default constraint to drop' -- delete existing constraints
Exec (@ cmd)
------------------------------- End of the deletion constraint -----------------------------------------------------------

------------------------------- Start of deleting a specified column -----------------------------------------------------------
Select @ alter del = 'alter table' + @ tablename + 'drop column' + @ columnname
If @ cmddel is null
Begin
Print 'column deletion failed'
Set @ flag = 0
Set @ MSG = 'column deletion failed! '
End
Begin
Exec (@ mongodel)
Set @ flag = 1
Set @ MSG = 'column deleted successfully! '
End
------------------------------- End of deleting a specified column ------------------------------------------------------
End
------------------------------- Deletion process ended ----------------------------------------------------
Else -- some columns do not exist and do not need to be deleted
Begin
Set @ flag = 1
Set @ MSG = 'the specified column does not exist! '
End

Bytes -------------------------------------------------------------------------------------
Select @ flag
Return @ flag
End
Go

-- 3. Modify the Stored Procedure

If exists (Select name from sysobjects where name = 'Pro _ altercolumn' and type = 'P ')
Drop procedure pro_altercolumn
Go
Create procedure pro_altercolumn
@ Tablename varchar (100), @ columnname varchar (100), @ dbtype varchar (32), @ MSG varchar (50) Output
As
Declare @ flag char (1)
Set @ flag = 0
Set @ MSG = 'column addition failed! '
/*
Search for columns in a table
*/
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. ['+ @ tablename +'] ') and objectproperty (ID, N 'isusertable') = 1)

If (exists (select * From DBO. syscolumns where name = @ columnname and ID in
(Select ID from DBO. sysobjects where id = object_id (n' [DBO]. ['+ @ tablename +'] ') and objectproperty (ID, N 'isusertable') = 1 ))
)
Begin
-- Select 'existsed, now is alterring... 'modify
Declare @ brief AddColumn varchar (100)
Select @ override AddColumn = 'alter table' + @ tablename + 'alter column' + @ columnname + ''+ @ dbtype
Exec (@ brief AddColumn)
Set @ flag = 1
Set @ MSG = 'modification successful! '
End
Else
Begin
-- Select 'not existed! 'The column to be modified does not exist.
Set @ flag = 1
Set @ MSG = 'the column does not exist! '
End
Select @ flag
Return @ flag