⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 mssql70_sunmis30.sql

📁 做的比较好的人力资源管理。数据库：MS SQL 7。可以很方便的移植到MS sql SERVER 2000上
💻 SQL
📖 第 1 页 / 共 5 页
字号:
🔍
12345 
   drop table [dbo].[gsdm]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[jksc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[jksc]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[wlcbxm]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[wlcbxm]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[wlqdmc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[wlqdmc]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ygzydm]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[ygzydm]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ygzzmm]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[ygzzmm]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ygzcdm]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[ygzcdm]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ygzwdm]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[ygzwdm]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[yglbdm]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[yglbdm]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ygxldm]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[ygxldm]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ygjtcy]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[ygjtcy]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ygjxjl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[ygjxjl]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ygdafj]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[ygdafj]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ygda]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[ygda]
GO







/*************************/
/* 开始建表               */
/*************************/
CREATE TABLE [dbo].[bbdm] (
	[bbdm] [char] (6) NOT NULL ,
	[bbmc] [char] (20) NOT NULL ,
	[bbbz] [char] (1) NULL ,
	[hl]     [decimal](18, 7) NOT NULL ,
	[jldw] [char] (10) NOT NULL ,
	[bz]     [char] (20) NULL ,
	CONSTRAINT [PK_bbdm] PRIMARY KEY  NONCLUSTERED ( [bbdm]) )
GO


CREATE TABLE [dbo].[bmdm] (
	[bmmc] [char] (20) NOT NULL ,
	[bmdm] [char] (6) NOT NULL,
	CONSTRAINT [pk_bmdm] PRIMARY KEY  NONCLUSTERED ([bmdm])  )
GO



CREATE TABLE [dbo].[cbxm] (
	[xmbh] [varchar] (4) NOT NULL ,
	[xmmc] [varchar] (20) NOT NULL ,
	CONSTRAINT [pk_cbxm] PRIMARY KEY  NONCLUSTERED ([xmbh])  )
GO


CREATE TABLE [dbo].[ckdm] (
	[ckdm] [varchar] (2) NOT NULL ,
	[ckmc] [varchar] (16) NOT NULL ,
	[ssbm] [varchar] (6) NULL ,
	[cgybm] [varchar] (6) NULL ,
	[wljzn] [int] NULL ,
	[wljzy] [int] NULL ,
	[cpjzn] [int] NULL ,
	[cpjzy] [int] NULL ,
	[wljzwc] [varchar] (1) NULL ,
	[cpjzwc] [varchar] (1) NULL ,
	[qtcx] [char] (1) NULL ,
	[ckyt] [char] (2) NULL ,
	[xsdfh] [char] (1) NULL ,
	[gcbh] [char] (1) NULL ,
       [jjff] [char] (2) NOT NULL DEFAULT "SJ",
       [init] [char] (1) NULL DEFAULT "N",
	CONSTRAINT [pk_ckdm] PRIMARY KEY  NONCLUSTERED ([ckdm]) )
GO




CREATE TABLE [dbo].[cpdm] (
	[cpdm] [varchar] (10) NOT NULL ,
	[cpmc] [varchar] (24) NOT NULL ,
	[cpxh] [varchar] (30) NULL ,
	[cplb] [varchar] (4) NOT NULL ,
	[jldw] [char] (6) NULL ,
	[kcsl] [decimal](14, 3) NULL ,
	[dhsl] [decimal](14, 3) NULL ,
	[zzsl] [decimal](14, 3) NULL ,
	[qhsl] [decimal](14, 3) NULL ,
	CONSTRAINT [PK_cpdm] PRIMARY KEY  NONCLUSTERED ([cpdm])  )
GO




CREATE TABLE [dbo].[cplb] (
	[cplb] [varchar] (4) NOT NULL ,
	[cplbmc] [varchar] (24) NOT NULL ,
	[xlh_bz] [varchar] (1) NULL ,
	[scjdms] [char] (2) NULL ,
	CONSTRAINT [pk_cplb] PRIMARY KEY  NONCLUSTERED ([cplb])  )
