NDSIMMsgLog

USE

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

CREATE TABLE [dbo].[tUser] (
 [lUserID] [int] IDENTITY (1, 1) NOT NULL ,
 [sUserID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sCompanyID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sUserName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sCardInNum] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [sCardOutNum] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [sCardType] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
 [bSex] [bit] NULL ,
 [dtBirthday] [datetime] NULL ,
 [sIDCode] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
 [sIDNo] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
 [sIDUnit] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
 [sWorkUnit] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
 [sPeopleCode] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
 [sNationCode] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
 [bUserStatus] [bit] NOT NULL ,
 [dtLoginTime] [datetime] NULL ,
 [dtOfflineTime] [datetime] NULL ,
 [sComputerIP] [varchar] (39) COLLATE Chinese_PRC_CI_AS NULL ,
 [sComputerName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
 [sComputerNo] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
 [sComputerMac] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
 [sInitCompanyID] [varchar] (14) COLLATE Chinese_PRC_CI_AS NULL ,
 [dtRegTime] [datetime] NULL ,
 [sModifyCompanyID] [varchar] (14) COLLATE Chinese_PRC_CI_AS NULL ,
 [dtLastModify] [datetime] NULL ,
 [bHasSent] [bit] NOT NULL ,
 [dtWriteTime] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tUser] WITH NOCHECK ADD
 CONSTRAINT [PK_tUser] PRIMARY KEY  CLUSTERED
 (
  [sUserID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tUser] ADD
 CONSTRAINT [DF_tUser_sPeopleCode] DEFAULT (N'HA') FOR [sPeopleCode],
 CONSTRAINT [DF_tUser_sNationCode] DEFAULT (N'CHN') FOR [sNationCode],
 CONSTRAINT [DF_tUser_bUserStatus] DEFAULT (0) FOR [bUserStatus],
 CONSTRAINT [DF_tUser_dtRegTime] DEFAULT (getdate()) FOR [dtRegTime],
 CONSTRAINT [DF_tUser_bHasSent] DEFAULT (0) FOR [bHasSent],
 CONSTRAINT [DF_tUser_dtWriteTime] DEFAULT (getdate()) FOR [dtWriteTime]
GO

 CREATE  INDEX [IX_tUser_sIDNo] ON [dbo].[tUser]([sIDNo]) ON [PRIMARY]
GO

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

CREATE TABLE [dbo].[tIMMsgLog] (
 [lLogID] [int] IDENTITY (1, 1) NOT NULL ,
 [sUserID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sUserName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sCompanyID] [varchar] (14) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sServiceType] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sAccounts] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sSender] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
 [sRecver] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
 [sMsg] [varchar] (512) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [dtVisitTime] [datetime] NOT NULL ,
 [dtCenterTime] [datetime] NULL ,
 [sComputerIP] [varchar] (39) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sComputerMac] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
 [sComputerName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [sComputerNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [sSendAccount] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
 [bHasSent] [bit] NULL ,
 [sReceiveAccount] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tIMMsgLog] ADD
 CONSTRAINT [DF_tIMMsgLog20070130_sCenterTime] DEFAULT (getdate()) FOR [dtCenterTime],
 CONSTRAINT [PK_tIMMsgLog20070130] PRIMARY KEY  CLUSTERED
 (
  [lLogID]
 )  ON [PRIMARY]
GO

 

-----------------------------用户函数-------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FUNC_ConvertDateTime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FUNC_ConvertDateTime]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Func_GetDtByDateTime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Func_GetDtByDateTime]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetOrderType]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetOrderType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetToday]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetToday]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO