table.sql

foxpro连接sqlserver的例子

-- 商品销售发票
-- 表.脚本
-- 红虎于2002.12.14

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

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

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

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

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

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

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

CREATE TABLE [dbo].[CustMast] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustName] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CustAddress] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[CustTelephone] [char] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[InvoiceItemList] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[InvoiceID] [int] NOT NULL ,
	[ItemID] [int] NOT NULL ,
	[InvoicePrice] [money] NOT NULL ,
	[InvoiceQty] [money] NOT NULL ,
	[InvoiceAmount] [money] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[InvoiceOrder] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[InvoiceNo] [char] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[InvoiceDate] [smalldatetime] NULL ,
	[CustID] [int] NULL ,
	[Notes] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[UserID] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ItemMast] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[ItemTypeID] [int] NOT NULL ,
	[ItemNo] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ItemFullName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ItemModel] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ItemFrom] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ItemPrice] [money] NOT NULL ,
	[ItemUnit] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ItemType] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[ParentID] [int] NOT NULL ,
	[ItemTypeName] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SystemData] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Item] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Value] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Password] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Rights] [text] COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[CustMast] WITH NOCHECK ADD 
	CONSTRAINT [PK_CustMast] PRIMARY KEY  CLUSTERED 
	(
		[CustName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[InvoiceOrder] WITH NOCHECK ADD 
	CONSTRAINT [PK_InvoiceOrder] PRIMARY KEY  CLUSTERED 
	(
		[InvoiceNo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ItemMast] WITH NOCHECK ADD 
	CONSTRAINT [DF_ItemMast_ItemTypeID] DEFAULT (0) FOR [ItemTypeID],
	CONSTRAINT [DF_ItemMast_ItemNo] DEFAULT ('') FOR [ItemNo],
	CONSTRAINT [DF_ItemMast_ItemFullName] DEFAULT ('') FOR [ItemFullName],
	CONSTRAINT [DF_ItemMast_ItemModel] DEFAULT ('') FOR [ItemModel],
	CONSTRAINT [DF_ItemMast_ItemEasyName] DEFAULT ('') FOR [ItemFrom],
	CONSTRAINT [DF_ItemMast_PricePerKG] DEFAULT (0) FOR [ItemPrice],
	CONSTRAINT [DF_ItemMast_UnitName] DEFAULT ('') FOR [ItemUnit]
GO

ALTER TABLE [dbo].[ItemType] WITH NOCHECK ADD 
	CONSTRAINT [DF_ItemType_ParentID] DEFAULT (0) FOR [ParentID]
GO

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD 
	CONSTRAINT [DF_Users_UserName] DEFAULT ('') FOR [UserName],
	CONSTRAINT [DF_Users_Password] DEFAULT ('') FOR [Password],
	CONSTRAINT [DF_Users_Rights] DEFAULT ('') FOR [Rights]
GO