- Modify Table Name
Format: sp_rename tablename, newtablename
sp_rename tablename,newtablename
- Modify Field name
Format: sp_rename 'tablename. colname', newcolname, 'column'
sp_rename 'tablename.colname',newcolname,'column'
- Add Field
Format: alter table table_name add new_column data_type [interality_codition]
Example 1 alter table student Add nationality varchar (20) -- Example 2 add int type columns, default value: 0 alter table student Add studentName int default 0 -- Example 3 add int type columns, the default value is 0, primary key alter table student add studentId int primary key default 0 -- Example 4 check whether the name field exists in student and delete the field if exists (select * from syscolumns where id = object_id ('student ') and name = 'name') begin alter table student drop column name end
- Change Field
Format: alter table table_name alter column column_name
ALTER TABLE student ALTER COLUMN name VARCHAR(200)
- Delete Field
Format: alter table table_name drop column column_name
ALTER TABLE student DROP COLUMN nationality;
- View Field Constraints
Format: select * from information_schema.constraint_column_usage where TABLE_NAME = table_name
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM information_schema.CONSTRAINT_COLUMN_USAGEWHERE TABLE_NAME = 'student'
- View the default constraint expression of a field (that is, the default value, etc)
Format: select * from information_schema.columns where TABLE_NAME = table_name
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT FROM information_schema.COLUMNSWHERE TABLE_NAME='student'
- View the default constraint name of a field
Format: select name from sysobjects where object_id (table_name) = parent_obj and xtype = 'D'
Select name from sysobjectswhere object_id ('table? Name? ') = Parent_obj and xtype = 'D'
- Delete field constraints
Format: alter table tablename drop constraint constraintname
ALTER TABLE student DROP CONSTRAINT PK__student__2F36BC5B772B9A0B
- Add field constraints
Format: alter table tablename add constraint constraintname primary key (column_name)
-- Example 1 alter table stuInfo add constraint PK_stuNo primary key (stuNo) -- Example 2 ADD Primary Key CONSTRAINT (primary key) -- Primary Key CONSTRAINT PK_stuNO exists, delete if exists (SELECT * FROM sysobjects WHERE name = 'pk _ stuNo 'and xtype = 'pk ') alter TABLE stuInfoDrop Constraint PK_stuNoGo -- Re-add primary key constraint PK_stuNOALTER TABLE stuInfo ADD Constraint PK_stuNo primary key (stuNo) Go -- Example 3 ADD Unique UQ Constraint (Unique CONSTRAINT) -- Unique Constraint exists, delete if exists (SELECT * FROM sysobjects WHERE name = 'uq _ stuid' and xtype = 'uq ') alter TABLE stuInfoDrop Constraint UQ_stuIDGo -- Re-ADD the UNIQUE UQ_stuIDALTER TABLE stuInfo add constraint UQ_stuID UNIQUE (stuID) -- Example 4 ADD Default DF Constraint (Default Constraint) -- UQ_stuNO, delete if exists (SELECT * FROM sysobjects WHERE name = 'df _ stuaddress' and xtype = 'D ') alter TABLE stuInfo Drop Constraint DF_stuAddressGo -- ADD the default constraint DF_stuAddressALTER TABLE stuInfo ADD Constraint DF_stuAddress DEFAULT ('address unknown ') FOR stuAddress -- Example 5 Check CK constraints (Check Constraint) -- if exists (SELECT * FROM sysobjects WHERE name = 'ck _ stuage' and xtype = 'C') Alter TABLE stuInfo Drop Cons