Add a new column to an existing SQL Server table and add a description.

Note: SQL Server 2005 and later versions are supported. This version is estimated to be not supported (working environment ).

To work, you need to add a new column to the existing SQL Server table and add a description. The stored procedure is as follows (attach the stored procedure first and then explain)

 Code  /******************* Function: Add a column and add a column description to call: exec [setcolumninfo] 'table name', 'column name', n' column description, description', 'column type {default: nvarchar (50)} ',' column default value {default: null }'******************/ Create procedure [DBO]. [setcolumninfo] @ tablename nvarchar (100) -- Table name , @ Columnname nvarchar (100) -- Column name , @ Columninfo nvarchar (2000) -- Column description , @ Columntype nvarchar (100) =' Nvarchar (50) ' -- Column type, for example, nvarchar (50) , @ Columndefault nvarchar (100) =' Null ' -- Column default value, for example, null Asbeginif not exists (select * From syscolumnswhere id = object_id (@ tablename) and name = @ columnname) beginprint' Exec: '+ (' ALTER TABLE '+ @ Tablename +' Add '+ @ Columnname +'   '+ @ Columntype +'   '+ @ Columndefault) print' Add [ '+ @ Columnname +' ] Column 'Exec (' ALTER TABLE '+ @ Tablename +'Add '+ @ Columnname +'   '+ @ Columntype +'   '+ @ Columndefault) endif exists (select * From: fn_listextendedproperty (' Ms_description ',' Schema ' -- User or user-defined type , N' DBO ' -- Name of the specified level 0 object type , N' Table ' -- Type of level 1 Object , @ Tablename -- Name of the specified level 1 Object Type , N'Column ' -- Type of level 2 object , @ Columnname -- Name of the specified level 2 object type ) Beginprint' Edit [ '+ @ Columnname +' ] Description 'Exec SYS. sp_updateextendedproperty @ name = N' Ms_description ' -- Name of the property to be added , @ Value = @ columninfo -- Value to be associated with the attribute , @ Level0type = N' Schema ' -- User or user-defined type , @ Level0name = N' DBO '-- Name of the specified level 0 object type , @ Level1type = N' Table ' -- Type of level 1 Object , @ Level1name = @ tablename -- Name of the specified level 1 Object Type , @ Level2type = N' Column ' -- Type of level 2 object , @ Level2name = @ columnname -- Name of the specified level 2 object type Endelsebeginprint' Add [ '+ @ Columnname +' ] Description 'Exec SYS. sp_addextendedproperty @ name = N' Ms_description ' -- Name of the property to be added , @ Value = @ columninfo -- Value to be associated with the attribute , @ Level0type = N' Schema ' -- User or user-defined type , @ Level0name = N' DBO ' -- Name of the specified level 0 object type , @ Level1type = N' Table ' -- Type of level 1 Object , @ Level1name = @ tablename -- Name of the specified level 1 Object Type , @ Level2type = N' Column ' -- Type of level 2 object , @ Level2name = @ columnname -- Name of the specified level 2 object type Endendgo

 

Explanation:

Statement: Select * From syscolumns where id = object_id (@ tablename) and name = @ columnname

Purpose: Check whether a specified Column exists in the table. If yes, an error is returned when adding the column.

Alter table statement:

The alter table statement is used to add, modify, or delete columns in an existing table.

To add columns to a table, use the following syntax:

 
Alter table table_nameadd column_name datatype
 

 
To delete columns in a table, use the following syntax:
 

 
Alter table table_name drop column column_name
 

To change the data type of columns in a table, use the following syntax:

 
Alter table table_namealter column column_name datatype
 

Add, delete, and modify attributes:

Fn_listextendedproperty: obtains the extended property. It mainly determines whether the property exists. If it exists, it is updated. If it does not exist, it is added.

Sp_updateextendedproperty: update field description

 
Sp_addextendedproperty: add field description
 

 
Sp_dropextendedproperty:Delete field description
 

 
Because sp_dropextendedproperty does not show a special example in the above stored procedure:
 

 
Exec sp_dropextendedproperty'Ms_description','User', DBO ,'Table','Table','Column', A1