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

Note: SQL Server 2005 and above support. Version estimation is 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 explain it first)

Code

/******** Call 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] @tableNameNVARCHAR(+) -name of the table, @columnNameNVARCHAR(+) --column name , @columnInfoNVARCHAR(a) --Column description, description , @columnTypeNVARCHAR(+) = 'NVARCHAR' --column type for example: NVARCHAR, @columnDefaultNVARCHAR(+) = 'null' ---column default value for example: NULL  asbeginIF notEXISTS (SELECT * fromsyscolumnsWHEREid = object_id (@tableName) andNAME = @columnName)BEGINPrint'exec:' + ('ALTER TABLE ' + @tableName + ' ADD ' + @columnName + ' + ' + @columnType + ' + @co Lumndefault)Print'add[' [email protected]+ ']column'EXEC('ALTER TABLE ' + @tableName + ' ADD ' + @columnName + ' + ' + @columnType + ' + @columnDefault) ' /c9>ENDIFEXISTS (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' --type of Level 1 object , @tableName --The name of the specified level 1 object type , N 'COLUMN' --the type of the 2-level object , @ ColumnName --name of the specified level 2 object type ))BEGINPrint'edit[' [email protected]+ ']description'EXECsys.Sp_updateextendedproperty@name = N 'ms_description' --the name of the property to be added , @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' --type of 1-level object , @ Level1name = @tableName --The name of the specified level 1 object type , @ Level2type = N 'COLUMN' -the type of 2-level object , @ Level2name = @columnName --The name of the specified level 2 object type ENDELSEBEGINPrint'add[' [email protected]+ ']description'EXECsys.sp_addextendedproperty@name = N 'ms_description' --the name of the property to be added , @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' --type of 1-level object , @ Level1name = @tableName --The name of the specified level 1 object type , @ Level2type = N 'COLUMN' -the type of 2-level object , @ Level2name = @columnName --The name of the specified level 2 object type ENDEndGO

Explain:

statement: SELECT * from syscolumns WHERE id = object_id (@tableName) and NAME = @columnName

Function: Finds whether the specified column exists in the table. Add will error if present.

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_name

ADD

column_name datatype

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

ALTER TABLE

table_name

DROP COLUMN

column_name

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

ALTER TABLE

table_name

ALTER COLUMN

column_name datatype

Attribute additions and deletions:

Fn_listextendedproperty : Gets the extended property, primarily determines whether the property exists if it exists, updates, does not exist, and adds

Sp_updateextendedproperty : Update Field Description

Sp_addextendedproperty

: Add Field Description

sp_dropextendedproperty: Delete Field Description

because Sp_dropextendedproperty does not appear in the stored procedure above, there is an attached example:

EXEC Sp_dropextendedproperty

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

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