dstr_fd.sql

用友U8财务软件VB源程序, 本版本为2002年版本

/*=============================================================*/
/* Database name:  U8资金管理8.50升级脚本                       */
/* DBMS name:      Microsoft SQL Server 2000                   */
/* Created on:     2002-4-28 11:12:05                          */
/* Created by:     章景峰                                      */
/*=============================================================*/


if not exists(select 1 from systypes where name='fd_udt_digest')
  exec sp_addtype fd_udt_digest, 'nvarchar(255)'
go


if not exists(select 1 from systypes where name='fd_udt_id')
  exec sp_addtype fd_udt_id, 'char(15)' , 'not null'
go


if not exists(select 1 from systypes where name='fd_udt_username')
  exec sp_addtype fd_udt_username, 'nvarchar(20)'
go


if exists (select 1 from sysobjects where id = object_id('fd_cadset') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_cadset'),'cad_id','AllowsNull') is null
	begin	
	IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'AddDateDfltcad' AND type = 'D')
	begin
	exec sp_unbindefault 'fd_cadset.cad_id'
	drop default AddDateDfltcad
	end
	alter table fd_cadset add cad_id fd_udt_id not null constraint AddDateDfltcad DEFAULT '000000000000000'
	alter table fd_cadset drop constraint AddDateDfltcad
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_cadset') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_cadset'),'cad_id','AllowsNull') = 0
	if exists (select 1 from fd_cadset where cad_id = '000000000000000')
	begin
	update fd_cadset set cad_id =830000000000000 + iid
	update fd_cadset set cad_id =stuff(cad_id,1,1,'0')
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_cadset') and type = 'U') and COL_LENGTH('fd_cadset','cCadID') < 50
	begin
	alter table fd_cadset drop constraint aaaaaFD_CadSet_PK
	alter table fd_cadset alter column cCadID nvarchar(50) not null
	alter table fd_cadset alter column cMark fd_udt_digest null
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_cadset') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_cadset'),'cad_id','AllowsNull') = 0 and not exists (select 1 from  sysindexes where  id    = object_id('fd_cadset') and  name  = 'PK_FD_CADSET' and   indid > 0   and   indid < 255)
	alter table fd_cadset add constraint PK_FD_CADSET primary key ([cad_id]) on 'default'
go


if not exists (select 1 from sysobjects where id = object_id('fd_cadset') and type = 'U')
	create table fd_cadset (
	cad_id               fd_udt_id            not null,
	cCadID               nvarchar(50)         not null,
	iMonth               smallint             not null,
	iDelay               smallint             null,
	cMark                fd_udt_digest        null,
	constraint PK_FD_CADSET primary key  (cad_id)
	)
go


if not exists (select 1 from  sysindexes where id = object_id('fd_cadset') and name = 'aaaaaFD_CadSet_PK' and indid > 0 and indid < 255)
	create unique  index aaaaaFD_CadSet_PK on fd_cadset (
	CcadID
)
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('fd_cadsets')
            and   name  = 'ix_fd_cadset_b'
            and   indid > 0
            and   indid < 255)
   drop index fd_cadsets.ix_fd_cadset_b
go



if exists (select 1 from sysobjects where id = object_id('fd_cadsets') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_cadsets'),'cad_b_id','AllowsNull') is null
	begin
	IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'AddDateDfltcad_b_id' AND type = 'D')
	begin
	exec sp_unbindefault 'fd_cadsets.cad_b_id'
	drop default AddDateDfltcad_b_id
	end
	alter table fd_cadsets add cad_b_id fd_udt_id not null constraint AddDateDfltcad_b_id DEFAULT '000000000000000'
	alter table fd_cadsets drop constraint AddDateDfltcad_b_id
	IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'AddDateDfltcad_id' AND type = 'D')
	begin
	exec sp_unbindefault 'fd_cadsets.cad_id'
	drop default AddDateDfltcad_id
	end
	alter table fd_cadsets add cad_id fd_udt_id not null constraint AddDateDfltcad_id DEFAULT '000000000000000'	
	alter table fd_cadsets drop constraint AddDateDfltcad_id
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_cadsets') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_cadsets'),'cad_b_id','AllowsNull') = 0
	if exists (select 1 from fd_cadsets where cad_b_id = '000000000000000')
	begin
	update fd_cadsets set cad_b_id =920000000000000 + iid
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_cadsets') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_cadsets'),'cad_id','AllowsNull') = 0
	if exists (select 1 from fd_cadsets where cad_id = '000000000000000')
	begin
	update fd_cadsets set cad_id =fd_cadset.cad_id from fd_cadset,fd_cadsets where fd_cadsets.cCadID=fd_cadset.cCadID
	end	
go


if exists (select 1 from sysobjects where id = object_id('fd_cadsets') and type = 'U') and COL_LENGTH('fd_cadsets','cCadID') < 50
	begin
	drop index fd_cadsets.ID_DATE
	alter table fd_cadsets drop constraint aaaaaFD_CadSets_PK
	alter table fd_cadsets alter column cCadID nvarchar(50) null
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_cadsets') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_cadsets'),'cad_b_id','AllowsNull') = 0 and not exists (select 1 from  sysindexes where  id    = object_id('fd_cadsets') and  name  = 'PK_FD_CADSETS' and   indid > 0   and   indid < 255)
	alter table fd_cadsets add constraint PK_FD_CADSETS primary key ([cad_b_id]) on 'default'
go


if not exists (select 1 from  sysobjects where  id = object_id('fd_cadsets') and type = 'U')
	create table fd_cadsets (
	cad_b_id             fd_udt_id            not null,
	cad_id               fd_udt_id            not null,
	DclosDate            datetime             not null,
	cCadID               nvarchar(50)         null,
	constraint PK_FD_CADSETS primary key  (cad_b_id)
	)
go


create unique  index ix_fd_cadset_b on fd_cadsets (
cad_id,
DclosDate
)
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('fd_intra')
            and   name  = 'ix_fd_irate'
            and   indid > 0
            and   indid < 255)
   drop index fd_intra.ix_fd_irate
go


if exists (select 1 from sysobjects where id = object_id('fd_intra') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_intra'),'irate_id','AllowsNull') is null
	begin
	IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'AddDateDfltirate' AND type = 'D')
	begin
	exec sp_unbindefault 'fd_intra.irate_id'
	drop default AddDateDfltirate
	end
	alter table fd_intra add irate_id fd_udt_id not null constraint AddDateDfltirate DEFAULT '000000000000000'
	alter table fd_intra drop constraint AddDateDfltirate
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_intra') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_intra'),'irate_id','AllowsNull') = 0
	if exists (select 1 from fd_intra where irate_id = '000000000000000')
	begin
	update fd_intra set irate_id =880000000000000 + iid
	update fd_intra set irate_id =stuff(irate_id,1,1,'0')
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_intra') and type = 'U') and COL_LENGTH('fd_intra','cIntrID') < 50
	begin
	alter table fd_intra drop constraint aaaaaFD_Intra_PK
	alter table fd_intra alter column cIntrID nvarchar(50) not null
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_intra') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_intra'),'irate_id','AllowsNull') = 0 and not exists (select 1 from  sysindexes where  id    = object_id('fd_intra') and  name  = 'PK_FD_INTRA' and   indid > 0   and   indid < 255)
	alter table fd_intra add constraint PK_FD_INTRA primary key ([irate_id]) on 'default'
go


if not exists (select 1 from  sysobjects where  id = object_id('fd_intra') and   type = 'U')
	create table fd_intra (
	irate_id             fd_udt_id            not null,
	cIntrID              nvarchar(50)         not null,
	constraint PK_FD_INTRA primary key  (irate_id)
	)
go


