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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE [dbo].[job_tab] (
	[job_id] [int] IDENTITY (1, 1) NOT NULL ,
	[job_userid] [int] NOT NULL ,
	[job_title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[job_type] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[job_company] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_salary] [nchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[job_industry] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_attrib] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_region] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[job_contact_man] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_contact_phone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_contact_fax] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_contact_address] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_contact_zipcode] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_contact_email] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[job_contact_http] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_info] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[job_company_info] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[job_end_date] [datetime] NOT NULL ,
	[job_deliver_date] [datetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[customer_mails] (
	[id] [int] NOT NULL ,
	[retailerId] [int] NULL ,
	[uid] [int] NULL ,
	[mailSubject] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
	[mailBody] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[mailOperation] [int] NULL ,
	[MailFrom] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[mailTo] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[sender] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[addtime] [datetime] NULL ,
	[attachment] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
	[mailorphone] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[freight_docs] (
	[freight_docs_id] [int] IDENTITY (11000, 1) NOT NULL ,
	[freight_docs_cat1] [char] (4) COLLATE Chinese_PRC_CI_AS_WS NOT NULL ,
	[freight_docs_cat2] [char] (4) COLLATE Chinese_PRC_CI_AS_WS NOT NULL ,
	[freight_docs_title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS_WS NOT NULL ,
	[freight_docs_title_keyword] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[freight_docs_date] [datetime] NOT NULL ,
	[freight_docs_body] [text] COLLATE Chinese_PRC_CI_AS_WS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[freight_docs_cat] (
	[cat_code] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[cat_value] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS_WS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[job_seeker_tab] (
	[job_seeker_id] [int] IDENTITY (1, 1) NOT NULL ,
	[job_seeker_userid] [int] NOT NULL ,
	[job_seeker_realname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[job_seeker_email] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[job_seeker_country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_province] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_city] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_phone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_address] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_zipcode] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_gender] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_http] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_birthday] [datetime] NULL ,
	[job_seeker_height] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_work_years] [int] NULL ,
	[job_seeker_special] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_college] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_major] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_minor] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_computer_skill] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_education] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_language1] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_ability1] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_language2] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_ability2] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_work_region] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_work_salary] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_work_position1] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_work_company1] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_work_company2] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_work_position2] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_work_position3] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_work_company3] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_edu_back] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_resume] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[job_seeker_isok] [bit] NOT NULL ,
	[job_seeker_clicks] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[members] (
	[user_id] [int] IDENTITY (1, 1) NOT NULL ,
	[loginname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS_WS NOT NULL ,
	[passwd] [nvarchar] (50) COLLATE Chinese_PRC_CS_AS_WS NOT NULL ,
	[email] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[realname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[gender] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[company] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[biz_type] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[attrib] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[address] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[zipcode] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[phone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[fax] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[province] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[city] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[vip] [int] NOT NULL ,
	[vip_type] [int] NOT NULL ,
	[registerDate] [datetime] NOT NULL ,
	[http] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[memo] [text] COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[members_log] (
	[log_id] [int] IDENTITY (1, 1) NOT NULL ,
	[log_loginname] [nvarchar] (50) COLLATE Chinese_PRC_CS_AS_WS NOT NULL ,
	[log_date] [datetime] NOT NULL ,
	[log_ip] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[messages] (
	[id] [int] NOT NULL ,
	[senderId] [int] NULL ,
	[sender] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[receiverId] [int] NULL ,
	[receiver] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[msgs] [varchar] (500) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[stime] [datetime] NOT NULL ,
	[isread] [tinyint] NOT NULL ,
	[isdeleted] [tinyint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[news] (
	[news_id] [int] IDENTITY (1, 1) NOT NULL ,
	[news_title] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[news_body] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
	[news_keyword] [nvarchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
	[news_source] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[news_author] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[news_special] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[news_hot] [int] NOT NULL ,
	[news_type] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[news_date] [datetime] NOT NULL ,
	[news_click] [int] NOT NULL ,
	[news_pic] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[news_isok] [bit] NOT NULL ,
	[news_right] [int] NOT NULL ,
	[news_isdeleted] [bit] NOT NULL ,
	[news_actor] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[oaCalendar] (
	[id] [int] NOT NULL ,
	[userid] [int] NOT NULL ,
	[sDate] [datetime] NULL ,
	[EndDate] [datetime] NULL ,
	[body] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
	[isdone] [tinyint] NOT NULL ,
	[pri] [tinyint] NOT NULL ,
	[everyday] [tinyint] NOT NULL ,
	[everyweek] [tinyint] NULL ,
	[dayofweek] [tinyint] NULL ,
	[hours] [datetime] NULL ,
	[uid] [int] NULL ,
	[retailerid] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[oaLogs] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[loginname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[date] [datetime] NULL ,
	[ip] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[url] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[query] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[oaNameCard] (
	[id] [int] NOT NULL ,
	[userid] [int] NOT NULL ,
	[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[company] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[position] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[phone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[fax] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[mobile] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[pager] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[address] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[zipcode] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[email] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[http] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[oaPris] (
	[code] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[value] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[detail] [nvarchar] (250) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[province] (
	[code] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[cnvalue] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[envalue] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ship_tab] (
	[ship_id] [int] IDENTITY (1, 1) NOT NULL ,
	[ship_port] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ship_line] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ship_mode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ship_title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ship_memo] [text] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ship_date_end] [datetime] NOT NULL ,
	[ship_deliver] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ship_deliver_date] [datetime] NOT NULL ,
	[ship_userid] [int] NOT NULL ,
	[ship_vehicle_pic] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[ship_vehicle_country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ship_isok] [bit] NOT NULL ,
	[ship_isdeleted] [bit] NOT NULL ,
	[ship_deliver_modify_date] [datetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[trade_biz] (
	[biz_id] [int] IDENTITY (1, 1) NOT NULL ,
	[biz_trade_id] [int] NOT NULL ,
	[biz_trade_submit_userid] [int] NOT NULL ,
	[biz_trade_intent_userid] [int] NOT NULL ,
	[biz_body] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[biz_read] [bit] NOT NULL ,
	[biz_date] [datetime] NOT NULL ,
	[biz_author_userid] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[trade_tab] (
	[trade_id] [int] IDENTITY (10000, 1) NOT NULL ,
	[trade_type] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[trade_name] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[trade_danger] [int] NULL ,
	[trade_danger_weiguihao] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_danger_yema] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_danger_dengji] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_ship_mode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_ship_line] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_start_port] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_volume] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_weight] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_containertype] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_start_country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_start_city] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_end_country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_end_city] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_start_date] [datetime] NULL ,
	[trade_end_date] [datetime] NULL ,
	[trade_ship_fee_payway] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_date_end] [datetime] NULL ,
	[trade_memo] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_deliver] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[trade_deliver_date] [datetime] NOT NULL ,
	[trade_userid] [int] NOT NULL ,
	[trade_isok] [bit] NOT NULL ,
	[trade_isdeleted] [bit] NOT NULL ,
	[trade_deliver_modify_date] [datetime] NOT NULL ,
	[trade_modify_date] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[userPris] (
	[userid] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[code] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[isAuth] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[AuthFrom] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[usersdb] (
	[userId] [int] IDENTITY (1, 1) NOT NULL ,
	[loginname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[passwd] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[nickname] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[email] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[realname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[idno] [char] (18) COLLATE Chinese_PRC_CI_AS NULL ,
	[gender] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[birthday] [datetime] NULL ,
	[degree] [int] NULL ,
	[position] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[dept] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[gid] [tinyint] NULL ,
	[marital] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[province] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[region] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[address] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[mobile] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[phone] [char] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[fax] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[postcode] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
	[addedtime] [datetime] NULL ,
	[lastlogin] [datetime] NULL ,
	[pri] [tinyint] NULL ,
	[isOK] [tinyint] NULL ,
	[lastloginip] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[icq] [char] (12) COLLATE Chinese_PRC_CI_AS NULL ,
	[oicq] [char] (12) COLLATE Chinese_PRC_CI_AS NULL ,
	[isdeleted] [int] NOT NULL ,
	[provinceList] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[yellowPages] (
	[yp_id] [int] IDENTITY (1, 1) NOT NULL ,
	[yp_company] [varchar] (150) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[yp_cindustry] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[yp_http] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
	[yp_body] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[yp_keyword] [varchar] (250) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[yp_logo] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
	[yp_phone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[yp_fax] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[yp_address] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[yp_zipcode] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[yp_province] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[yp_city] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[yp_addtime] [datetime] NULL ,
	[yp_uid] [int] NULL ,
	[yp_isok] [int] NOT NULL ,
	[yp_email] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[yp_isBest] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[job_tab] WITH NOCHECK ADD 
	CONSTRAINT [DF_job_tab_job_deliver_date] DEFAULT (getdate()) FOR [job_deliver_date]
GO

ALTER TABLE [dbo].[job_seeker_tab] WITH NOCHECK ADD 
	CONSTRAINT [DF_job_seeker_tab_job_seeker_work_years] DEFAULT (0) FOR [job_seeker_work_years],
	CONSTRAINT [DF_job_seeker_tab_job_seeker_isok] DEFAULT (1) FOR [job_seeker_isok],
	CONSTRAINT [DF_job_seeker_tab_job_seeker_clicks] DEFAULT (0) FOR [job_seeker_clicks]
GO

ALTER TABLE [dbo].[members] WITH NOCHECK ADD 
	CONSTRAINT [DF_members_vip] DEFAULT (0) FOR [vip]
GO

ALTER TABLE [dbo].[members_log] WITH NOCHECK ADD 
	CONSTRAINT [DF_members_log_log_date] DEFAULT (getdate()) FOR [log_date]
GO

ALTER TABLE [dbo].[news] WITH NOCHECK ADD 
	CONSTRAINT [DF_news_news_hot] DEFAULT (0) FOR [news_hot],
	CONSTRAINT [DF_news_news_click] DEFAULT (0) FOR [news_click],
	CONSTRAINT [DF_news_news_isok] DEFAULT (0) FOR [news_isok],
	CONSTRAINT [DF_news_news_right] DEFAULT (0) FOR [news_right],
	CONSTRAINT [DF_news_nesw_isdeleted] DEFAULT (0) FOR [news_isdeleted]
GO

ALTER TABLE [dbo].[ship_tab] WITH NOCHECK ADD 
	CONSTRAINT [DF_ship_tab_ship_isok] DEFAULT (0) FOR [ship_isok],
	CONSTRAINT [DF_ship_tab_ship_isdeleted] DEFAULT (0) FOR [ship_isdeleted],
	CONSTRAINT [DF_ship_tab_ship_deliver_modify_date] DEFAULT (getdate()) FOR [ship_deliver_modify_date]
GO

ALTER TABLE [dbo].[trade_biz] WITH NOCHECK ADD 
	CONSTRAINT [DF_trade_biz_biz_read] DEFAULT (0) FOR [biz_read],
	CONSTRAINT [DF_trade_biz_biz_date] DEFAULT (getdate()) FOR [biz_date]
GO

ALTER TABLE [dbo].[trade_tab] WITH NOCHECK ADD 
	CONSTRAINT [DF_trade_tab_trade_isok] DEFAULT (1) FOR [trade_isok],
	CONSTRAINT [DF_trade_tab_trade_isdeleted] DEFAULT (0) FOR [trade_isdeleted],
	CONSTRAINT [DF_trade_tab_trade_deliver_modify_date] DEFAULT (getdate()) FOR [trade_deliver_modify_date],
	CONSTRAINT [DF_trade_tab_trade_modify_date] DEFAULT (getdate()) FOR [trade_modify_date]
GO

ALTER TABLE [dbo].[usersdb] WITH NOCHECK ADD 
	CONSTRAINT [DF_usersdb_isdeleted] DEFAULT (0) FOR [isdeleted]
GO

 CREATE  INDEX [IX_freight_docs] ON [dbo].[freight_docs]([freight_docs_cat1], [freight_docs_cat2], [freight_docs_title]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_job_seeker_tab] ON [dbo].[job_seeker_tab]([job_seeker_id], [job_seeker_major], [job_seeker_work_region]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_trade_biz] ON [dbo].[trade_biz]([biz_id], [biz_trade_submit_userid], [biz_trade_intent_userid]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_trade_tab] ON [dbo].[trade_tab]([trade_id], [trade_type], [trade_name], [trade_date_end]) ON [PRIMARY]
GO

