--修改表名--格式：SP_RENAME TABLENAME,NEWTABLENAMESP_RENAME TABLENAME,NEWTABLENAME--只能对表，不能对临时表--修改字段名--格式：SP_RENAME 'TABLENAME.COLNAME',NEWCOLNAME,'COLUMN'SP_RENAME 'TABLENAME.COLNAME',NEWCOLNAME,'COLUMN' --添加字段--格式：ALTER TABLE TABLE_NAME ADD NEW_COLUMN DATA_TYPE [INTERALITY_CODITION]示例1ALTER TABLE STUDENT ADD NATIONALITY VARCHAR(20)ALTER TABLE STUDENT ADD ID IDENTITY INT(1,1)--示例2 添加INT类型的列,默认值为 0ALTER TABLE STUDENT ADD STUDENTNAME INT DEFAULT 0--示例3 添加INT类型的列,默认值为0，主键ALTER TABLE STUDENT ADD STUDENTID INT PRIMARY KEY DEFAULT 0 --示例4 判断STUDENT中是否存在NAME字段且删除字段IF EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID('STUDENT') AND NAME='NAME') BEGIN ALTER TABLE STUDENT DROP COLUMN NAME END 更改字段格式：ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAMEALTER TABLE STUDENT ALTER COLUMN NAME VARCHAR(200)删除字段格式：ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAMEALTER TABLE STUDENT DROP COLUMN NATIONALITY;查看字段约束格式： SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = TABLE_NAMESELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGEWHERE TABLE_NAME = 'STUDENT'查看字段缺省约束表达式 （即默认值等）格式：SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TABLE_NAMESELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='STUDENT'查看字段缺省约束名格式：SELECT NAME FROM SYSOBJECTS WHERE OBJECT_ID(TABLE_NAME)=PARENT_OBJ AND XTYPE=’D’SELECT NAME FROM SYSOBJECTSWHERE OBJECT_ID('表?名?')=PARENT_OBJ AND XTYPE='D'删除字段约束格式：ALTER TABLE TABLENAME DROP CONSTRAINT CONSTRAINTNAMEALTER TABLE STUDENT DROP CONSTRAINT PK__STUDENT__2F36BC5B772B9A0B添加字段约束格式：ALTER TABLE TABLENAME ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY (COLUMN_NAME)--示例1ALTER TABLE STUINFO ADD CONSTRAINT PK_STUNO PRIMARY KEY (STUNO)--示例2 添加主键约束（PRIMARY KEY）-- 存在主键约束PK_STUNO，则删除IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='PK_STUNO' AND XTYPE='PK')ALTER TABLE STUINFODROP CONSTRAINT PK_STUNOGO-- 重新添加主键约束PK_STUNOALTER TABLE STUINFO ADD CONSTRAINT PK_STUNO PRIMARY KEY (STUNO)GO--示例3 添加 唯一UQ约束（UNIQUE CONSTRAINT）-- 存在唯一约束UQ_STUNO，则删除IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='UQ_STUID' AND XTYPE='UQ')ALTER TABLE STUINFODROP CONSTRAINT UQ_STUIDGO-- 重新添加唯一约束UQ_STUIDALTER TABLE STUINFO ADD CONSTRAINT UQ_STUID UNIQUE (STUID)--示例4 添加默认DF约束（DEFAULT CONSTRAINT）-- 存在默认约束UQ_STUNO，则删除IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='DF_STUADDRESS' AND XTYPE='D')ALTER TABLE STUINFO DROP CONSTRAINT DF_STUADDRESSGO-- 重新添加默认约束DF_STUADDRESSALTER TABLE STUINFO ADD CONSTRAINT DF_STUADDRESS DEFAULT ('地址不详') FOR STUADDRESS--示例5 检查CK约束（CHECK CONSTRAINT）-- 存在检查约束UQ_STUNO，则删除IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='CK_STUAGE' AND XTYPE='C')ALTER TABLE STUINFO DROP CONS
--修改表名
--格式：SP_RENAME TABLENAME,NEWTABLENAMESP_RENAME TABLENAME,NEWTABLENAME--只能对表，不能对临时表 --修改字段名--格式：SP_RENAME 'TABLENAME.COLNAME',NEWCOLNAME,'COLUMN'SP_RENAME 'TABLENAME.COLNAME',NEWCOLNAME,'COLUMN'
--添加字段
--格式：ALTER TABLE TABLE_NAME ADD NEW_COLUMN DATA_TYPE [INTERALITY_CODITION]示例1ALTER TABLE STUDENT ADD NATIONALITY VARCHAR(20)ALTER TABLE STUDENT ADD ID IDENTITY INT(1,1)--示例2 添加INT类型的列,默认值为 0
ALTER TABLE STUDENT ADD STUDENTNAME INT DEFAULT 0--示例3 添加INT类型的列,默认值为0，主键ALTER TABLE STUDENT ADD STUDENTID INT PRIMARY KEY DEFAULT 0 --示例4 判断STUDENT中是否存在NAME字段且删除字段IF EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID('STUDENT') AND NAME='NAME') BEGIN ALTER TABLE STUDENT DROP COLUMN NAME END 更改字段格式：ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAMEALTER TABLE STUDENT ALTER COLUMN NAME VARCHAR(200)删除字段格式：ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAMEALTER TABLE STUDENT DROP COLUMN NATIONALITY;查看字段约束格式： SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = TABLE_NAMESELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGEWHERE TABLE_NAME = 'STUDENT'查看字段缺省约束表达式 （即默认值等）格式：SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TABLE_NAMESELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='STUDENT'查看字段缺省约束名格式：SELECT NAME FROM SYSOBJECTS WHERE OBJECT_ID(TABLE_NAME)=PARENT_OBJ AND XTYPE=’D’SELECT NAME FROM SYSOBJECTSWHERE OBJECT_ID('表?名?')=PARENT_OBJ AND XTYPE='D'删除字段约束格式：ALTER TABLE TABLENAME DROP CONSTRAINT CONSTRAINTNAMEALTER TABLE STUDENT DROP CONSTRAINT PK__STUDENT__2F36BC5B772B9A0B添加字段约束格式：ALTER TABLE TABLENAME ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY (COLUMN_NAME)--示例1ALTER TABLE STUINFO ADD CONSTRAINT PK_STUNO PRIMARY KEY (STUNO)
--示例2 添加主键约束（PRIMARY KEY）
-- 存在主键约束PK_STUNO，则删除IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='PK_STUNO' AND XTYPE='PK') ALTER TABLE STUINFODROP CONSTRAINT PK_STUNOGO-- 重新添加主键约束PK_STUNOALTER TABLE STUINFO ADD CONSTRAINT PK_STUNO PRIMARY KEY (STUNO)GO--示例3 添加 唯一UQ约束（UNIQUE CONSTRAINT）
-- 存在唯一约束UQ_STUNO，则删除IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='UQ_STUID' AND XTYPE='UQ') ALTER TABLE STUINFODROP CONSTRAINT UQ_STUIDGO-- 重新添加唯一约束UQ_STUIDALTER TABLE STUINFO ADD CONSTRAINT UQ_STUID UNIQUE (STUID)--示例4 添加默认DF约束（DEFAULT CONSTRAINT）
-- 存在默认约束UQ_STUNO，则删除IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='DF_STUADDRESS' AND XTYPE='D') ALTER TABLE STUINFO DROP CONSTRAINT DF_STUADDRESSGO-- 重新添加默认约束DF_STUADDRESSALTER TABLE STUINFO ADD CONSTRAINT DF_STUADDRESS DEFAULT ('地址不详') FOR STUADDRESS--示例5 检查CK约束（CHECK CONSTRAINT）
-- 存在检查约束UQ_STUNO，则删除IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='CK_STUAGE' AND XTYPE='C') ALTER TABLE STUINFO DROP CONS