--以下为考勤
CREATE TABLE xiangmu (xinzhong int NULL,mingcheng varchar (20) NULL,leibie tinyint NULL,xiaoshuwei tinyintNULL,IsJiben tinyint NULL,chuli tinyint NULL,xiangmudaima int NULL)
if not Exists (Select * from xiangmu where xinzhong=1 and mingcheng='件工工资') begin insert into xiangmu(xinzhong,mingcheng,leibie,xiaoshuwei,IsJiben,chuli,xiangmudaima)values(1,'件工工资',2,2,1,0,2) end
if not Exists (Select * from xiangmu where xinzhong=2 and mingcheng='件工工资') begin insert into xiangmu(xinzhong,mingcheng,leibie,xiaoshuwei,IsJiben,chuli,xiangmudaima)values(2,'件工工资',2,2,1,1,2) end
if not Exists (Select * from xiangmu where xinzhong=1 and mingcheng='时工工资') begin insert into xiangmu(xinzhong,mingcheng,leibie,xiaoshuwei,IsJiben,chuli,xiangmudaima)values(1,'时工工资',2,2,1,1,3) end
if not Exists (Select * from xiangmu where xinzhong=2 and mingcheng='时工工资') begin insert into xiangmu(xinzhong,mingcheng,leibie,xiaoshuwei,IsJiben,chuli,xiangmudaima)values(2,'时工工资',2,2,1,1,3) end
Drop TABLE CaFeiTMP
Drop Table chachuangriqi

Alter Table caozuolog add Name varchar(600) not null default ''
Alter Table caozuolog add xtdatetime datetime not null default getdate()
Alter Table caozuolog Alter Column Name varchar(600) not null 
ALTER TABLE caozuolog add DEFAULT ('') for Name WITH VALUES
Alter Table caozuolog Drop Column dingdan
Alter Table caozuolog Drop Column dingdanhao
Alter Table caozuolog Drop Column kuanhao
Alter Table caozuolog Drop Column changhao
Alter Table caozuolog Drop Column czy
Alter Table caozuolog Drop Column cpname
Alter Table caozuolog Drop Column riqi
Alter Table caozuolog Drop Column fj1
Alter Table caozuolog Drop Column fj2


--删除caozuolog表的主键--
declare @PK varchar(100) Select @PK=name from sysobjects where xtype='PK' and parent_obj=object_id('caozuolog') Exec('alter table caozuolog drop CONSTRAINT '+@pk) 

--以下为以前更新时char转varchar后的空格--
update CodeTable set FieldName=REPLACE(FieldName,' ','')                
update CodeTable set InputCode=REPLACE(InputCode,' ','')   

update CONF set BACKUPPATH=REPLACE(BACKUPPATH,' ','')                   
update CONF set WPGonghao=REPLACE(WPGonghao,' ','') 

update Department set DEPT_NAME=REPLACE(DEPT_NAME,' ','')

update DINGDAN set DINGDANHAO=REPLACE(DINGDANHAO,' ','')                                  
update DINGDAN set BEIZHU=REPLACE(BEIZHU,' ','')                    
update DINGDAN set zhiyang=REPLACE(zhiyang,' ','')                   
update DINGDAN set kekuan=REPLACE(kekuan,' ','')                    
update DINGDAN set kehu=REPLACE(kehu,' ','')                      
update DINGDAN set buzhong=REPLACE(buzhong,' ','')                   
update DINGDAN set kuanshi=REPLACE(kuanshi,' ','')                   
update DINGDAN set zubie=REPLACE(zubie,' ','')

update DINGDANMINGXI set DINGDANHAO=REPLACE(DINGDANHAO,' ','')          
update DINGDANMINGXI set SEHAO=REPLACE(SEHAO,' ','')                    
update DINGDANMINGXI set CHIMA=REPLACE(CHIMA,' ','') 

update employee set emp_no=REPLACE(emp_no,' ','')                       
update employee set emp_name=REPLACE(emp_name,' ','')                   
update employee set Job=REPLACE(Job,' ','') 

update EmployeeAddin set Emp_No=REPLACE(Emp_No,' ','') 

update fasttiaoma set dingdanhao=REPLACE(dingdanhao,' ','') 

update FCut set dingdanhao=REPLACE(dingdanhao,' ','')                   
update FCut set zu=REPLACE(zu,' ','')                                   
update FCut set zhuyishixiang=REPLACE(zhuyishixiang,' ','')             
update FCut set buban=REPLACE(buban,' ','') 

