Add a new column to the SQL Server table and add a description _mssql

Note: SQL Server 2005 and above support. Version estimates are not supported (working environment 2005,2008).

To work, you need to add a new column to the existing table in SQL Server and add a description. Thus there is a stored procedure as follows. (Attach the stored procedure and then explain)

/******** Invoke method ********** function: Add column and add column description information 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 = ' NVARCHAR (50) '--column type for example: NVARCHAR, @columnDefault NVARCHAR (m) = ' NULL '--column default values such as: NULL as Be Gin Ifnotexists (SELECT * from syscolumns WHERE id = object_id (@tableName) and NAME = @columnName) BEGIN print ' EXEC: ' + ( ' ALTER TABLE ' + @tableName + ' ADD ' + @columnName + ' + @columnType + ' + @columnDefault) print ' add[' + @columnName + ']c
Olumn ' EXEC (' ALTER TABLE ' + @tableName + ' ADD ' + @columnName + ' + @columnType + ' + @columnDefault) End ifexists ( SELECT * From::fn_listextendedproperty (' ms_description ', ' SCHEMA '-user or user-defined type, n ' dbo '--the name of the specified level 0 object type, n ' TABLE '--Level 1 object Type, @tableName--the name of the specified level 1 object type, N ' COLUMN '--the type of Level 2 object, @columnName--the name of the specified 2-level object type)) BEGIN print' edit[' + @columnName + ']description ' EXEC sys.sp_updateextendedproperty @name = N ' ms_description '--Name of the property to add, @value = @ Columninfo--The value that will be associated with the property, @level0type = N ' SCHEMA '-user or user-defined type, @level0name = n ' dbo '--the name of the specified level 0 object type, @level1type = N ' TABLE '--the type of Level 1 object, @level1name = @tableName--The name of the specified level 1 object type, @level2type = N ' COLUMN '--the type of the 2-level object, @level2name = @columnName- The name of the specified level 2 object type End ELSE BEGIN print ' add[' + @columnName + ']description ' EXEC sys.sp_addextendedproperty @name = N ' Ms_descrip tion '--the name of the property to add, @value = @columnInfo--The value that will be associated with the property, @level0type = N ' SCHEMA '-user or user-defined type, @level0name = n ' dbo '--the specified level 0 object The name of the type, @level1type = N ' TABLE '--the type of the Level 1 object, @level1name = @tableName--The name of the Class 1 object type specified, @level2type = n ' COLUMN '-the type of the 2-level object, @level2name = @columnName--Name of the specified level 2 object type End Go

Explain:

Statement:

Action: Find out if the specified column exists in the table. If present, the addition will cause an error.

ALTER TABLE Statement:

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

If you want to add columns to the table, use the following syntax:

To delete a column from a table, use the following syntax:

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

Properties of additions and deletions change:

Fn_listextendedproperty: Gets the extended property, mainly determines if the attribute exists, if it exists, if it does not exist, add

Sp_updateextendedproperty: Update Field description

Sp_addextendedproperty: Adding a field description

Sp_dropextendedproperty: Delete Field description

Because the sp_dropextendedproperty above stored procedure does not appear the special attached example:

Execsp_dropextendedproperty ' ms_description ', ' user ', dbo, ' table ', ' Tables ', ' column ', A1

The above is a small set to introduce the SQL Server table to add new columns and add a description, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!