sql修改表及表中列

sql语句动态添加列名的问题
------解决方案--------------------------------------------------------
create table #t (id int)

alter table #t add c1 int

declare @c varchar(10)
set @c='c2'
exec('alter table #t add '+@c+' int')

select * from #t
/*
id c1 c2 
----------- ----------- ----------- 
*/

drop table #t

alter标准范式

ALTER TABLE table { [ ALTER COLUMN column_name     { new_data_type [ ( precision [ , scale ] ) ]        [ COLLATE < collation_name > ]        [ NULL | NOT NULL ]        | {ADD | DROP } ROWGUIDCOL }    ]     | ADD        { [ < column_definition > ]        | column_name AS computed_column_expression        } [ ,...n ]    | [ WITH CHECK | WITH NOCHECK ] ADD        { < table_constraint > } [ ,...n ]     | DROP        { [ CONSTRAINT ] constraint_name             | COLUMN column } [ ,...n ]     | { CHECK | NOCHECK } CONSTRAINT        { ALL | constraint_name [ ,...n ] }    | { ENABLE | DISABLE } TRIGGER        { ALL | trigger_name [ ,...n ] } }< column_definition > ::=    { column_name data_type }    [ [ DEFAULT constant_expression ] [ WITH VALUES ]    | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]        ]     [ ROWGUIDCOL ]    [ COLLATE < collation_name > ]    [ < column_constraint > ] [ ...n ]< column_constraint > ::=    [ CONSTRAINT constraint_name ]    { [ NULL | NOT NULL ]        | [ { PRIMARY KEY | UNIQUE }            [ CLUSTERED | NONCLUSTERED ]            [ WITH FILLFACTOR = fillfactor ]            [ ON { filegroup | DEFAULT } ]            ]         | [ [ FOREIGN KEY ]            REFERENCES ref_table [ ( ref_column ) ]            [ ON DELETE { CASCADE | NO ACTION } ]            [ ON UPDATE { CASCADE | NO ACTION } ]            [ NOT FOR REPLICATION ]            ]         | CHECK [ NOT FOR REPLICATION ]            ( logical_expression )     }< table_constraint > ::=    [ CONSTRAINT constraint_name ]    { [ { PRIMARY KEY | UNIQUE }        [ CLUSTERED | NONCLUSTERED ]        { ( column [ ,...n ] ) }         [ WITH FILLFACTOR = fillfactor ]        [ ON { filegroup | DEFAULT } ]        ]         |    FOREIGN KEY            [ ( column [ ,...n ] ) ]            REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]            [ ON DELETE { CASCADE | NO ACTION } ]            [ ON UPDATE { CASCADE | NO ACTION } ]            [ NOT FOR REPLICATION ]        | DEFAULT constant_expression            [ FOR column ] [ WITH VALUES ]        |    CHECK [ NOT FOR REPLICATION ]            ( search_conditions )     }示例A. 更改表以添加新列下例添加一个允许空值的列，而且没有通过 DEFAULT 定义提供值。各行的新列中的值将为 NULL。CREATE TABLE doc_exa ( column_a INT) GOALTER TABLE doc_exa ADD column_b VARCHAR(20) NULLGOEXEC sp_help doc_exaGODROP TABLE doc_exaGOB. 更改表以除去列下例修改表以删除一列。CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL) GOALTER TABLE doc_exb DROP COLUMN column_bGOEXEC sp_help doc_exbGODROP TABLE doc_exbGOC. 更改表以添加具有约束的列下例向表中添加具有 UNIQUE 约束的新列。 CREATE TABLE doc_exc ( column_a INT) GOALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL    CONSTRAINT exb_unique UNIQUEGOEXEC sp_help doc_excGODROP TABLE doc_excGOD. 更改表以添加未验证的约束下例向表中的现有列上添加约束。该列中存在一个违反约束的值；因此，利用 WITH NOCHECK 来防止对现有行验证约束，从而允许该约束的添加。CREATE TABLE doc_exd ( column_a INT) GOINSERT INTO doc_exd VALUES (-1)GOALTER TABLE doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a > 1)GOEXEC sp_help doc_exdGODROP TABLE doc_exdGOE. 更改表以添加多个带有约束的列下例向表中添加多个带有约束的新列。第一个新列具有 IDENTITY 属性；表中每一行的标识列都将具有递增的新值。CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) GOALTER TABLE doc_exe ADD /* Add a PRIMARY KEY identity column. */ column_b INT IDENTITYCONSTRAINT column_b_pk PRIMARY KEY, /* Add a column referencing another column in the same table. */ column_c INT NULL  CONSTRAINT column_c_fk REFERENCES doc_exe(column_a),/* Add a column with a constraint to enforce that   */ /* nonnull data is in a valid phone number format.  */column_d VARCHAR(16) NULL CONSTRAINT column_d_chkCHECK (column_d IS NULL OR column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" ORcolumn_d LIKE"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),/* Add a nonnull column with a default.  */ column_e DECIMAL(3,3)CONSTRAINT column_e_defaultDEFAULT .081GOEXEC sp_help doc_exeGODROP TABLE doc_exeGOF. 添加具有默认值的可为空的列下例添加可为空的、具有 DEFAULT 定义的列，并使用 WITH VALUES 为表中的各现有行提供值。如果没有使用 WITH VALUES，那么每一行的新列中都将具有 NULL 值。ALTER TABLE MyTable ADD AddDate smalldatetime NULLCONSTRAINT AddDateDfltDEFAULT getdate() WITH VALUESG. 禁用并重新启用一个约束下例禁用用于限制可接受的薪水数据的约束。WITH NOCHECK CONSTRAINT 与 ALTER TABLE 一起使用，以禁用该约束并使正常情况下会引起约束违规的插入操作得以执行。WITH CHECK CONSTRAINT 重新启用该约束。CREATE TABLE cnst_example (id INT NOT NULL, name VARCHAR(10) NOT NULL, salary MONEY NOT NULL    CONSTRAINT salary_cap CHECK (salary < 100000))-- Valid insertsINSERT INTO cnst_example VALUES (1,"Joe Brown",65000)INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)-- This insert violates the constraint.INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)-- Disable the constraint and try again.ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_capINSERT INTO cnst_example VALUES (3,"Pat Jones",105000)-- Reenable the constraint and try another insert, will fail.ALTER TABLE cnst_example CHECK CONSTRAINT salary_capINSERT INTO cnst_example VALUES (4,"Eric James",110000)H. 禁用并重新启用触发器下例使用 ALTER TABLE 的 DISABLE TRIGGER 选项来禁用触发器，以使正常情况下会违反触发器条件的插入操作得以执行。然后下例使用 ENABLE TRIGGER 重新启用触发器。CREATE TABLE trig_example (id INT, name VARCHAR(10),salary MONEY)go-- Create the trigger.CREATE TRIGGER trig1 ON trig_example FOR INSERTas IF (SELECT COUNT(*) FROM INSERTEDWHERE salary > 100000) > 0BEGINprint "TRIG1 Error: you attempted to insert a salary > $100,000"ROLLBACK TRANSACTIONENDGO-- Attempt an insert that violates the trigger.INSERT INTO trig_example VALUES (1,"Pat Smith",100001)GO-- Disable the trigger.ALTER TABLE trig_example DISABLE TRIGGER trig1GO-- Attempt an insert that would normally violate the triggerINSERT INTO trig_example VALUES (2,"Chuck Jones",100001)GO-- Re-enable the trigger.ALTER TABLE trig_example ENABLE TRIGGER trig1GO-- Attempt an insert that violates the trigger.INSERT INTO trig_example VALUES (3,"Mary Booth",100001)GO