update GCut set mingcheng=REPLACE(mingcheng,' ','')                     
update GCut set gonghao=REPLACE(gonghao,' ','') 

update GONGHAOTABLE set GONGHAO=REPLACE(GONGHAO,' ','') 

update gonghaotableadmin set gonghao=REPLACE(gonghao,' ','')            
              
update gonghaotemp set gonghao=REPLACE(gonghao,' ','')                  
           
update gongxubiao set gongxuming=REPLACE(gongxuming,' ','') 

update gongxuleixing set xuhao=REPLACE(xuhao,' ','')                    
update gongxuleixing set mingcheng=REPLACE(mingcheng,' ','') 

update gz set gonghao=REPLACE(gonghao,' ','')

update JIANGONGGZ set GONGHAO=REPLACE(GONGHAO,' ','')  

update KOULING set YONGHUMING=REPLACE(YONGHUMING,' ','')                
update KOULING set MIMA=REPLACE(MIMA,' ','')                            
update KOULING set xingming=REPLACE(xingming,' ','') 

update kuanhaoriqi set kuanhao=REPLACE(kuanhao,' ','')                  
update kuanhaoriqi set caozuoyuan=REPLACE(caozuoyuan,' ','')

update Leaveemployee set emp_no=REPLACE(emp_no,' ','')                  
update Leaveemployee set emp_name=REPLACE(emp_name,' ','')              
update Leaveemployee set Job=REPLACE(Job,' ','')                        
update Leaveemployee set LeaveReason=REPLACE(LeaveReason,' ','') 

update PCut set kuanhao=REPLACE(kuanhao,' ','') 

update scap set dingdanhao=REPLACE(dingdanhao,' ','')                   
update scap set kuanhao=REPLACE(kuanhao,' ','')                         
update scap set sehao=REPLACE(sehao,' ','')                             
update scap set chima=REPLACE(chima,' ','')                             
update scap set zpbumen=REPLACE(zpbumen,' ','')  

update shigonggz set gonghao=REPLACE(gonghao,' ','')

update STYLE set KUANHAO=REPLACE(KUANHAO,' ','')                        
update STYLE set GONGXUMING=REPLACE(GONGXUMING,' ','')                  
update STYLE set qhfu=REPLACE(qhfu,' ','')                              
update STYLE set bumen=REPLACE(bumen,' ','')  

update stylepic set kuanhao=REPLACE(kuanhao,' ','')  

update stylepic set kuanhao=REPLACE(kuanhao,' ','')  

update xiangmu set mingcheng=REPLACE(mingcheng,' ','') 

update YUEBAOBIAO set DINGDAN=REPLACE(DINGDAN,' ','')                   
update YUEBAOBIAO set KUANHAO=REPLACE(KUANHAO,' ','')                   
update YUEBAOBIAO set GONGHAO=REPLACE(GONGHAO,' ','') 

update zdybaobiao set bname=REPLACE(bname,' ','')

--以下是2011-04-23的更新--
Alter Table ChiMaTMP add Row int not null default 0 

--以下是2011-04-26更新---
drop Table gongxuleixing
drop Table gongxubiao
Create Table GongXuMing (xuhao varchar(10) not null DEFAULT '',gongxuming varchar(60) not null DEFAULT '')


--以下是2011-04-30更新--
Alter Table STYLE Drop Column qhfu

--以下是2011-05-15更新--
drop Table zdybaobiao

--以下是2011-05-15更新--
update KOULING set YONGHUMING='Admin',xingming='管理员' where YONGHUMING='admin'                                                                                                        

--以下是2011-06-19更新--
CREATE TABLE Units (FLCode varchar(25) NULL,FLGroup varchar(25) NULL,LB int NULL,YN bit NULL ,UnitCode varchar(10) NULL,UnitName varchar(40) NULL,Relation varchar(10) NULL,Telephone varchar(20) NULL,Address varchar(60) NULL,EMail varchar(40) NULL,Facsimile varchar(20) NULL,Bank varchar(40) NULL,Account varchar(40) DEFAULT '',Note varchar(256) NULL,Price numeric(18,4) NULL,ABC VARCHAR(40) null)
if not Exists (Select * from Units where FLCode='00000' and LB=0 and YN=1) begin insert into Units(FLCode,FLGroup,LB,YN)values('00000','','0','1') end    

--以下是2011-08-11更新--
Alter Table Units add ABC VARCHAR(40) null

--以下是2011-08-12更新--
Alter Table DINGDAN add CHRQ datetime null

--以下是2011-09-01更新--
Alter Table DINGDAN add CaoZuoYuan varchar(12) NULL   --修改类型
Alter Table FCut add CaoZuoYuan varchar(12) NULL

--以下是2011-09-28更新--
Alter Table CONF Drop Column DelGongHao
Alter Table CONF Drop Column PRINTLINE
Alter Table CONF Drop Column PRINTJUSTIFY
Alter Table CONF Drop Column BACKUPPATH
Alter Table CONF Drop Column WPGonghao
Alter Table CONF Drop Column zongchuangciflag
Alter Table CONF Drop Column Code
Alter Table CONF Drop Column gonghaofangshi
Alter Table CONF Drop Column gonghaoabc
Alter Table CONF Drop Column gonghaoweishu
drop Table setupoption

--以下是2011-09-29更新--
Create Table DelGongHao (DelGongHao varchar(10) null,DelName varchar(10) null,DelDateTime datetime not null DEFAULT getdate(),SaveGongHao varchar(10) null)ll)
Alter Table EmployeeAddin Alter Column Emp_No varchar(10) null 


--以下是2012-03-18更新--
Create Table Report (Code int not null identity(1,1),Configured varchar(40) null,ReportJM varchar(40) null,ReportName varchar(40) null,ReportFile image null)

--以下是2012-05-06更新--
alter table KOULING alter column YONGHUMING varchar(20)   --修改类型
alter table KOULING alter column MIMA varchar(20)  
alter table KOULING alter column xingming varchar(20) 

Alter Table KOULING Drop Column quanxian0
Alter Table KOULING Drop Column quanxian1
Alter Table KOULING Drop Column quanxian2
Alter Table KOULING Drop Column quanxian3
Alter Table KOULING Drop Column quanxian4
Alter Table KOULING Drop Column quanxian5
Alter Table KOULING Drop Column quanxian6
Alter Table KOULING Drop Column quanxian7
Alter Table KOULING ADD Permission varchar(350) not null default ''

--以下是2012-05-06更新--
CREATE TABLE CONF1(Configured VARCHAR(100) NOT NULL default '') 
DECLARE @S varchar(100) SET @S=Right(CONVERT(int,(SELECT TIAOMA FROM CONF))+10000000000,10) SET @S=@S+CONVERT(VARCHAR(4),(SELECT YUERIQI FROM CONF),120) SET @S=@S+Right(DATEPART(mm,'20120201')+100,2) SET @S=@S+'000000000000000000000000000000000000000000000000000000000000000000000000000000000000' INSERT INTO CONF1(Configured)VALUES(@S)
DROP TABLE CONF 
CREATE TABLE CONF(Configured VARCHAR(100) NOT NULL default '') 
DECLARE @S varchar(100) SET @S=(SELECT Configured FROM CONF1) INSERT INTO CONF(Configured)VALUES(@S)
DROP TABLE CONF1
IF (SELECT COUNT(*) FROM CONF)=0 Insert into  CONF (Configured)VALUES('0000001000201201000000000000000000000000000000000000000000000000000000000000000000000000000000000000')
--以下这句升级到2014-12-08版,CONF增加TiaoMaHead执行不了.解决爱凡卡问题--
IF (SELECT COUNT(*) FROM CONF)=1 IF (SELECT TOP 1 * FROM CONF)='' UPDATE CONF SET Configured='0000001000201201000000000000000000000000000000000000000000000000000000000000000000000000000000000000'

--以下是2012-07-26更新--
if not Exists (Select * From SysObjects Where xtype='U' And Name='ZQBACKUP')begin CREATE TABLE ZQBACKUP(Code int NOT NULL DEFAULT 0,DBName varchar(40) NOT NULL DEFAULT '',DDate datetime NOT NULL DEFAULT getdate(),FileName varchar(40) NOT NULL DEFAULT '') end
if not Exists (Select * From SysObjects Where xtype='U' And Name='ClientBFHF')begin CREATE TABLE ClientBFHF(Code int NOT NULL DEFAULT 0,BFHF varchar(40) NOT NULL DEFAULT '',FileName varchar(50) NOT NULL DEFAULT '',TiShi varchar(40) NOT NULL DEFAULT '',YaSuoFile image NOT NULL DEFAULT '') end


--以下是2012-07-30更新-----大更新
Alter Table STYLE Drop Column heding
Alter Table STYLE Drop Column bumen

--删除默认值
declare @name varchar(20)  select @name=b.name from syscolumns a,sysobjects b where a.id=object_id('STYLE') and b.id=a.cdefault and a.name='fudongbili' and b.name like 'DF%'  exec('alter table STYLE drop constraint '+@name) 
alter table STYLE alter column fudongbili numeric(18,4) null    --修改类型

