生成DDL範例協助
July 26, 2024
This conversation may reflect the link creator’s personalized data, which isn’t shared and can meaningfully change how the model responds.
等下幫我 gen ddl, 我先給你範例
好的,請提供範例,我會根據它來生成你需要的DDL。
先給你我示範提供的資料
明白了,請提供你的示範資料。
我會給你以下格式的資料,等下給你我希望你 gen 的格式
PUSH_SYS APP基本資料
Name Type Nullable Index Comments
SYS_UID varchar(4) PK 系統代碼
SYS_NAME nvarchar(20) 系統名稱
SYS_STATUS char(1) 系統狀態
BUNDLE_ID varchar(100) IOS BUNDLE ID Package Name
FCM_CERT varchar(MAX) V Android FCM KEY
APNS_CERT_FILE varchar(100) V IOS 憑證檔案
APNS_CERT_PWD varchar(300) V IOS 憑證密碼
CREATE_TIME datetime 建立時間
UPDATE_TIME datetime V 更新時間
APP_OWNER varchar(100) V 管理員註記
APP_REMARK varchar(15) V APP備註
CREATE_USER varchar(100) 建立人員
UPDATE_USER varchar(100) V 更新人員
了解了,請告訴我你希望生成DDL的格式或範本,我會根據你的要求進行生成。
以下是我希望你給我的格式,等下給你其他table 資料
-- PUSH.dbo.PUSH_SYS definition
-- Drop table if it exists
-- DROP TABLE PUSH.dbo.PUSH_SYS;
CREATE TABLE PUSH.dbo.PUSH_SYS (
SYS_UID varchar(4) NOT NULL, -- 系統代碼
SYS_NAME nvarchar(20) NOT NULL, -- 系統名稱
SYS_STATUS char(1) NOT NULL, -- 系統狀態
BUNDLE_ID varchar(100) NOT NULL, -- IOS BUNDLE ID Package Name
FCM_CERT varchar(MAX) , -- Android FCM KEY
APNS_CERT_FILE varchar(100) , -- IOS 憑證檔案
APNS_CERT_PWD varchar(300) , -- IOS 憑證密碼
CREATE_TIME datetime NOT NULL, -- 建立時間
UPDATE_TIME datetime , -- 更新時間
APP_OWNER varchar(100) , -- 管理員註記
APP_REMARK varchar(15) , -- APP備註
CREATE_USER varchar(100) NOT NULL, -- 建立人員
UPDATE_USER varchar(100) , -- 更新人員
CONSTRAINT PUSH_SYS_PK PRIMARY KEY (SYS_UID) -- Primary Key
);
-- Extended properties
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'APP基本資料', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統代碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'SYS_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統名稱', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'SYS_NAME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統狀態', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'SYS_STATUS';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IOS BUNDLE ID Package Name', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'BUNDLE_ID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Android FCM KEY', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'FCM_CERT';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IOS 憑證檔案', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'APNS_CERT_FILE';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IOS 憑證密碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'APNS_CERT_PWD';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'CREATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'UPDATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'管理員註記', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'APP_OWNER';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'APP備註', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'APP_REMARK';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立人員', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'CREATE_USER';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人員', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_SYS', @level2type=N'Column', @level2name=N'UPDATE_USER';
明白了!請提供其他表格的資料,我會按照你希望的格式生成DDL。
PUSH_API API使用管理
Name Type Nullable Index Comments
API_UID varchar(10) PK API代碼
API_NAME nvarchar(30) API名稱
API_STATUS char(1) API開啟'1'/暫停'2'
API_DESC nvarchar(300) V 說明
CREATE_TIME datetime 建立時間
UPDATE_TIME datetime V 更新時間
CREATE_USER varchar(100) 建立人員
UPDATE_USER varchar(100) V 更新人員
以下是根據你提供的格式生成的DDL語句:
sql-- PUSH.dbo.PUSH_API definition
-- Drop table if it exists
-- DROP TABLE PUSH.dbo.PUSH_API;
CREATE TABLE PUSH.dbo.PUSH_API (
API_UID varchar(10) NOT NULL, -- API代碼
API_NAME nvarchar(30) NOT NULL, -- API名稱
API_STATUS char(1) NOT NULL, -- API開啟'1'/暫停'2'
API_DESC nvarchar(300) , -- 說明
CREATE_TIME datetime NOT NULL, -- 建立時間
UPDATE_TIME datetime , -- 更新時間
CREATE_USER varchar(100) NOT NULL, -- 建立人員
UPDATE_USER varchar(100) , -- 更新人員
CONSTRAINT PUSH_API_PK PRIMARY KEY (API_UID) -- Primary Key
);
-- Extended properties
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API使用管理', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_API';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API代碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_API', @level2type=N'Column', @level2name=N'API_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API名稱', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_API', @level2type=N'Column', @level2name=N'API_NAME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API開啟''1''/暫停''2''', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_API', @level2type=N'Column', @level2name=N'API_STATUS';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'說明', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_API', @level2type=N'Column', @level2name=N'API_DESC';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_API', @level2type=N'Column', @level2name=N'CREATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_API', @level2type=N'Column', @level2name=N'UPDATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立人員', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_API', @level2type=N'Column', @level2name=N'CREATE_USER';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人員', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_API', @level2type=N'Column', @level2name=N'UPDATE_USER';
如果還有其他表格資料需要生成DDL,隨時告訴我!
PUHS_CHANNEL Channel基本資料
Name Type Nullable Index Default Comments
CH_UID char(4) PK Channel 代碼
CH_NAME varchar(20) Channel 名稱
ENCBLOCK char(64) Channel 密碼
CH_TPS int TPS
CH_STATUS char(1) Channel狀態
CREATE_TIME datetime 建立時間
UPDATE_TIME datetime V 更新時間
CH_OWNER varchar(100) V Channel擁有者
CH_REMARK varchar(300) V Channel備註
CREATE_USER varchar(20) 建立人員
UPDATE_USER varchar(20) V 更新人員
根據你提供的格式,這是 PUHS_CHANNEL
表的DDL語句:
sql-- PUSH.dbo.PUHS_CHANNEL definition
-- Drop table if it exists
-- DROP TABLE PUSH.dbo.PUHS_CHANNEL;
CREATE TABLE PUSH.dbo.PUHS_CHANNEL (
CH_UID char(4) NOT NULL, -- Channel 代碼
CH_NAME varchar(20) NOT NULL, -- Channel 名稱
ENCBLOCK char(64) NOT NULL, -- Channel 密碼
CH_TPS int NOT NULL, -- TPS
CH_STATUS char(1) NOT NULL, -- Channel狀態
CREATE_TIME datetime NOT NULL, -- 建立時間
UPDATE_TIME datetime , -- 更新時間
CH_OWNER varchar(100) , -- Channel擁有者
CH_REMARK varchar(300) , -- Channel備註
CREATE_USER varchar(20) NOT NULL, -- 建立人員
UPDATE_USER varchar(20) , -- 更新人員
CONSTRAINT PUHS_CHANNEL_PK PRIMARY KEY (CH_UID) -- Primary Key
);
-- Extended properties
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Channel基本資料', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Channel 代碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL', @level2type=N'Column', @level2name=N'CH_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Channel 名稱', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL', @level2type=N'Column', @level2name=N'CH_NAME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Channel 密碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL', @level2type=N'Column', @level2name=N'ENCBLOCK';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TPS', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL', @level2type=N'Column', @level2name=N'CH_TPS';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Channel狀態', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL', @level2type=N'Column', @level2name=N'CH_STATUS';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL', @level2type=N'Column', @level2name=N'CREATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL', @level2type=N'Column', @level2name=N'UPDATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Channel擁有者', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL', @level2type=N'Column', @level2name=N'CH_OWNER';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Channel備註', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL', @level2type=N'Column', @level2name=N'CH_REMARK';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立人員', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL', @level2type=N'Column', @level2name=N'CREATE_USER';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人員', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUHS_CHANNEL', @level2type=N'Column', @level2name=N'UPDATE_USER';
如果還有其他表格資料需要生成DDL,請隨時告訴我!
PUSH_CH_APP Channel與APP關聯資料
Name Type Nullable Index Default Comments
CH_APP_SEQ int PK 序號
CH_UID char(4) V Channel 代碼
APP_UID varchar(4) APP代碼
CREATE_TIME datetime 建立時間
根據你提供的格式,這是 PUSH_CH_APP
表的DDL語句:
sql-- PUSH.dbo.PUSH_CH_APP definition
-- Drop table if it exists
-- DROP TABLE PUSH.dbo.PUSH_CH_APP;
CREATE TABLE PUSH.dbo.PUSH_CH_APP (
CH_APP_SEQ int NOT NULL, -- 序號
CH_UID char(4) , -- Channel 代碼
APP_UID varchar(4) , -- APP代碼
CREATE_TIME datetime NOT NULL, -- 建立時間
CONSTRAINT PUSH_CH_APP_PK PRIMARY KEY (CH_APP_SEQ) -- Primary Key
);
-- Extended properties
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Channel與APP關聯資料', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CH_APP';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序號', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CH_APP', @level2type=N'Column', @level2name=N'CH_APP_SEQ';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Channel 代碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CH_APP', @level2type=N'Column', @level2name=N'CH_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'APP代碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CH_APP', @level2type=N'Column', @level2name=N'APP_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CH_APP', @level2type=N'Column', @level2name=N'CREATE_TIME';
如果還有其他表格資料需要生成DDL,隨時告訴我!
PUSH_CH_API Channel與API關聯資料
Name Type Nullable Index Default Comments
CH_API_SEQ int PK 序號
CH_UID char(4) V Channel ID
API_UID varchar(8) API UID
CREATE_TIME datetime 建立時間
這是 PUSH_CH_API
表的DDL語句:
sql-- PUSH.dbo.PUSH_CH_API definition
-- Drop table if it exists
-- DROP TABLE PUSH.dbo.PUSH_CH_API;
CREATE TABLE PUSH.dbo.PUSH_CH_API (
CH_API_SEQ int NOT NULL, -- 序號
CH_UID char(4) , -- Channel ID
API_UID varchar(8) , -- API UID
CREATE_TIME datetime NOT NULL, -- 建立時間
CONSTRAINT PUSH_CH_API_PK PRIMARY KEY (CH_API_SEQ) -- Primary Key
);
-- Extended properties
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Channel與API關聯資料', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CH_API';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序號', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CH_API', @level2type=N'Column', @level2name=N'CH_API_SEQ';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Channel ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CH_API', @level2type=N'Column', @level2name=N'CH_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API UID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CH_API', @level2type=N'Column', @level2name=N'API_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CH_API', @level2type=N'Column', @level2name=N'CREATE_TIME';
如果還有其他表格資料需要生成DDL,隨時告訴我!
PUSH_USER PUSH使用者
Name Type Nullable Index Comments
USER_UQ varchar(255) PK 使用者唯一識別值
SYS_UID varchar(4) PK 系統代碼
USER_STATUS char(1) 使用者狀態
CREATE_TIME datetime 建立時間
UPDATE_TIME datetime V 更新時間
CREATE_CH char(4) 建立的Channel
UPDATE_CH char(4) V 更新的Channel
USER_PHONE varchar(10) V 使用者手機號碼
USER_EMAIL varchar(255) V 使用者電子信箱
PART_KEY int PK partition key
ALL_DELETE_TIME datetime V 全刪除時間
ALL_READ_TIME datetime V 全已讀時間
這是 PUSH_USER
表的DDL語句:
sql-- PUSH.dbo.PUSH_USER definition
-- Drop table if it exists
-- DROP TABLE PUSH.dbo.PUSH_USER;
CREATE TABLE PUSH.dbo.PUSH_USER (
USER_UQ varchar(255) NOT NULL, -- 使用者唯一識別值
SYS_UID varchar(4) NOT NULL, -- 系統代碼
USER_STATUS char(1) NOT NULL, -- 使用者狀態
CREATE_TIME datetime NOT NULL, -- 建立時間
UPDATE_TIME datetime , -- 更新時間
CREATE_CH char(4) , -- 建立的Channel
UPDATE_CH char(4) , -- 更新的Channel
USER_PHONE varchar(10) , -- 使用者手機號碼
USER_EMAIL varchar(255) , -- 使用者電子信箱
PART_KEY int NOT NULL, -- partition key
ALL_DELETE_TIME datetime , -- 全刪除時間
ALL_READ_TIME datetime , -- 全已讀時間
CONSTRAINT PUSH_USER_PK PRIMARY KEY (USER_UQ, SYS_UID, PART_KEY) -- Primary Key
);
-- Extended properties
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'PUSH使用者', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'使用者唯一識別值', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'USER_UQ';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統代碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'SYS_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'使用者狀態', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'USER_STATUS';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'CREATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'UPDATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立的Channel', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'CREATE_CH';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新的Channel', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'UPDATE_CH';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'使用者手機號碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'USER_PHONE';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'使用者電子信箱', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'USER_EMAIL';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'partition key', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'PART_KEY';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'全刪除時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'ALL_DELETE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'全已讀時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_USER', @level2type=N'Column', @level2name=N'ALL_READ_TIME';
如果有其他表格或需要調整的地方,請隨時告訴我!
PUSH_CONF 使用者App推播設定
Name Type Nullable Index Comments
USER_UQ varchar(255) PK 使用者序號
SYS_UID varchar(4) PK 系統代碼
PUSH_FLAG char(1) 推播通知開關
PUSH_HOUR1 char(1) 推播時段一
PUSH_HOUR2 char(1) 推播時段二
PUSH_HOUR3 char(1) 推播時段三
NIGHT_SILENT char(1) 夜間靜音
CREATE_TIME datetime 建立時間
UPDATE_TIME datetime V 更新時間
CREATE_CH char(4) 建立的Channel
UPDATE_CH char(4) V 更新的Channel
這是 PUSH_CONF
表的DDL語句:
sql-- PUSH.dbo.PUSH_CONF definition
-- Drop table if it exists
-- DROP TABLE PUSH.dbo.PUSH_CONF;
CREATE TABLE PUSH.dbo.PUSH_CONF (
USER_UQ varchar(255) NOT NULL, -- 使用者序號
SYS_UID varchar(4) NOT NULL, -- 系統代碼
PUSH_FLAG char(1) NOT NULL, -- 推播通知開關
PUSH_HOUR1 char(1) , -- 推播時段一
PUSH_HOUR2 char(1) , -- 推播時段二
PUSH_HOUR3 char(1) , -- 推播時段三
NIGHT_SILENT char(1) , -- 夜間靜音
CREATE_TIME datetime NOT NULL, -- 建立時間
UPDATE_TIME datetime , -- 更新時間
CREATE_CH char(4) , -- 建立的Channel
UPDATE_CH char(4) , -- 更新的Channel
CONSTRAINT PUSH_CONF_PK PRIMARY KEY (USER_UQ, SYS_UID) -- Primary Key
);
-- Extended properties
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'使用者App推播設定', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'使用者序號', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF', @level2type=N'Column', @level2name=N'USER_UQ';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統代碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF', @level2type=N'Column', @level2name=N'SYS_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'推播通知開關', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF', @level2type=N'Column', @level2name=N'PUSH_FLAG';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'推播時段一', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF', @level2type=N'Column', @level2name=N'PUSH_HOUR1';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'推播時段二', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF', @level2type=N'Column', @level2name=N'PUSH_HOUR2';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'推播時段三', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF', @level2type=N'Column', @level2name=N'PUSH_HOUR3';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'夜間靜音', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF', @level2type=N'Column', @level2name=N'NIGHT_SILENT';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF', @level2type=N'Column', @level2name=N'CREATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF', @level2type=N'Column', @level2name=N'UPDATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立的Channel', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF', @level2type=N'Column', @level2name=N'CREATE_CH';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新的Channel', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_CONF', @level2type=N'Column', @level2name=N'UPDATE_CH';
如果有其他表格需要DDL語句或任何其他幫助,請告訴我!
PUSH_DEVICE 使用者裝置
Name Type Nullable Index Comments
DEVICE_UUID varchar(255) PK 手機序號
SYS_UID VARCHAR(4) 系統代碼
PUSH_TOKEN varchar(255) U 推播碼
USER_SEQ varchar(255) V V 綁定的使用者序號
INI_PUSH_FLAG char(1) 初始推播通知開關
INI_PUSH_HOUR1 char(1) 初始推播時段一
INI_PUSH_HOUR2 char(1) 初始推播時段二
INI_PUSH_HOUR3 char(1) 初始推播時段三
NIGHT_SILENT char(1) 夜間靜音
APP_VERSION varchar(30) APP版本
DEVICE_VERSION varchar(30) 手機OS版本
DEVICE_OS varchar(20) 手機作業系統
DEVICE_BRAND varchar(30) 手機廠牌型號
CREATE_TIME datetime 建立時間
UPDATE_TIME datetime V 更新時間
OLD_USER_SEQ varchar(255) V 被置換的User
USER_CHANGE_TIME datetime V User置換的時間
CREATE_CH char(4) 建立的Channel
UPDATE_CH char(4) V 更新的Channel
這是 PUSH_DEVICE
表的DDL語句:
sql-- PUSH.dbo.PUSH_DEVICE definition
-- Drop table if it exists
-- DROP TABLE PUSH.dbo.PUSH_DEVICE;
CREATE TABLE PUSH.dbo.PUSH_DEVICE (
DEVICE_UUID varchar(255) NOT NULL, -- 手機序號
SYS_UID varchar(4) NOT NULL, -- 系統代碼
PUSH_TOKEN varchar(255) UNIQUE NOT NULL, -- 推播碼
USER_SEQ varchar(255) , -- 綁定的使用者序號
INI_PUSH_FLAG char(1) , -- 初始推播通知開關
INI_PUSH_HOUR1 char(1) , -- 初始推播時段一
INI_PUSH_HOUR2 char(1) , -- 初始推播時段二
INI_PUSH_HOUR3 char(1) , -- 初始推播時段三
NIGHT_SILENT char(1) , -- 夜間靜音
APP_VERSION varchar(30) , -- APP版本
DEVICE_VERSION varchar(30) , -- 手機OS版本
DEVICE_OS varchar(20) , -- 手機作業系統
DEVICE_BRAND varchar(30) , -- 手機廠牌型號
CREATE_TIME datetime NOT NULL, -- 建立時間
UPDATE_TIME datetime , -- 更新時間
OLD_USER_SEQ varchar(255) , -- 被置換的User
USER_CHANGE_TIME datetime , -- User置換的時間
CREATE_CH char(4) , -- 建立的Channel
UPDATE_CH char(4) , -- 更新的Channel
CONSTRAINT PUSH_DEVICE_PK PRIMARY KEY (DEVICE_UUID) -- Primary Key
);
-- Extended properties
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'使用者裝置', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手機序號', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'DEVICE_UUID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統代碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'SYS_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'推播碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'PUSH_TOKEN';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'綁定的使用者序號', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'USER_SEQ';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'初始推播通知開關', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'INI_PUSH_FLAG';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'初始推播時段一', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'INI_PUSH_HOUR1';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'初始推播時段二', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'INI_PUSH_HOUR2';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'初始推播時段三', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'INI_PUSH_HOUR3';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'夜間靜音', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'NIGHT_SILENT';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'APP版本', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'APP_VERSION';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手機OS版本', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'DEVICE_VERSION';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手機作業系統', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'DEVICE_OS';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手機廠牌型號', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'DEVICE_BRAND';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'CREATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'UPDATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'被置換的User', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'OLD_USER_SEQ';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User置換的時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'USER_CHANGE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立的Channel', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'CREATE_CH';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新的Channel', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_DEVICE', @level2type=N'Column', @level2name=N'UPDATE_CH';
如果有其他需求或需要調整的地方,隨時告訴我!
PUSH_ITEM_CLASSIFY 通知項目大項分類
Name Type Nullable Index Comments
SYS_UID varchar(4) PK 系統代碼
ITEM_CLASSIFY_UID char(3) PK 通知項目大項分類ID
ITEM_CLASSIFY_NAME varchar(50) 通知項目大項分類名稱
這是 PUSH_ITEM_CLASSIFY
表的DDL語句:
sql-- PUSH.dbo.PUSH_ITEM_CLASSIFY definition
-- Drop table if it exists
-- DROP TABLE PUSH.dbo.PUSH_ITEM_CLASSIFY;
CREATE TABLE PUSH.dbo.PUSH_ITEM_CLASSIFY (
SYS_UID varchar(4) NOT NULL, -- 系統代碼
ITEM_CLASSIFY_UID char(3) NOT NULL, -- 通知項目大項分類ID
ITEM_CLASSIFY_NAME varchar(50) NOT NULL, -- 通知項目大項分類名稱
CONSTRAINT PUSH_ITEM_CLASSIFY_PK PRIMARY KEY (SYS_UID, ITEM_CLASSIFY_UID) -- Primary Key
);
-- Extended properties
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'通知項目大項分類', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM_CLASSIFY';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統代碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM_CLASSIFY', @level2type=N'Column', @level2name=N'SYS_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'通知項目大項分類ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM_CLASSIFY', @level2type=N'Column', @level2name=N'ITEM_CLASSIFY_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'通知項目大項分類名稱', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM_CLASSIFY', @level2type=N'Column', @level2name=N'ITEM_CLASSIFY_NAME';
如果有任何其他要求或需要更多幫助,隨時告訴我!
PUSH_ITEM 訊息項目
Name Type Nullable Index Comments
SYS_UID varchar(4) PK 系統代碼
ITEM_UID varchar(7) PK 訊息項目ID
ITEM_NAME varchar(50) 訊息項目名稱
ITEM_CLASSIFY_UID char(3) 通知項目大項分類ID
IS_DISPLAY char(1) 是否顯示此訊息項目
CREATE_TIME datetime 建立時間
UPDATE_TIME datetime V 更新時間
CREATE_USER varchar(100) 建立使用者
UPDATE_USER varchar(100) V 更新使用者
ITEM_TYPE char(1) 訊息項目類型
EMAIL_REMIND char(1) 推播失敗郵件通知
PHONE_REMIND char(1) 推播失敗簡訊通知
PUSH_FLAG char(1) 初始推播通知開關
這是 PUSH_ITEM
表的DDL語句:
sql-- PUSH.dbo.PUSH_ITEM definition
-- Drop table if it exists
-- DROP TABLE PUSH.dbo.PUSH_ITEM;
CREATE TABLE PUSH.dbo.PUSH_ITEM (
SYS_UID varchar(4) NOT NULL, -- 系統代碼
ITEM_UID varchar(7) NOT NULL, -- 訊息項目ID
ITEM_NAME varchar(50) NOT NULL, -- 訊息項目名稱
ITEM_CLASSIFY_UID char(3) , -- 通知項目大項分類ID
IS_DISPLAY char(1) , -- 是否顯示此訊息項目
CREATE_TIME datetime , -- 建立時間
UPDATE_TIME datetime , -- 更新時間
CREATE_USER varchar(100) , -- 建立使用者
UPDATE_USER varchar(100) , -- 更新使用者
ITEM_TYPE char(1) , -- 訊息項目類型
EMAIL_REMIND char(1) , -- 推播失敗郵件通知
PHONE_REMIND char(1) , -- 推播失敗簡訊通知
PUSH_FLAG char(1) , -- 初始推播通知開關
CONSTRAINT PUSH_ITEM_PK PRIMARY KEY (SYS_UID, ITEM_UID) -- Primary Key
);
-- Extended properties
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'訊息項目', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統代碼', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'SYS_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'訊息項目ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'ITEM_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'訊息項目名稱', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'ITEM_NAME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'通知項目大項分類ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'ITEM_CLASSIFY_UID';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否顯示此訊息項目', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'IS_DISPLAY';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'CREATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新時間', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'UPDATE_TIME';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立使用者', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'CREATE_USER';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新使用者', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'UPDATE_USER';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'訊息項目類型', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'ITEM_TYPE';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'推播失敗郵件通知', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'EMAIL_REMIND';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'推播失敗簡訊通知', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'PHONE_REMIND';
EXEC PUSH.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'初始推播通知開關', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'PUSH_ITEM', @level2type=N'Column', @level2name=N'PUSH_FLAG';
如果還有其他表格或需要進一步調整,隨時告訴我!