GO





CREATE TABLE [dbo].[cplbmbcb] (
	[djcb] [decimal](12, 2) NOT NULL ,
	[cbxmbh] [char] (4) NOT NULL ,
	[cplb] [char] (4) NOT NULL ,
	CONSTRAINT [pk_cplbmbcb] PRIMARY KEY  NONCLUSTERED ([cbxmbh],[cplb])  )
GO





CREATE TABLE [dbo].[cplbsjcb] (
	[cbxmbh] [char] (4) NOT NULL ,
	[cplb] [char] (4) NOT NULL ,
	CONSTRAINT [pk_cplbsjcb] PRIMARY KEY  NONCLUSTERED ([cbxmbh],[cplb]) )
GO





CREATE TABLE [dbo].[cppc] (
	[ckdm] [char] (2) NOT NULL ,
	[cpdm] [char] (10) NOT NULL ,
	[pch]  [char] (12) NOT NULL ,
	[gcbh] [char] (10) NULL ,
	[kcsl] [decimal](14, 3) NOT NULL DEFAULT (0),
	[sqkcsl] [decimal](14, 3) NOT NULL DEFAULT (0),
	[yf]      [char] (6) NULL ,
	CONSTRAINT [PK_cppc] PRIMARY KEY  NONCLUSTERED ([ckdm],[cpdm],[pch]) )
GO




CREATE TABLE [dbo].[cppc_ld] (
	[yf] [char] (6) NOT NULL ,
	[ckdm] [char] (2) NOT NULL ,
	[cpdm] [char] (10) NOT NULL ,
	[pch] [char] (12) NOT NULL ,
	[gcbh] [char] (10) NULL ,
	[kcsl] [decimal](14, 3) NOT NULL DEFAULT (0),
	[sqkcsl] [decimal](14, 3) NOT NULL DEFAULT (0), 
	CONSTRAINT [PK_cppc_ld] PRIMARY KEY  NONCLUSTERED ([yf],[ckdm],[cpdm],[pch])  )
GO




CREATE TABLE [dbo].[cprk] (
	[ckdm] [char] (2) NOT NULL ,
	[rkdh] [char] (12) NOT NULL ,
	[rkrq] [datetime] NOT NULL ,
	[cpdm] [char] (10) NOT NULL ,
	[gcbh] [char] (10) NULL ,
	[rksl] [decimal](14, 3) NOT NULL ,
	[zdr] [char] (8) NOT NULL ,
	[bz] [char] (20) NULL ,
	[rkdlb] [char] (2) NOT NULL ,
	[zrck] [char] (2) NULL ,
	[pch] [char] (12) NOT NULL ,
	[jzr] [char] (10) NULL ,
	[shr] [char] (10) NULL ,
	[yf] [char] (6) NULL ,
	CONSTRAINT [PK_cprk] PRIMARY KEY  NONCLUSTERED ([ckdm],[rkdh]) )
GO





CREATE TABLE [dbo].[cprk_ld] (
	[yf] [char] (6) NOT NULL ,
	[ckdm] [char] (2) NOT NULL ,
	[rkdh] [char] (12) NOT NULL ,
	[rkrq] [datetime] NOT NULL ,
	[cpdm] [char] (10) NOT NULL ,
	[gcbh] [char] (10) NULL ,
	[rksl] [decimal](14, 3) NOT NULL ,
	[zdr] [char] (8) NOT NULL ,
	[bz] [char] (20) NULL ,
	[rkdlb] [char] (2) NOT NULL ,
	[zrck] [char] (2) NULL ,
	[pch] [char] (12) NOT NULL ,
	[jzr] [char] (10) NULL ,
	[shr] [char] (10) NULL ,
	CONSTRAINT [PK_cprk_ld] PRIMARY KEY  NONCLUSTERED ([yf],[ckdm],[rkdh])  )
GO





