For more information about how to add, modify, delete, and describe fields, see.
For more information about how to add, modify, delete, and describe fields, see.
The Code is as follows:
-- New 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