Sqlserver adds, modifies, deletes, and describes fields.

Copy codeThe Code is as follows: -- add a table Field
ALTER procedure [dbo]. [sp_Web_TableFiled_Insert]
(
@ TableName varchar (100 ),
@ FieldName varchar (100 ),
FieldExplain varchar (200 ),
@ DataType varchar (100 ),
@ ConnectTableName varchar (100 ),
@ FieldLength int,
@ NewsID int output
)
As
Begin transaction mytran
Declare @ errorSum int
If not exists (SELECT * FROM syscolumns where id = object_id (@ TableName) AND name = @ FieldName)
Begin
Insert tb_TableField
(
TableName,
FieldName,
FieldExplain,
DataType,
ConnectTableName,
FieldLength,
UserSetSign
)
Values
(
@ TableName,
@ FieldName,
@ FieldExplain,
@ DataType,
@ ConnectTableName,
@ FieldLength,
'1'
)
Declare @ SQL varchar (8000)
-- Judgment type
If (@ DataType = 'decimal ')
Begin
Set @ SQL = 'alter table' + @ TableName + 'add' + @ FieldName + ''+ @ DataType + '(' + Convert (varchar, @ FieldLength) + ', 2' + ')'
End
Else if (@ DataType = 'varchar ')
Begin
Set @ SQL = 'alter table' + @ TableName + 'add' + @ FieldName + ''+ @ DataType + '(' + Convert (varchar, @ FieldLength) + ')'
End
Else
Begin
Set @ SQL = 'alter table' + @ TableName + 'add' + @ FieldName + ''+ @ DataType
End
Exec (@ SQL)
EXECUTE sp_addextendedproperty N 'Ms _ description', @ FieldExplain, N 'user', N 'dbo', N 'table', @ TableName, N 'column', @ FieldName;
Set @ errorSum = @ errorSum + @ error
Set @ NewsID = 0;
End
Else
Begin
Set @ NewsID = 1;
End
If (@ errorSum> 0)
Begin
Rollback tran
End
Else
Begin
Commit tran mytran
End
-- Modify Table Fields
ALTER procedure [dbo]. [sp_Web_TableFiled_Update]
(
@ TableName varchar (100 ),
@ FieldName varchar (100 ),
FieldExplain varchar (200 ),
@ DataType varchar (100 ),
@ ConnectTableName varchar (100 ),
@ FieldLength int,
@ ID int,
@ NewsID int output
)
As
Begin transaction mytran
Declare @ fname varchar (100)
Declare @ errorSum int
-- First retrieve the name of the previous field in the table
Select @ fname = FieldName from tb_TableField where ID = @ ID
Declare @ pstid int
Declare @ SQL varchar (8000)
-- Retrieve the ID corresponding to tb_PaySystemToLocation Based on the field name.
Select @ pstid = ID from tb_PaySystemToLocation where LocationField = @ fname
Set @ SQL = 'SP _ rename' + CHAR (39) + @ TableName + '. ['+ @ fname +'] '+ CHAR (39) +', '+ char (39) + @ FieldName + char (39) +', '+ char (39) + 'column '+ CHAR (39)
Exec (@ SQL)
Update tb_TableField
Set TableName = @ TableName,
FieldName = @ FieldName,
FieldExplain = @ FieldExplain,
DataType = @ DataType,
ConnectTableName = @ ConnectTableName,
FieldLength = @ FieldLength
Where ID = @ ID
-- Modify Field description
EXECUTE sp_updateextendedproperty N 'Ms _ description', @ FieldExplain, N 'user', N 'dbo', N 'table', @ TableName, N 'column', @ FieldName;
-- EXEC sp_updateextendedproperty 'Ms _ description', @ FieldExplain, 'user', dbo, 'table', @ TableName, 'column', @ FieldName
Set @ NewsID = 0;
Set @ errorSum = @ errorSum + @ error
If (@ ERROR> 0)
Begin
Rollback tran
End
Else
Begin
Commit tran mytran
End
-Delete table fields
ALTER procedure [dbo]. [sp_Web_TableFiled_Delete]
(
@ ID int,
@ NewsID int output
)
As
Begin transaction mytran
Declare @ fname varchar (100)
Declare @ tablename varchar (100)
Declare @ pstid int
Declare @ SQL varchar (8000)
Declare @ errorSum int
-- Retrieve the field name and table name
Select @ fname = FieldName, @ tablename = TableName from tb_TableField where ID = @ ID
-- Retrieve the ID of tb_PaySystemToLocation
Select @ pstid = ID from tb_PaySystemToLocation where LocationField = @ fname
Delete from tb_TableField where ID = @ ID
Set @ SQL = 'alter table' + @ tablename + 'drop column' + @ fname
Exec (@ SQL)
Set @ errorSum = @ errorSum + @ error
Set @ NewsID = 0;
If (@ errorSum> 0)
Begin
Rollback tran
End
Else
Begin
Commit tran mytran
End