CREATE TABLE [dbo].[cprkxlh] (
	[ckdm] [char] (2) NOT NULL ,
	[rkdh] [char] (12) NOT NULL ,
	[xlh] [char] (20) NOT NULL ,
	[old_scrq] [datetime] NULL ,
	[old_cfck] [char] (2) NULL ,
	[old_zrrq] [datetime] NULL ,
	[old_zcrq] [datetime] NULL ,
	[old_pch] [char] (12) NULL ,
	[old_rkdh] [char] (12) NULL ,
	[old_rckdm] [char] (2) NULL ,
	[yf] [char] (6) NULL ,
	CONSTRAINT [PK_cprkxlh] PRIMARY KEY  NONCLUSTERED ([ckdm],[rkdh],[xlh])  )
GO



CREATE TABLE [dbo].[cprkxlh_ld] (
	[yf] [char] (6) NOT NULL ,
	[ckdm] [char] (2) NOT NULL ,
	[rkdh] [char] (12) NOT NULL ,
	[xlh] [char] (20) NOT NULL ,
	[old_scrq] [datetime] NULL ,
	[old_cfck] [char] (2) NULL ,
	[old_zrrq] [datetime] NULL ,
	[old_zcrq] [datetime] NULL ,
	[old_pch] [char] (12) NULL ,
	[old_rkdh] [char] (12) NULL ,
	[old_rckdm] [char] (2) NULL ,
	CONSTRAINT [PK_cprkxlh_ld] PRIMARY KEY  NONCLUSTERED ([yf],[ckdm],[rkdh],	[xlh])  )
GO





CREATE TABLE [dbo].[cptz] (
	[cpdm] [varchar] (10) NOT NULL ,
	[kcsl] [decimal](13, 3) NOT NULL ,
	[cbdj] [decimal](13, 3) NULL ,
	[xsdj] [decimal](13, 3) NULL ,
	[cfdz] [varchar] (20) NULL ,
	[sqkcl] [decimal](13, 3) NULL ,
	[ckdm] [char] (2) NOT NULL ,
	[cbjje] [numeric](13, 3) NULL ,
	[xsjje] [numeric](13, 3) NULL ,
	[yf] [char] (6) NULL ,
	CONSTRAINT [PK_cptz] PRIMARY KEY  NONCLUSTERED ([cpdm],[ckdm]) )
GO




CREATE TABLE [dbo].[cptz_ld] (
	[yf] [varchar] (6) NOT NULL ,
	[cpdm] [varchar] (10) NOT NULL ,
	[kcsl] [decimal](13, 3) NOT NULL ,
	[cbdj] [decimal](13, 3) NULL ,
	[xsdj] [decimal](13, 3) NULL ,
	[cfdz] [varchar] (20) NULL ,
	[sqkcl] [decimal](13, 3) NULL ,
	[ckdm] [char] (2) NOT NULL ,
	[cbjje] [numeric](13, 3) NULL ,
	[xsjje] [numeric](13, 3) NULL ,
	CONSTRAINT [pk_cptz_ld] PRIMARY KEY  NONCLUSTERED ([yf],[cpdm],[ckdm])  )
GO



CREATE TABLE [dbo].[cpxlh] (
	[xlh] [varchar] (20) NOT NULL ,
	[cpdm] [varchar] (10) NOT NULL ,
	[rkrq] [datetime] NULL ,
	[cfdz] [varchar] (20) NULL ,
	[fhrq] [datetime] NULL ,
	[gcbh] [varchar] (10) NOT NULL ,
	[khbh] [varchar] (10) NULL ,
	[xssbh] [varchar] (10) NULL ,
	[xlh2] [char] (20) NULL ,
	[xlh3] [char] (20) NULL ,
	[sel_yn] [char] (1) NULL ,
	[fhbz] [char] (20) NULL ,
	[msbh] [char] (2) NULL ,
	[rq01] [datetime] NULL ,
	[rq02] [datetime] NULL ,
	[rq03] [datetime] NULL ,
	[rq04] [datetime] NULL ,
	[rq05] [datetime] NULL ,
	[rq06] [datetime] NULL ,
	[rq07] [datetime] NULL ,
	[rq08] [datetime] NULL ,
	[rq09] [datetime] NULL ,
	[rq10] [datetime] NULL ,
	[cfck] [char] (2) NULL ,
	[zrrq] [datetime] NULL ,
	[zcrq] [datetime] NULL ,
	[pch] [char] (12) NULL ,
	[rkdh] [char] (12) NULL ,
	[rckdm] [char] (2) NULL ,
	[fhdh] [char] (12) NULL ,
	[fckdm] [char] (2) NULL ,
	CONSTRAINT [PK_cpxlh] PRIMARY KEY  NONCLUSTERED ([xlh])  )
GO





CREATE TABLE [dbo].[cxtjlm] (
	[tjid] [int] NOT NULL ,
	[bm] [varchar] (30) NOT NULL ,
	[column1] [varchar] (30) NOT NULL ,
	[dw_colname] [varchar] (30) NULL ,
	CONSTRAINT [pk_cxtjlm] PRIMARY KEY  NONCLUSTERED ([tjid],[bm],[column1])  )
GO



CREATE TABLE [dbo].[cxtjmc] (
	[tjid] [int] NOT NULL ,
	[mkmc] [varchar] (30) NOT NULL ,
	[tjmc] [varchar] (30) NOT NULL ,
	CONSTRAINT [pk_cxtjmc] PRIMARY KEY  NONCLUSTERED ([tjid])  )
GO




CREATE TABLE [dbo].[cxtjnr] (
	[tjid] [int] NOT NULL ,
	[kh] [varchar] (5) NULL ,
	[column1] [varchar] (30) NULL ,
	[oper] [varchar] (12) NULL ,
	[value] [varchar] (100) NULL ,
	[logical] [varchar] (5) NULL ,
	[id] [int] NOT NULL ,
	CONSTRAINT [pk_cxtjnr] PRIMARY KEY  NONCLUSTERED ([tjid],[id])  )
GO





CREATE TABLE [dbo].[cxtjsort] (
	[tjid] [int] NULL ,
	[sortcol] [varchar] (60) NULL ,
	[sorttype] [decimal](1, 0) NULL ,
	[id] [int] NOT NULL ,
	CONSTRAINT [pk_cxtjsort] PRIMARY KEY  NONCLUSTERED ([id]) )
GO




CREATE TABLE [dbo].[dqdm] (
	[dqdm] [varchar] (4) NOT NULL ,
	[dqmc] [varchar] (20) NOT NULL ,
	CONSTRAINT [pk_dqdm] PRIMARY KEY  NONCLUSTERED ([dqdm])  )
GO




CREATE TABLE [dbo].[fhd] (
	[ckdm] [char] (2) NOT NULL ,
	[fhdh] [char] (12) NOT NULL ,
	[fhrq] [datetime] NOT NULL ,
	[xsdh] [char] (12) NULL ,
	[fhdlb] [char] (2) NOT NULL ,
	[zdr] [char] (8) NOT NULL ,
	[bz] [char] (20) NULL ,
	[htbh] [char] (20) NULL ,
	[zcck] [char] (2) NULL ,
	[jzr] [char] (10) NULL ,
	[shr] [char] (10) NULL ,
	[yf] [char] (6) NULL ,
	CONSTRAINT [PK_fhd] PRIMARY KEY  NONCLUSTERED ([ckdm],[fhdh])  )
GO



CREATE TABLE [dbo].[fhd_ld] (
	[yf] [char] (6) NOT NULL ,
	[ckdm] [char] (2) NOT NULL ,
	[fhdh] [char] (12) NOT NULL ,
	[fhrq] [datetime] NOT NULL ,
	[zdr] [char] (8) NOT NULL ,
	[bz] [char] (20) NULL ,
	[xsdh] [char] (12) NULL ,
12345

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -