Add new columns and descriptions in the SQL Server table, and add descriptions in the server table.

Add new columns and descriptions in the SQL Server table, and add descriptions in the server table.

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)

/******************* 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} '*******************/CREATEPROCEDURE [dbo]. [SetColumnInfo] @ tableName NVARCHAR (100) -- table name, @ columnName NVARCHAR (100) -- column name, @ columnInfo NVARCHAR (2000) -- column description, description, @ columnType NVARCHAR (100) = 'nvarchar (50) '-- column type, for example, NVARCHAR (50), @ columnDefault NVARCHAR (100) = 'null' -- column default value, for example: NULLasbeginIFNOTEXISTS (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) ENDIFEXISTS (SELECT * FROM: fn_listextendedproperty ('Ms _ description', 'scheme' -- 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 -- the name of the specified level 2 object type) BEGINprint 'edit ['+ @ columnName +'] Description 'exec sys. sp_updateextendedproperty @ name = n' MS _ description' -- name of the attribute to be added, @ value = @ columnInfo -- value to be associated with the attribute, @ level0type = n'scheme' -- 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 attribute to be added, @ value = @ columnInfo -- value to be associated with the attribute, @ level0type = n'scheme' -- 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:

ALTERTABLE table_name ADD column_name datatype 

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

ALTERTABLE table_name DROPCOLUMN column_name 

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

ALTERTABLE table_name ALTERCOLUMN 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:

EXECsp_dropextendedproperty 'Ms _ description', 'user', dbo, 'table', 'table', 'column', a1

The above section describes how to add new columns and descriptions in the SQL Server table. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!