📄 sms3ex.sql
字号:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CMPP30_GETMESSAGE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_CMPP30_GETMESSAGE]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CMPP30_RECEIVEMESSAGE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_CMPP30_RECEIVEMESSAGE]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CMPP30_RECEIVEREPORT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_CMPP30_RECEIVEREPORT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CMPP30_SUBMIT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_CMPP30_SUBMIT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CMPP30_SUBMITERROR]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_CMPP30_SUBMITERROR]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CMPP30_SUBMITHISTORY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_CMPP30_SUBMITHISTORY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CMPP30_SyncOrderRelationReq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_CMPP30_SyncOrderRelationReq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CMPP30_UPDATESUBMIT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_CMPP30_UPDATESUBMIT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_SYSTEM_DATAPOOL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_SYSTEM_DATAPOOL]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VIEW_CMPP30_SUBMITREPORT]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VIEW_CMPP30_SUBMITREPORT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMPP30_DELIVER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMPP30_DELIVER]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMPP30_REPORT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMPP30_REPORT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMPP30_SUBMIT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMPP30_SUBMIT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMPP30_SUBMIT_ERROR]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMPP30_SUBMIT_ERROR]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMPP30_SUBMIT_HISTORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMPP30_SUBMIT_HISTORY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MISC16_SyncOrderRelationReq]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MISC16_SyncOrderRelationReq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SYSTEM_BADWORD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SYSTEM_BADWORD]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SYSTEM_BLACKLIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SYSTEM_BLACKLIST]
GO
CREATE TABLE [dbo].[CMPP30_DELIVER] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Msg_ID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Dest_Id] [varchar] (21) COLLATE Chinese_PRC_CI_AS NULL ,
[Service_Id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[TP_pid] [smallint] NULL ,
[TP_udhi] [smallint] NULL ,
[Pk_longId] [smallint] NULL ,
[Pk_total] [smallint] NULL ,
[Pk_number] [smallint] NULL ,
[Msg_Fmt] [smallint] NULL ,
[Src_terminal_Id] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[Src_terminal_type] [smallint] NULL ,
[Registered_Delivery] [smallint] NULL ,
[Msg_Length] [smallint] NULL ,
[Msg_Content] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[LinkID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[MO_Date] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMPP30_REPORT] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Msg_Id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Stat] [varchar] (7) COLLATE Chinese_PRC_CI_AS NULL ,
[Submit_time] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Done_time] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Dest_terminal_Id] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[SMSC_sequence] [bigint] NULL ,
[MO_Date] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMPP30_SUBMIT] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MT_SequenceID] [bigint] NULL ,
[MT_SendCount] [int] NULL ,
[MT_SubmitType] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[MT_Date] [datetime] NULL ,
[Pk_total] [smallint] NULL ,
[Pk_number] [smallint] NULL ,
[Registered_Delivery] [smallint] NULL ,
[Msg_level] [smallint] NULL ,
[Service_Id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Fee_UserType] [smallint] NULL ,
[Fee_terminal_Id] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[Fee_terminal_type] [smallint] NULL ,
[TP_pId] [smallint] NULL ,
[TP_udhi] [smallint] NULL ,
[Msg_Fmt] [smallint] NULL ,
[Msg_src] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[FeeType] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[FeeCode] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[ValId_Time] [varchar] (17) COLLATE Chinese_PRC_CI_AS NULL ,
[At_Time] [varchar] (17) COLLATE Chinese_PRC_CI_AS NULL ,
[Src_Id] [varchar] (21) COLLATE Chinese_PRC_CI_AS NULL ,
[Dest_terminal_Id] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[Dest_terminal_type] [smallint] NULL ,
[Msg_Content] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[LinkID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMPP30_SUBMIT_ERROR] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MT_SequenceID] [bigint] NULL ,
[MT_SendCount] [int] NULL ,
[MT_SubmitType] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[MT_Date] [datetime] NULL ,
[Msg_Id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Pk_total] [smallint] NULL ,
[Pk_number] [smallint] NULL ,
[Registered_Delivery] [smallint] NULL ,
[Msg_level] [smallint] NULL ,
[Service_Id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Fee_UserType] [smallint] NULL ,
[Fee_terminal_Id] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[Fee_terminal_type] [smallint] NULL ,
[TP_pId] [smallint] NULL ,
[TP_udhi] [smallint] NULL ,
[Msg_Fmt] [smallint] NULL ,
[Msg_src] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[FeeType] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[FeeCode] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[ValId_Time] [varchar] (17) COLLATE Chinese_PRC_CI_AS NULL ,
[At_Time] [varchar] (17) COLLATE Chinese_PRC_CI_AS NULL ,
[Src_Id] [varchar] (21) COLLATE Chinese_PRC_CI_AS NULL ,
[Dest_terminal_Id] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[Dest_terminal_type] [smallint] NULL ,
[Msg_Content] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[LinkID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ErrorCode] [int] NULL ,
[ErrorInfo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[ErrorDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMPP30_SUBMIT_HISTORY] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MT_SequenceID] [bigint] NULL ,
[MT_SendCount] [int] NULL ,
[MT_SubmitType] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[MT_Date] [datetime] NULL ,
[Msg_Id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Pk_total] [smallint] NULL ,
[Pk_number] [smallint] NULL ,
[Registered_Delivery] [smallint] NULL ,
[Msg_level] [smallint] NULL ,
[Service_Id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Fee_UserType] [smallint] NULL ,
[Fee_terminal_Id] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[Fee_terminal_type] [smallint] NULL ,
[TP_pId] [smallint] NULL ,
[TP_udhi] [smallint] NULL ,
[Msg_Fmt] [smallint] NULL ,
[Msg_src] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[FeeType] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[FeeCode] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[ValId_Time] [varchar] (17) COLLATE Chinese_PRC_CI_AS NULL ,
[At_Time] [varchar] (17) COLLATE Chinese_PRC_CI_AS NULL ,
[Src_Id] [varchar] (21) COLLATE Chinese_PRC_CI_AS NULL ,
[Dest_terminal_Id] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[Dest_terminal_type] [smallint] NULL ,
[Msg_Content] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[LinkID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MISC16_SyncOrderRelationReq] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MsgType] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[TransactionID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[Version] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Send_Address_DeviceType] [int] NULL ,
[Send_Address_DeviceID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[Dest_Address_DeviceType] [int] NULL ,
[Dest_Address_DeviceID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[FeeUser_ID_UserIDType] [int] NULL ,
[FeeUser_ID_MSISDN] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[FeeUser_ID_PseudoCode] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[DestUser_ID_UserIDType] [int] NULL ,
[DestUser_ID_MSISDN] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[DestUser_ID_PseudoCode] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[LinkID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ActionID] [int] NULL ,
[ActionReasonID] [int] NULL ,
[SPID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[SPServiceID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[AccessMode] [int] NULL ,
[FeatureStr] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[MO_Date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[SYSTEM_BADWORD] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Protocol] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[BadWord] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SYSTEM_BLACKLIST] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Protocol] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[Mobile] [varchar] (13) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMPP30_DELIVER] WITH NOCHECK ADD
CONSTRAINT [PK_Deliver] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMPP30_REPORT] WITH NOCHECK ADD
CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMPP30_SUBMIT] WITH NOCHECK ADD
CONSTRAINT [PK_Submit] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMPP30_SUBMIT_ERROR] WITH NOCHECK ADD
CONSTRAINT [PK_Submit_Error] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMPP30_SUBMIT_HISTORY] WITH NOCHECK ADD
CONSTRAINT [PK_Submit_History] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MISC16_SyncOrderRelationReq] WITH NOCHECK ADD
CONSTRAINT [PK_SyncOrderRelationReq] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SYSTEM_BADWORD] WITH NOCHECK ADD
CONSTRAINT [PK_SYSTEM_BADWORD] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SYSTEM_BLACKLIST] WITH NOCHECK ADD
CONSTRAINT [PK_SYSTEM_BLACKLIST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMPP30_DELIVER] ADD
CONSTRAINT [DF_CMPP30_DELIVER_Pk_total] DEFAULT (1) FOR [Pk_total],
CONSTRAINT [DF_CMPP30_DELIVER_Pk_number] DEFAULT (1) FOR [Pk_number],
CONSTRAINT [DF_Deliver_MO_Date] DEFAULT (getdate()) FOR [MO_Date]
GO
ALTER TABLE [dbo].[CMPP30_REPORT] ADD
CONSTRAINT [DF_Report_MO_Date] DEFAULT (getdate()) FOR [MO_Date]
GO
ALTER TABLE [dbo].[CMPP30_SUBMIT] ADD
CONSTRAINT [DF_Submit_MT_SequenceID] DEFAULT ((-1)) FOR [MT_SequenceID],
CONSTRAINT [DF_Submit_MT_SendCount] DEFAULT (0) FOR [MT_SendCount],
CONSTRAINT [DF_CMPP_Submit_MT_SubmitType] DEFAULT (1) FOR [MT_SubmitType],
CONSTRAINT [DF_Submit_MT_Date] DEFAULT (getdate()) FOR [MT_Date],
CONSTRAINT [DF_Submit_Pk_total] DEFAULT (1) FOR [Pk_total],
CONSTRAINT [DF_Submit_Pk_number] DEFAULT (1) FOR [Pk_number],
CONSTRAINT [DF_Submit_Registered_Delivery] DEFAULT (0) FOR [Registered_Delivery],
CONSTRAINT [DF_Submit_Msg_level] DEFAULT (7) FOR [Msg_level],
CONSTRAINT [DF_CMPP_Submit_Service_Id] DEFAULT ('ABC') FOR [Service_Id],
CONSTRAINT [DF_Submit_Fee_UserType] DEFAULT (0) FOR [Fee_UserType],
CONSTRAINT [DF_Submit_Fee_terminal_type] DEFAULT (0) FOR [Fee_terminal_type],
CONSTRAINT [DF_Submit_TP_pId] DEFAULT (0) FOR [TP_pId],
CONSTRAINT [DF_Submit_TP_udhi] DEFAULT (0) FOR [TP_udhi],
CONSTRAINT [DF_Submit_Msg_Fmt] DEFAULT (15) FOR [Msg_Fmt],
CONSTRAINT [DF_CMPP_Submit_FeeType] DEFAULT (1) FOR [FeeType],
CONSTRAINT [DF_Submit_FeeCode] DEFAULT (0) FOR [FeeCode],
CONSTRAINT [DF_Submit_Dest_terminal_type] DEFAULT (0) FOR [Dest_terminal_type]
GO
CREATE INDEX [Index_CMPP30_Submit] ON [dbo].[CMPP30_SUBMIT]([MT_SequenceID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMPP30_SUBMIT_ERROR] ADD
CONSTRAINT [DF_Submit_Error_MT_SequenceID] DEFAULT ((-1)) FOR [MT_SequenceID],
CONSTRAINT [DF_Submit_Error_MT_SendCount] DEFAULT (0) FOR [MT_SendCount],
CONSTRAINT [DF_Submit_Error_MT_Date] DEFAULT (getdate()) FOR [MT_Date],
CONSTRAINT [DF_Submit_Error_Msg_Id] DEFAULT (0) FOR [Msg_Id],
CONSTRAINT [DF_Submit_Error_Pk_total] DEFAULT (1) FOR [Pk_total],
CONSTRAINT [DF_Submit_Error_Pk_number] DEFAULT (1) FOR [Pk_number],
CONSTRAINT [DF_Submit_Error_Registered_Delivery] DEFAULT (0) FOR [Registered_Delivery],
CONSTRAINT [DF_Submit_Error_Msg_level] DEFAULT (7) FOR [Msg_level],
CONSTRAINT [DF_Submit_Error_Fee_UserType] DEFAULT (0) FOR [Fee_UserType],
CONSTRAINT [DF_Submit_Error_Fee_terminal_type] DEFAULT (0) FOR [Fee_terminal_type],
CONSTRAINT [DF_Submit_Error_TP_pId] DEFAULT (0) FOR [TP_pId],
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -