/****** Object: Database testdb Script Date: 16.12.2002 11:48:02 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'testdb')
DROP DATABASE [testdb]
GO
CREATE DATABASE [testdb] ON (NAME = N'testdb_Data', FILENAME = N'C:\testdb_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'testdb_Log', FILENAME = N'C:\testdb_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE Cyrillic_General_CI_AS
GO
exec sp_dboption N'testdb', N'autoclose', N'false'
GO
exec sp_dboption N'testdb', N'bulkcopy', N'false'
GO
exec sp_dboption N'testdb', N'trunc. log', N'false'
GO
exec sp_dboption N'testdb', N'torn page detection', N'true'
GO
exec sp_dboption N'testdb', N'read only', N'false'
GO
exec sp_dboption N'testdb', N'dbo use', N'false'
GO
exec sp_dboption N'testdb', N'single', N'false'
GO
exec sp_dboption N'testdb', N'autoshrink', N'false'
GO
exec sp_dboption N'testdb', N'ANSI null default', N'false'
GO
exec sp_dboption N'testdb', N'recursive triggers', N'false'
GO
exec sp_dboption N'testdb', N'ANSI nulls', N'false'
GO
exec sp_dboption N'testdb', N'concat null yields null', N'false'
GO
exec sp_dboption N'testdb', N'cursor close on commit', N'false'
GO
exec sp_dboption N'testdb', N'default to local cursor', N'false'
GO
exec sp_dboption N'testdb', N'quoted identifier', N'false'
GO
exec sp_dboption N'testdb', N'ANSI warnings', N'false'
GO
exec sp_dboption N'testdb', N'auto create statistics', N'true'
GO
exec sp_dboption N'testdb', N'auto update statistics', N'true'
GO
use [testdb]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ext_Supply_Clients]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Ext_Supply] DROP CONSTRAINT FK_ext_Supply_Clients
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Ext_Supply_Items_ext_Supply]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Ext_Supply_Items] DROP CONSTRAINT FK_Ext_Supply_Items_ext_Supply
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Int_Supply_Items_Ext_Supply_Items]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Int_Supply_Items] DROP CONSTRAINT FK_Int_Supply_Items_Ext_Supply_Items
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Int_Supply_Items_Int_supply]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Int_Supply_Items] DROP CONSTRAINT FK_Int_Supply_Items_Int_supply
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Ext_Supply_Items_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Ext_Supply_Items] DROP CONSTRAINT FK_Ext_Supply_Items_Products
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Int_Supply_Items_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Int_Supply_Items] DROP CONSTRAINT FK_Int_Supply_Items_Products
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Int_supply_Shops]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Int_supply] DROP CONSTRAINT FK_Int_supply_Shops
GO
/****** Object: Table [dbo].[Clients] Script Date: 16.12.2002 11:48:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Clients]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Clients]
GO
/****** Object: Table [dbo].[Ext_Supply] Script Date: 16.12.2002 11:48:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ext_Supply]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Ext_Supply]
GO
/****** Object: Table [dbo].[Ext_Supply_Items] Script Date: 16.12.2002 11:48:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ext_Supply_Items]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Ext_Supply_Items]
GO
/****** Object: Table [dbo].[Int_Supply_Items] Script Date: 16.12.2002 11:48:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Int_Supply_Items]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Int_Supply_Items]
GO
/****** Object: Table [dbo].[Int_supply] Script Date: 16.12.2002 11:48:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Int_supply]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Int_supply]
GO
/****** Object: Table [dbo].[Products] Script Date: 16.12.2002 11:48:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Products]
GO
/****** Object: Table [dbo].[Shops] Script Date: 16.12.2002 11:48:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Shops]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Shops]
GO
/****** Object: Table [dbo].[Clients] Script Date: 16.12.2002 11:48:10 ******/
CREATE TABLE [dbo].[Clients] (
[CLient_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Client_Name] [varchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Ext_Supply] Script Date: 16.12.2002 11:48:11 ******/
CREATE TABLE [dbo].[Ext_Supply] (
[Supply_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Supply_No] [varchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Client_ID] [int] NOT NULL ,
[Date_supply] [datetime] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Ext_Supply_Items] Script Date: 16.12.2002 11:48:12 ******/
CREATE TABLE [dbo].[Ext_Supply_Items] (
[Supply_item_id] [int] IDENTITY (1, 1) NOT NULL ,
[Supply_Id] [int] NOT NULL ,
[Product_id] [int] NOT NULL ,
[Price] [money] NOT NULL ,
[Qty] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Int_Supply_Items] Script Date: 16.12.2002 11:48:12 ******/
CREATE TABLE [dbo].[Int_Supply_Items] (
[Supply_Item_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Supply_ID] [int] NOT NULL ,
[Product_ID] [int] NOT NULL ,
[Price] [money] NOT NULL ,
[Qty] [int] NOT NULL ,
[Ext_Supply_Item_no] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Int_supply] Script Date: 16.12.2002 11:48:13 ******/
CREATE TABLE [dbo].[Int_supply] (
[Supply_Id] [int] IDENTITY (1, 1) NOT NULL ,
[Supply_no] [varchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Shop_ID] [int] NOT NULL ,
[Supply_Date] [datetime] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Products] Script Date: 16.12.2002 11:48:13 ******/
CREATE TABLE [dbo].[Products] (
[Product_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Product_name] [varchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Shops] Script Date: 16.12.2002 11:48:14 ******/
CREATE TABLE [dbo].[Shops] (
[Shop_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Shop_Name] [varchar] (50) COLLATE Cyrillic_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Clients] WITH NOCHECK ADD
CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
(
[CLient_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ext_Supply] WITH NOCHECK ADD
CONSTRAINT [PK_ext_Supply] PRIMARY KEY CLUSTERED
(
[Supply_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ext_Supply_Items] WITH NOCHECK ADD
CONSTRAINT [PK_Ext_Supply_Items] PRIMARY KEY CLUSTERED
(
[Supply_item_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Int_supply] WITH NOCHECK ADD
CONSTRAINT [PK_Int_supply] PRIMARY KEY CLUSTERED
(
[Supply_Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Products] WITH NOCHECK ADD
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[Product_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Shops] WITH NOCHECK ADD
CONSTRAINT [PK_Shops] PRIMARY KEY CLUSTERED
(
[Shop_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ext_Supply] ADD
CONSTRAINT [FK_ext_Supply_Clients] FOREIGN KEY
(
[Client_ID]
) REFERENCES [dbo].[Clients] (
[CLient_ID]
)
GO
ALTER TABLE [dbo].[Ext_Supply_Items] ADD
CONSTRAINT [FK_Ext_Supply_Items_ext_Supply] FOREIGN KEY
(
[Supply_Id]
) REFERENCES [dbo].[Ext_Supply] (
[Supply_ID]
),
CONSTRAINT [FK_Ext_Supply_Items_Products] FOREIGN KEY
(
[Product_id]
) REFERENCES [dbo].[Products] (
[Product_ID]
)
GO
ALTER TABLE [dbo].[Int_Supply_Items] ADD
CONSTRAINT [FK_Int_Supply_Items_Ext_Supply_Items] FOREIGN KEY
(
[Ext_Supply_Item_no]
) REFERENCES [dbo].[Ext_Supply_Items] (
[Supply_item_id]
),
CONSTRAINT [FK_Int_Supply_Items_Int_supply] FOREIGN KEY
(
[Supply_ID]
) REFERENCES [dbo].[Int_supply] (
[Supply_Id]
),
CONSTRAINT [FK_Int_Supply_Items_Products] FOREIGN KEY
(
[Product_ID]
) REFERENCES [dbo].[Products] (
[Product_ID]
)
GO
ALTER TABLE [dbo].[Int_supply] ADD
CONSTRAINT [FK_Int_supply_Shops] FOREIGN KEY
(
[Shop_ID]
) REFERENCES [dbo].[Shops] (
[Shop_ID]
)
GO
exec sp_addextendedproperty N'MS_Description', N'Код поставщика', N'user', N'dbo', N'table', N'Clients', N'column', N'CLient_ID'
GO
exec sp_addextendedproperty N'MS_Description', N'Название поставщика', N'user', N'dbo', N'table', N'Clients', N'column', N'Client_Name'
GO
exec sp_addextendedproperty N'MS_Description', N'Код поставщика', N'user', N'dbo', N'table', N'Ext_Supply', N'column', N'Client_ID'
GO
exec sp_addextendedproperty N'MS_Description', N'Дата накладной', N'user', N'dbo', N'table', N'Ext_Supply', N'column', N'Date_supply'
GO
exec sp_addextendedproperty N'MS_Description', N'Код приходной накладной', N'user', N'dbo', N'table', N'Ext_Supply', N'column', N'Supply_ID'
GO
exec sp_addextendedproperty N'MS_Description', N'Номер накладной', N'user', N'dbo', N'table', N'Ext_Supply', N'column', N'Supply_No'
GO
exec sp_addextendedproperty N'MS_Description', N'Цена товара', N'user', N'dbo', N'table', N'Ext_Supply_Items', N'column', N'Price'
GO
exec sp_addextendedproperty N'MS_Description', N'Код товара', N'user', N'dbo', N'table', N'Ext_Supply_Items', N'column', N'Product_id'
GO
exec sp_addextendedproperty N'MS_Description', N'Количество товара', N'user', N'dbo', N'table', N'Ext_Supply_Items', N'column', N'Qty'
GO
exec sp_addextendedproperty N'MS_Description', N'Код приходной накладной', N'user', N'dbo', N'table', N'Ext_Supply_Items', N'column', N'Supply_Id'
GO
exec sp_addextendedproperty N'MS_Description', N'Код позиции накладной', N'user', N'dbo', N'table', N'Ext_Supply_Items', N'column', N'Supply_item_id'
GO
exec sp_addextendedproperty N'MS_Description', N'Код позиции приходной накладной, по которой пришел этот товар', N'user', N'dbo', N'table', N'Int_Supply_Items', N'column', N'Ext_Supply_Item_no'
GO
exec sp_addextendedproperty N'MS_Description', N'Цена товара', N'user', N'dbo', N'table', N'Int_Supply_Items', N'column', N'Price'
GO
exec sp_addextendedproperty N'MS_Description', N'Код товара', N'user', N'dbo', N'table', N'Int_Supply_Items', N'column', N'Product_ID'
GO
exec sp_addextendedproperty N'MS_Description', N'Количество товара', N'user', N'dbo', N'table', N'Int_Supply_Items', N'column', N'Qty'
GO
exec sp_addextendedproperty N'MS_Description', N'Код расходной накладной', N'user', N'dbo', N'table', N'Int_Supply_Items', N'column', N'Supply_ID'
GO
exec sp_addextendedproperty N'MS_Description', N'Код позиции расходной накладной', N'user', N'dbo', N'table', N'Int_Supply_Items', N'column', N'Supply_Item_ID'
GO
exec sp_addextendedproperty N'MS_Description', N'Код получателя накладной', N'user', N'dbo', N'table', N'Int_supply', N'column', N'Shop_ID'
GO
exec sp_addextendedproperty N'MS_Description', N'Дата накладной', N'user', N'dbo', N'table', N'Int_supply', N'column', N'Supply_Date'
GO
exec sp_addextendedproperty N'MS_Description', N'Код расходной накладной', N'user', N'dbo', N'table', N'Int_supply', N'column', N'Supply_Id'
GO
exec sp_addextendedproperty N'MS_Description', N'Номер накладной', N'user', N'dbo', N'table', N'Int_supply', N'column', N'Supply_no'
GO
exec sp_addextendedproperty N'MS_Description', N'Код товара', N'user', N'dbo', N'table', N'Products', N'column', N'Product_ID'
GO
exec sp_addextendedproperty N'MS_Description', N'Название товара', N'user', N'dbo', N'table', N'Products', N'column', N'Product_name'
GO
exec sp_addextendedproperty N'MS_Description', N'Код получателя накладной', N'user', N'dbo', N'table', N'Shops', N'column', N'Shop_ID'
GO
exec sp_addextendedproperty N'MS_Description', N'Название получателя накладной', N'user', N'dbo', N'table', N'Shops', N'column', N'Shop_Name'
GO
|