SQL Server adds modify deletion of fields, as well as descriptions of fields _mssql

Copy Code code 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
--Modifying a table field
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 remove the previous field name from the table
Select @fname =fieldname from Tb_tablefield where id= @ID
DECLARE @pstid int
DECLARE @sql varchar (8000)
--then remove the corresponding ID from the tb_paysystemtolocation based on the field name
Select @pstid =id from tb_paysystemtolocation where locationfield= @fname
Set @sql = ' sp_rename ' +char + @TableName + '. [' + @fname + '] ' +char (+) + ', ' +char (+) + @FieldName +char (+) + ', ' + CHAR (+) + ' 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 the 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 a table field
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
--Remove field name, table name
Select @fname =fieldname, @tablename =tablename from Tb_tablefield where id= @ID
--Remove the Tb_paysystemtolocation ID
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