SQL Server常见对表的操作

☻给数据库表添加字段

ALTER TABLE dbo.TrainApply ADD IsPassFirstCheck INT NOT NULL DEFAULT 0 --默认o:未处理，1审核通过，2审核未通过

ALTER TABLE dbo.TrainApply ADD FirstCheckUserID NVARCHAR(50)

ALTER TABLE dbo.TrainApply ADD FirstCheckTime DATETIME

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻删除表中某些字段

ALTER TABLE dbo.TrainApply DROP COLUMN FirstCheckUserID

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻删除表中主键

删除表中已经存在的主键： 

alter table 表名 drop constraint 主键名(不是字段名) 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻创建表

use StuDB

go

if exists(select * from sysobjects where name='stuMarks')

drop table stuMarks

create table stuMarks

(

ExamNo int identity(1,1) primary key,

stuNo char(6) not null,

writtenExam int not null,

LabExam int not null

)

Go

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//修改表字段的类型

ALTER TABLE tablename ALTER COLUMN colname newDataType

比如：ALTER TABLE mytable ALTER COLUMN mycol1 INT DEFAULT 0

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//修改表字段长度

Alter Table zxchem_Suggest Alter Column Yj_Finish_Date Varchar(10) Not Null --修改字段长度

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//修改表的字段名

--test_tab 表的 val字段，修改为 val2 字段。

--不过简单的写，就是

EXECUTE sp_rename N'dbo.test_tab.val', N'val2', 'COLUMN'

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻跨服务器导入数据

INSERT INTO DB_Test.dbo.tb1 SELECT * FROM OPENROWSET('SQLOLEDB','192.168.0.7';'sa';'123456',DB_Test.dbo.Users_Detail)

WHERE CONVERT(CHAR(10),date,10)=CONVERT(CHAR(10),GETDATE()-1,10)

源文档 <http://www.cnblogs.com/andy_tigger/archive/2010/06/03/1750856.html>