alter table STYLE alter column fangongbili numeric(18,4) null   --修改类型
alter table kuanhaoriqi alter column heding bit null         --修改类型

Alter Table kuanhaoriqi ADD CaiJia numeric(18,4) NULL
UPDATE kuanhaoriqi SET CaiJia=A.CaiJia FROM PCut A WHERE kuanhaoriqi.Kuanhao=A.Kuanhao
DROP TABLE PCut

EXEC sp_rename 'stylepic','KuanHaoPic' --重命名表名 
alter table KuanHaoPic alter column kuanhao varchar(20) null   --修改类型
Exec sp_rename 'KuanHaoPic.pic','Picture','column'  --修改字段名称
Alter Table KuanHaoPic ADD Extension varchar(20)
UPDATE KuanHaoPic SET Extension='.jpg' WHERE Picture is not null

EXEC sp_rename 'kuanhaoriqi','KuanHao' --重命名表名 

Alter Table kuanhao ADD XiuGaiRiQi datetime 

--修改订单号款号长度为20
--alter table DINGDAN alter column DINGDANHAO varchar(20)  --这名不执行，需手动修改
alter table DINGDAN alter column KUANHAO varchar(20)
alter table DINGDANMINGXI alter column DINGDANHAO varchar(20)
alter table kuanhao alter column KUANHAO varchar(20)
alter table STYLE alter column KUANHAO varchar(20)
alter table FCut alter column dingdanhao varchar(20)

--以下修改dingdanmingxi  将COL-》ROW_S    ROW-》COL_S
Exec sp_rename 'dingdanmingxi.ROW','COL_S','column' 
Exec sp_rename 'dingdanmingxi.COL','ROW_S','column' 

Exec sp_rename 'DINGDAN.jiner','CBDJ','column'  --修改字段名称
alter table DINGDAN alter column CBDJ numeric(18,4) null         --修改类型--成品单价 

--2012-08-02新增条码头
Alter Table DINGDAN ADD TiaoMaHead INT       

UPDATE DINGDAN SET TiaoMaHead=A.TiaoMa FROM TiaoMaHead A WHERE DINGDAN.DINGDANHAO=A.DingDanHao

DROP TABLE TiaoMaHead


--删除C表的 TiaoMa ,BuChang, YuBu
--Alter Table C003 Drop Column TiaoMa ,BuChang, YuBu
--服务器: 消息 5074，级别 16，状态 1，行 1
--对象 'DF__C003__TiaoMa__1768EED4' 依赖于 列 'TiaoMa'。
--服务器: 消息 4922，级别 16，状态 1，行 1
--ALTER TABLE DROP COLUMN TiaoMa 失败，因为有一个或多个对象访问此列。

--以下是新增裁单增加整床缸号
Alter Table FCut Drop Column CutMode
Exec sp_rename 'FCut.buban','ZDGangHao','column'

--修改C表ROW-》COL_S      COL-》ROW_S    未整

--2012-08-06修改已经打印到FCUT
Alter Table FCUT ADD Printed BIT NULL
UPDATE FCUT SET Printed=1 FROM (SELECT * FROM PR) B WHERE FCUT.DINGDANHAO=B.DINGDAN AND FCUT.CHUANGHAO=B.CHUANGCI
DROP TABLE PR

--2012-08-07删除工价2和工价3，修改价格为numeric(18,4)
Exec sp_rename 'STYLE.JIAGE','JiaGe1','column'  --修改字段名称
alter table STYLE alter column JiaGe1 numeric(18,4) NULL
Alter Table STYLE ADD jiage2 numeric(18,4) NULL
Alter Table STYLE ADD jiage3 numeric(18,4) NULL

--删除T表JiaGeFlag,JiaGe,seHao,ChiMa,FuDong,Flag              未整
--修改T表ShuLiang为numeric(18,4)         _T表ShuLiang:=ShuLiang/10    未整

alter table DINGDAN alter column WANCHENG BIT   --修改类型

Alter Table hidedate Drop Column flag


DROP TABLE CaFeiTMP
DROP TABLE YUEBAOBIAO
Alter Table kuanhao ADD SHGongXu int null
Alter Table kuanhao ADD Code int null
alter table KuanHao alter column CaiJia numeric(18,4) null   --修改类型

DELETE FROM REPORT WHERE Left(ReportJM,3)<>'YF_'      --删除其它报表













