1.sql

来自「配置数据库参数 采用VB6.0 ADO+SQL Server 2000数据库实」· SQL 代码 · 共 197 行

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Spoilage_Buy]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Spoilage] DROP CONSTRAINT FK_Spoilage_Buy
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Buy_Goods]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Buy] DROP CONSTRAINT FK_Buy_Goods
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Sale_Goods]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Sale] DROP CONSTRAINT FK_Sale_Goods
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Goods_GoodsType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Goods] DROP CONSTRAINT FK_Goods_GoodsType
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Goods_Supplier]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Goods] DROP CONSTRAINT FK_Goods_Supplier
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Buy_UserInfo]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Buy] DROP CONSTRAINT FK_Buy_UserInfo
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Sale_UserInfo]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Sale] DROP CONSTRAINT FK_Sale_UserInfo
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Spoilage_UserInfo]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Spoilage] DROP CONSTRAINT FK_Spoilage_UserInfo
GO

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

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

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

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

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

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

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

CREATE TABLE [dbo].[Buy] (
	[BuyID] [gxcID] IDENTITY (1, 1) NOT NULL ,
	[GoodsID] [gxcID] NOT NULL ,
	[Amount] [decimal](18, 2) NOT NULL ,
	[UnitPrice] [gxcMoney] NOT NULL ,
	[Deliverer] [gxcTrueName] NOT NULL ,
	[Transactor] [gxcTrueName] NOT NULL ,
	[RegistrarID] [gxcID] NOT NULL ,
	[RegDate] [datetime] NOT NULL ,
	[Remark] [gxcRemark] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Goods] (
	[GoodsID] [gxcID] IDENTITY (1, 1) NOT NULL ,
	[GoodsName] [gxcCaption] NOT NULL ,
	[Amount] [decimal](18, 2) NOT NULL ,
	[UnitName] [gxcCaption] NOT NULL ,
	[TypeID] [gxcID] NOT NULL ,
	[SupplierID] [gxcID] NOT NULL ,
	[Introduce] [gxcRemark] NULL ,
	[Remark] [gxcRemark] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[GoodsType] (
	[TypeID] [gxcID] IDENTITY (1, 1) NOT NULL ,
	[TypeName] [gxcCaption] NOT NULL ,
	[Remark] [gxcRemark] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Sale] (
	[SaleID] [gxcID] IDENTITY (1, 1) NOT NULL ,
	[GoodsID] [gxcID] NOT NULL ,
	[Amount] [decimal](18, 2) NOT NULL ,
	[UnitPrice] [gxcMoney] NOT NULL ,
	[RegistrarID] [gxcID] NOT NULL ,
	[RegDate] [datetime] NOT NULL ,
	[Remark] [gxcRemark] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Spoilage] (
	[SpoilageID] [gxcID] IDENTITY (1, 1) NOT NULL ,
	[BuyID] [gxcID] NOT NULL ,
	[Amount] [decimal](18, 2) NOT NULL ,
	[Reportor] [gxcTrueName] NOT NULL ,
	[Reason] [gxcRemark] NOT NULL ,
	[RegistrarID] [gxcID] NOT NULL ,
	[RegDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Supplier] (
	[SupplierID] [gxcID] IDENTITY (1, 1) NOT NULL ,
	[SupplierName] [gxcCaption] NOT NULL ,
	[Contact] [gxcRemark] NULL ,
	[Introduce] [gxcRemark] NULL ,
	[Remark] [gxcRemark] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserInfo] (
	[UserID] [gxcID] IDENTITY (1, 1) NOT NULL ,
	[UserName] [gxcCaption] NOT NULL ,
	[Password] [gxcCaption] NOT NULL ,
	[TrueName] [gxcTrueName] NOT NULL ,
	[LastLoginTime] [datetime] NULL ,
	[UserType] [bit] NOT NULL 
) ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[RemarkDefault]', N'[Buy].[Remark]'
GO

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[RemarkDefault]', N'[Goods].[Introduce]'
GO

EXEC sp_bindefault N'[dbo].[RemarkDefault]', N'[Goods].[Remark]'
GO

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[RemarkDefault]', N'[GoodsType].[Remark]'
GO

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[RemarkDefault]', N'[Sale].[Remark]'
GO

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[RemarkDefault]', N'[Spoilage].[Reason]'
GO

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[RemarkDefault]', N'[Supplier].[Contact]'
GO

EXEC sp_bindefault N'[dbo].[RemarkDefault]', N'[Supplier].[Introduce]'
GO

EXEC sp_bindefault N'[dbo].[RemarkDefault]', N'[Supplier].[Remark]'
GO

setuser
GO