create unique  index ix_fd_irate on fd_intra (
CIntrID
)
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('fd_intras')
            and   name  = 'ix_fd_irate_b'
            and   indid > 0
            and   indid < 255)
   drop index fd_intras.ix_fd_irate_b
go


if exists (select 1 from sysobjects where id = object_id('fd_intras') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_intras'),'irate_b_id','AllowsNull') is null
	begin
	IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'AddDateDfltirate_b_id' AND type = 'D')
	begin
	exec sp_unbindefault 'fd_intras.irate_b_id'
	drop default AddDateDfltirate_b_id
	end
	alter table fd_intras add irate_b_id fd_udt_id not null constraint AddDateDfltirate_b_id DEFAULT '000000000000000'
	alter table fd_intras drop constraint AddDateDfltirate_b_id
	IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'AddDateDfltirate_id' AND type = 'D')
	begin
	exec sp_unbindefault 'fd_intras.irate_id'
	drop default AddDateDfltirate_id
	end
	alter table fd_intras add irate_id fd_udt_id not null constraint AddDateDfltirate_id DEFAULT '000000000000000'	
	alter table fd_intras drop constraint AddDateDfltirate_id
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_intras') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_intras'),'irate_b_id','AllowsNull') = 0
	if exists (select 1 from fd_intras where irate_b_id = '000000000000000')
	update fd_intras set irate_b_id =970000000000000 + iid
go


if exists (select 1 from sysobjects where id = object_id('fd_intras') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_intras'),'irate_id','AllowsNull') = 0
	if exists (select 1 from fd_intras where irate_id = '000000000000000')
	begin
	update fd_intras set irate_id =fd_intra.irate_id from fd_intras,fd_intra where fd_intras.cIntrID=fd_intra.cIntrID
	end	
go


if exists (select 1 from sysobjects where id = object_id('fd_intras') and type = 'U') and COL_LENGTH('fd_intras','cIntrID') < 50
	begin
	drop index fd_intras.cIntrID
	drop index fd_intras.indCidDate
	alter table fd_intras drop constraint aaaaaFD_Intras_PK
	alter table fd_intras alter column cIntrID nvarchar(50) null
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_intras') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_intras'),'irate_b_id','AllowsNull') = 0 and not exists (select 1 from  sysindexes where  id    = object_id('fd_intras') and  name  = 'PK_FD_INTRAS' and   indid > 0   and   indid < 255)
	alter table fd_intras add constraint PK_FD_INTRAS primary key ([irate_b_id]) on 'default'
go


if not exists (select 1 from  sysobjects where  id = object_id('fd_intras') and type = 'U')
	create table fd_intras (
	irate_b_id           fd_udt_id            not null,
	irate_id             fd_udt_id            not null,
	cIntrID              nvarchar(50)         null,
	Dbdate               datetime             not null,
	nzy                  float                not null,
	ncq                  float                null,
	nyq                  float                null,
	Bde                  bit                  null,
	Nde                  money                null,
	Ncdell               float                null,
	constraint PK_FD_INTRAS primary key  (irate_b_id)
	)
go


create unique  index ix_fd_irate_b on fd_intras (
irate_id,
Dbdate
)
go


declare @FKeyName varchar(200)
set @FKeyName=(select name from  sysobjects where  type = 'F' and name like 'FK__FD_AccDef__cUnit%')
set @FKeyName='alter table fd_accdef drop constraint ' + @FKeyName
exec (@FKeyName)
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('fd_accunit')
            and   name  = 'ix_fd_accunit_id'
            and   indid > 0
            and   indid < 255)
   drop index fd_accunit.ix_fd_accunit_id
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('fd_accunit')
            and   name  = 'ix_fd_accunit_name'
            and   indid > 0
            and   indid < 255)
   drop index fd_accunit.ix_fd_accunit_name
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('fd_accunit')
            and   name  = 'ix_fd_accunit_type_code'