createflowtabletobackdbasyear.sql

SQL方面值得学习,希望大家发表意见,多上传好文章,谢谢

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkFlowInstance1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WorkFlowInstance1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkTaskInstance1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WorkTaskInstance1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkTaskAnnex1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WorkTaskAnnex1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OperateInstance1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OperateInstance1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OperateInstanceTemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OperateInstanceTemp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskRecRelation1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TaskRecRelation1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[附件信息]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[附件信息]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SysLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SysLog]
GO
CREATE TABLE [dbo].[WorkFlowInstance1] (
	[Guid] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[AncestorId] [varchar] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[FatherId] [char] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[SampleId] [char] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[SampleFlowName] [varchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
	[TypeId] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
	[TypeName] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
	[Name] [varchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Nbr] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
	[OrderNbr] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
	[Des] [varchar] (512) COLLATE Chinese_PRC_CI_AS NULL ,
	[Priority] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[EndTime] [datetime] NULL ,
	[TimeLimit] [int] NULL ,
	[Status] [int] NULL ,
	[OverTimeStatus] [int] NULL ,
	[AppointTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Task] [varchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
	[RegionNbr] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[Version] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
	[CustomerLevel] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[Evaluation] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[ChildFlowStartTask] [char] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[StartTime] [datetime] NULL ,
	[RequiredEndTime] [datetime] NULL ,
	[OverTime] [datetime] NULL ,
	[SuspendDateTime] [datetime] NULL ,
	[SuspendTotalSeconds] [int] NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkTaskInstance1] (
	[Guid] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[SampleId] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[WorkFlowInstanceId] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[WorkFlowName] [varchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
	[OperType] [int] NULL ,
	[Name] [varchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[SendingTime] [datetime] NULL ,
	[StartTime] [datetime] NULL ,
	[EndTime] [datetime] NULL ,
	[Status] [int] NULL ,
	[OverTimeStatus] [int] NULL ,
	[OverTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[OverTimeReason] [int] NULL ,
	[RegionNbr] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[Version] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
	[LockPointer] [varchar] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[CollecterId] [varchar] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[IfNoClaimAppoint] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
	[NextTaskArchitId] [varchar] (512) COLLATE Chinese_PRC_CI_AS NULL ,
	[PreviousTaskGuid] [varchar] (512) COLLATE Chinese_PRC_CI_AS NULL ,
	[OperateId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Decs] [varchar] (512) COLLATE Chinese_PRC_CI_AS NULL ,
	[IsPagePurview] [smallint] NULL ,
	[SuspendDateTime] [datetime] NULL ,
	[SuspendTotalSeconds] [int] NULL ,
	[SnatChatStatus] [int] NOT NULL ,
	[CommandName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ProcessNotes] [varchar] (512) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[AssignNotes] [varchar] (512) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkTaskAnnex1] (
	[Guid] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[taskId] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[NextActionGuid] [varchar] (512) COLLATE Chinese_PRC_CI_AS NULL ,
	[UIId] [char] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[UIName] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
	[UILocation] [varchar] (1024) COLLATE Chinese_PRC_CI_AS NULL ,
	[UIViewId] [char] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[UIViewName] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
	[UIViewLocation] [varchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
	[FunSrcOperName] [varchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
	[FunSrcOperId] [varchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
	[FlowId] [char] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[ViewType] [smallint] NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OperateInstance1] (
	[Guid] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[TaskId] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[OperateType] [smallint] NOT NULL ,
	[Type] [smallint] NOT NULL ,
	[Name] [varchar] (256) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[UserId] [varchar] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[OperDataTime] [datetime] NULL ,
	[DescInfo] [varchar] (1024) COLLATE Chinese_PRC_CI_AS NULL ,
	[OverTimeStatus] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Architecture] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ArchitectureId] [char] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[FlowId] [char] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[CreateDateTime] [datetime] NULL ,
	[UserName] [varchar] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[DisplayName] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
	[Phone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[SelectType] [smallint] NULL ,
	[DisplayOper] [varchar] (36) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OperateInstanceTemp] (
	[Guid] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[TaskId] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[OperateType] [smallint] NOT NULL ,
	[Type] [smallint] NOT NULL ,
	[Name] [varchar] (256) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[UserId] [varchar] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[OperDataTime] [datetime] NULL ,
	[DescInfo] [varchar] (1024) COLLATE Chinese_PRC_CI_AS NULL ,
	[OverTimeStatus] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Architecture] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ArchitectureId] [char] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[FlowId] [char] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[CreateDateTime] [datetime] NULL ,
	[UserName] [varchar] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[DisplayName] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
	[Phone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[SelectType] [smallint] NULL ,
	[DisplayOper] [varchar] (36) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TaskRecRelation1] (
	[Guid] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[DataTable] [varchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RecId] [char] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[TaskId] [char] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[TaskName] [varchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
	[CreateDateTime] [datetime] NULL ,
	[FlowId] [char] (36) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[附件信息] (
	[Guid] [varchar] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[WorkFlowInstanceId] [varchar] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[WorkTaskId] [varchar] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[fileName] [varchar] (1024) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CreateUser] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[CreateDateTime] [datetime] NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SysLog] (
	[Guid] [varchar] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Type] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[UserName] [varchar] (64) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CreateDataTime] [datetime] NOT NULL ,
	[SuccessFlag] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Operation] [varchar] (1024) COLLATE Chinese_PRC_CI_AS NULL ,
	[ErrorCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Des] [varchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
	[UserPost] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[UserIP] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkFlowInstance1] ADD 
	CONSTRAINT [PK_WorkFlowInstance1] PRIMARY KEY  CLUSTERED 
	(
		[Guid]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[WorkTaskInstance1] ADD 
	CONSTRAINT [DF_WorkTaskInstance1_SnatChatStatus] DEFAULT (0) FOR [SnatChatStatus],
	CONSTRAINT [DF_WorkTaskInstance1_CommandName] DEFAULT ('缺省提交') FOR [CommandName],
	CONSTRAINT [DF_WorkTaskInstance1_ProcessNotes] DEFAULT ('缺省提交') FOR [ProcessNotes],
	CONSTRAINT [PK_WorkTaskInstance1] PRIMARY KEY  CLUSTERED 
	(
		[Guid]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[WorkTaskAnnex1] ADD 
	CONSTRAINT [PK_WorkTaskAnnex1] PRIMARY KEY  CLUSTERED 
	(
		[Guid]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[OperateInstance1] ADD 
	CONSTRAINT [DF_OperateInstance1_CreateDateTime] DEFAULT (getdate()) FOR [CreateDateTime],
	CONSTRAINT [PK_OperateInstance1] PRIMARY KEY  CLUSTERED 
	(
		[Guid]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[OperateInstanceTemp] ADD 
	CONSTRAINT [DF_OperateInstanceTemp_CreateDateTime] DEFAULT (getdate()) FOR [CreateDateTime],
	CONSTRAINT [PK_OperateInstanceTemp] PRIMARY KEY  CLUSTERED 
	(
		[Guid]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[TaskRecRelation1] ADD 
	CONSTRAINT [PK_TaskRecRelation1] PRIMARY KEY  CLUSTERED 
	(
		[Guid]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[附件信息] ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[Guid]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO