?? contact0310.sql
字號:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BakupDocus_s1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BakupDocus_s1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BakupOld_Operate_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BakupOld_Operate_Log]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CR_Main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CR_Main]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Debug]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Debug]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Depart]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Depart]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Discuss]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Discuss]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Docus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Docus]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Docus_s1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Docus_s1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Docus_s2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Docus_s2]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ECN_Main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ECN_Main]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ECN_Sub]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ECN_Sub]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EW_Main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EW_Main]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EW_Sub]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EW_Sub]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Emp_Contract]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Emp_Contract]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Fllow_List]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Fllow_List]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GAPOperate_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GAPOperate_Log]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GAP_Main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GAP_Main]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GAP_Rights]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GAP_Rights]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LEAVE_Main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LEAVE_Main]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MailLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MailLog]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MoudleSYS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MoudleSYS]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyFiles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MyFiles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OABuild]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OABuild]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OABuildSub]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OABuildSub]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OAFiles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OAFiles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OAMinds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OAMinds]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OANews]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OANews]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Operate_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Operate_Log]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PR_Main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PR_Main]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PR_Sub]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PR_Sub]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tables]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tables]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TrueOperate_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TrueOperate_Log]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ttemp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[users]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[nowdate]') and OBJECTPROPERTY(id, N'IsDefault') = 1)
drop default [dbo].[nowdate]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[nowdm]') and OBJECTPROPERTY(id, N'IsDefault') = 1)
drop default [dbo].[nowdm]
GO
create default [nowdate] as convert(char(10),getdate(),120)
GO
create default [nowdm] as convert(varchar,getdate(),120)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** 對象: 用戶定義的函數 dbo.datetostr 腳本日期: 2008-07-15 12:51:41 ******/
CREATE FUNCTION [dbo].[datetostr]
(@date datetime)
RETURNS
char(10)
AS
BEGIN
return convert(char(10),@date,111)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.getPRRows
(
@filter varchar(200) = ''
)
RETURNS @rows_tb TABLE
(State varchar(10),Docu_Code varchar(20), PR_DEP varchar(20), PR_EMP varchar(30) , PR_Date char(10), REQ_Date char(10),
MEMO varchar(100),Make_by varchar(30),Make_Date char(10),Audit_by varchar(30),Audit_Date char(10),Author_by varchar(30),Author_Date char(10)
)
AS
BEGIN
if @filter='' select @filter=' where ' --因為@filter有兩種情況，一種是空字符，一種是含where的條件語句
declare @sql varchar(500)
select @sql = 'INSERT INTO @rows_tb
select ''未審核'',PR_ID,PR_DEP,PR_EMP,PR_Date,REQ_Date,MEMO,Make_by,Make_Date,Audit_by,Audit_Date,Author_by,Author_Date
from PR_Main' + @filter + ' Audit_by='''' and Author_by='''' '
EXECUTE sp_executesql @sql
select @sql = 'INSERT INTO @rows_tb
select ''已審核'',PR_ID,PR_DEP,PR_EMP,PR_Date,REQ_Date,MEMO,Make_by,Make_Date,Audit_by,Audit_Date,Author_by,Author_Date
from PR_Main' + @filter + ' Audit_by<>'''' and Author_by='''' '
EXECUTE sp_executesql @sql
select @sql = 'INSERT INTO @rows_tb
select ''已核準'',PR_ID,PR_DEP,PR_EMP,PR_Date,REQ_Date,MEMO,Make_by,Make_Date,Audit_by,Audit_Date,Author_by,Author_Date
from PR_Main' + @filter + ' Audit_by<>'''' and Author_by<>'''' '
EXECUTE sp_executesql @sql
/*INSERT INTO @rows_tb
select '未審核',PR_ID,PR_DEP,PR_EMP,PR_Date,REQ_Date,MEMO,Make_by,Make_Date,Audit_by,Audit_Date,Author_by,Author_Date
from PR_Main where Audit_by='' and Author_by=''
INSERT INTO @rows_tb
select '已審核',PR_ID,PR_DEP,PR_EMP,PR_Date,REQ_Date,MEMO,Make_by,Make_Date,Audit_by,Audit_Date,Author_by,Author_Date
from PR_Main where Audit_by<>'' and Author_by=''
INSERT INTO @rows_tb
select '已核準',PR_ID,PR_DEP,PR_EMP,PR_Date,REQ_Date,MEMO,Make_by,Make_Date,Audit_by,Audit_Date,Author_by,Author_Date
from PR_Main where Audit_by<>'' and Author_by<>''*/
/* alternative sql statement or statements */
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[ACompany] (
[CompanyCode] [int] NOT NULL ,
[CompanyCName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CompanyEName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[DatabaseName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[APrdofLine] (
[PrdPlanNO] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CompanyCode] [int] NOT NULL ,
[DepartCode] [int] NOT NULL ,
[PrdLineCode] [int] NOT NULL ,
[BegDate] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[EndDate] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[State] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[createUser] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[createDate] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[memo] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Ver] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[APrdofLineSub] (
[PrdPlanNO] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Seq] [int] NOT NULL ,
[ProductCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[BegPrdDate] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PrdQty] [int] NOT NULL ,
[memo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ASeloptions] (
[Userid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[formid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Line] [smallint] NOT NULL ,
[field] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[fielddesc] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[fieldtype] [smallint] NOT NULL ,
[defvalue1] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[defvalue2] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[seltable] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[selflds] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[selfilter] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[apply] [bit] NOT NULL ,
[issinglefld] [bit] NOT NULL ,
[SubtableName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SubtableFld] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AccPeriod] (
[RowID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyCode] [int] NOT NULL ,
[AccPeriodCode] [int] NOT NULL ,
[Accbegdate] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Accenddate] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[IsCurrent] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BakupDocus_s1] (
[Docu_id] [int] NOT NULL ,
[Operate_id] [int] NOT NULL ,
[Button_name] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Button_desc] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Is_status] [bit] NOT NULL ,
[status_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Complete_Operate_id] [int] NULL ,
[IsRequired] [bit] NOT NULL ,
[PreOP_Button_name] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BakupOld_Operate_Log] (
[AutoRec] [int] IDENTITY (1, 1) NOT NULL ,
[Docu_id] [int] NOT NULL ,
[Docu_Code] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Operate_uid] [int] NOT NULL ,
[Button_name] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CompOperate_id] [int] NOT NULL ,
[CompStatus_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Operate_time] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CR_Main] (
[CR_ID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CR_DEP] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CR_EMP] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PLAN_OUT_TIME] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PLAN_RTN_TIME] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PLAN_GO_ADDR] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[GOODS_WH] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[REQ_CAR_TYPE] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PLAN_COST] [numeric](18, 2) NOT NULL ,
[FACT_COST] [numeric](18, 2) NOT NULL ,
[MEMO] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Make_by] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Make_Date] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Audit_by] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Audit_Date] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Author_by] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Author_Date] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -