fax.sql

自动传真系统

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'FAXSRV')
	DROP DATABASE [FAXSRV]
GO

CREATE DATABASE [FAXSRV]  ON (NAME = N'FAXSRV_Data', FILENAME = N'e:\Microsoft SQL Server\MSSQL\data\FAXSRV_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'FAXSRV_Log', FILENAME = N'e:\Microsoft SQL Server\MSSQL\data\FAXSRV_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS
GO

exec sp_dboption N'FAXSRV', N'autoclose', N'false'
GO

exec sp_dboption N'FAXSRV', N'bulkcopy', N'false'
GO

exec sp_dboption N'FAXSRV', N'trunc. log', N'false'
GO

exec sp_dboption N'FAXSRV', N'torn page detection', N'true'
GO

exec sp_dboption N'FAXSRV', N'read only', N'false'
GO

exec sp_dboption N'FAXSRV', N'dbo use', N'false'
GO

exec sp_dboption N'FAXSRV', N'single', N'false'
GO

exec sp_dboption N'FAXSRV', N'autoshrink', N'false'
GO

exec sp_dboption N'FAXSRV', N'ANSI null default', N'false'
GO

exec sp_dboption N'FAXSRV', N'recursive triggers', N'false'
GO

exec sp_dboption N'FAXSRV', N'ANSI nulls', N'false'
GO

exec sp_dboption N'FAXSRV', N'concat null yields null', N'false'
GO

exec sp_dboption N'FAXSRV', N'cursor close on commit', N'false'
GO

exec sp_dboption N'FAXSRV', N'default to local cursor', N'false'
GO

exec sp_dboption N'FAXSRV', N'quoted identifier', N'false'
GO

exec sp_dboption N'FAXSRV', N'ANSI warnings', N'false'
GO

exec sp_dboption N'FAXSRV', N'auto create statistics', N'true'
GO

exec sp_dboption N'FAXSRV', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
	exec sp_dboption N'FAXSRV', N'db chaining', N'false'
GO

use [FAXSRV]
GO

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

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

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

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

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

CREATE TABLE [dbo].[Fax_Bill] (
	[userid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[faxdoc] [varchar] (256) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[faxto] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[sendtime] [datetime] NULL ,
	[callcount] [smallint] NULL ,
	[notice] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Fax_Error] (
	[userid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[faxdoc] [varchar] (256) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[faxto] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[sendtime] [datetime] NULL ,
	[error] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[notice] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Fax_Recv] (
	[userid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[faxfrom] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[faxdoc] [varchar] (256) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[recvtime] [datetime] NULL ,
	[notice] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[state] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Fax_UserId] (
	[userid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[username] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[usermail] [varchar] (80) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[userright] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Fax_send] (
	[userid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[faxto] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[faxdoc] [varchar] (256) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[state] [smallint] NULL ,
	[callcount] [smallint] NULL ,
	[recvtime] [datetime] NULL ,
	[sendtime] [datetime] NULL ,
	[errorid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO