Add Field
Copy codeCode: alter table docdsp add dspcode char (200)
Delete FieldCopy codeThe Code is as follows: alter table table_NAME drop column column_NAME
Modify Field TypeCopy codeThe Code is as follows: alter table table_name alter column column_name new_data_type
Rename
Sp_rename
Change the name of the user-created object (such as a table, column, or user-defined data type) in the current database.
SyntaxCopy codeThe Code is as follows: sp_rename [@ objname =] 'object _ name ',
[@ Newname =] 'new _ name'
[, [@ Objtype =] 'object _ type']
-- Assume that the table to be processed is named tb.
-- Determines whether the table to be added has a primary key.
If exists (select 1 from sysobjects where parent_obj = object_id ('tb') and xtype = 'pk ')
Begin
Print 'The table already has a primary key, and the column can only be added as a normal column'
-- Add an int column. The default value is 0.
Alter table tb add column name int default 0
End
Else
Begin
Print 'table with no primary key, add primary key column'
-- Add an int column. The default value is 0.
Alter table tb add column name int primary key default 0
End
/*************************************** **************************************** *******/
Determine whether the name field exists in table1Copy codeThe Code is as follows: if exists (select * from syscolumns where id = object_id ('table1') and name = 'name') begin
Select * from people;
End