make-tables.sql

ASP.NET Web Forms Techniques

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

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

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

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

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

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

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

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

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

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

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

/****** Object:  Stored Procedure dbo.GetUserOrderLines    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetUserOrderLines]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetUserOrderLines]
GO

/****** Object:  Stored Procedure dbo.InsertNewQuoteExtraLine    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertNewQuoteExtraLine]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertNewQuoteExtraLine]
GO

/****** Object:  Stored Procedure dbo.MigrateQuotes    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MigrateQuotes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MigrateQuotes]
GO

/****** Object:  Stored Procedure dbo.ConvertToOrder    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ConvertToOrder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ConvertToOrder]
GO

/****** Object:  Stored Procedure dbo.GetAllCarColors    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAllCarColors]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAllCarColors]
GO

/****** Object:  Stored Procedure dbo.GetAllCarDetails    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAllCarDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAllCarDetails]
GO

/****** Object:  Stored Procedure dbo.GetAllCarExtras    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAllCarExtras]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAllCarExtras]
GO

/****** Object:  Stored Procedure dbo.GetModelEngineCombinedList    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetModelEngineCombinedList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetModelEngineCombinedList]
GO

/****** Object:  Stored Procedure dbo.GetModelList    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetModelList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetModelList]
GO

/****** Object:  Stored Procedure dbo.GetUserOrders    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetUserOrders]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetUserOrders]
GO

/****** Object:  Stored Procedure dbo.InsertNewQuote    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertNewQuote]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertNewQuote]
GO

/****** Object:  Stored Procedure dbo.AddMailingListAddress    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddMailingListAddress]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddMailingListAddress]
GO

/****** Object:  Stored Procedure dbo.AuthenticateUser    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AuthenticateUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AuthenticateUser]
GO

/****** Object:  Stored Procedure dbo.GetFinancePMTRates    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetFinancePMTRates]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetFinancePMTRates]
GO

/****** Object:  Stored Procedure dbo.GetNewsItem    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsItem]
GO

/****** Object:  Stored Procedure dbo.GetNewsList    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsList]
GO

/****** Object:  Stored Procedure dbo.GetUserDetails    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetUserDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetUserDetails]
GO

/****** Object:  Stored Procedure dbo.InsertNewUser    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertNewUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertNewUser]
GO

/****** Object:  Stored Procedure dbo.UpdatePmtFinanceData    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdatePmtFinanceData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdatePmtFinanceData]
GO

/****** Object:  Table [dbo].[tblQuoteOrderExtras]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblQuoteOrderExtras]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblQuoteOrderExtras]
GO

/****** Object:  Table [dbo].[tblCarColors]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCarColors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCarColors]
GO

/****** Object:  Table [dbo].[tblCarEngines]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCarEngines]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCarEngines]
GO

/****** Object:  Table [dbo].[tblCarExtras]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCarExtras]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCarExtras]
GO

/****** Object:  Table [dbo].[tblQuoteOrder]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblQuoteOrder]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblQuoteOrder]
GO

/****** Object:  Table [dbo].[tblCar]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCar]
GO

/****** Object:  Table [dbo].[tblColor]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblColor]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblColor]
GO

/****** Object:  Table [dbo].[tblEngine]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblEngine]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblEngine]
GO

/****** Object:  Table [dbo].[tblFinancePMTData]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblFinancePMTData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblFinancePMTData]
GO

/****** Object:  Table [dbo].[tblMailingList]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblMailingList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblMailingList]
GO

/****** Object:  Table [dbo].[tblNews]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblNews]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblNews]
GO

/****** Object:  Table [dbo].[tblOptionExtra]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblOptionExtra]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblOptionExtra]
GO

/****** Object:  Table [dbo].[tblUsers]    Script Date: 13/08/2002 15:51:14 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUsers]
GO

/****** Object:  Login anon    Script Date: 13/08/2002 15:51:05 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'anon')
BEGIN
  declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'XroxCars', @loginlang = N'us_english'
  if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
    select @logindb = N'master'
  if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
    select @loginlang = @@language
  exec sp_addlogin N'anon', null, @logindb, @loginlang
END
GO

/****** Object:  User anon    Script Date: 13/08/2002 15:51:07 ******/
if not exists (select * from dbo.sysusers where name = N'anon' and uid < 16382)
  EXEC sp_grantdbaccess N'anon', N'anon'
GO

/****** Object:  Table [dbo].[tblCar]    Script Date: 13/08/2002 15:51:17 ******/
CREATE TABLE [dbo].[tblCar] (
  [CarID] [int] IDENTITY (1, 1) NOT NULL ,
  [Model] [varchar] (50) NULL ,
  [Precis] [varchar] (1024) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblColor]    Script Date: 13/08/2002 15:51:18 ******/
CREATE TABLE [dbo].[tblColor] (
  [ColorID] [int] IDENTITY (1, 1) NOT NULL ,
  [Color] [varchar] (50) NOT NULL ,
  [IsMetallic] [bit] NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblEngine]    Script Date: 13/08/2002 15:51:18 ******/
CREATE TABLE [dbo].[tblEngine] (
  [EngineID] [int] IDENTITY (1, 1) NOT NULL ,
  [EngineName] [varchar] (10) NULL ,
  [CubicCapacity] [int] NULL ,
  [FuelType] [varchar] (20) NULL ,
  [Cylinders] [smallint] NULL ,
  [Turbo] [bit] NULL ,
  [MaxRPM] [int] NULL ,
  [BHP] [smallint] NULL ,
  [Gears] [smallint] NULL ,
  [AutoTrans] [bit] NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblFinancePMTData]    Script Date: 13/08/2002 15:51:19 ******/
CREATE TABLE [dbo].[tblFinancePMTData] (
  [Months] [int] NOT NULL ,
  [Payment] [decimal](9, 2) NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblMailingList]    Script Date: 13/08/2002 15:51:19 ******/
CREATE TABLE [dbo].[tblMailingList] (
  [EmailAddr] [varchar] (255) NOT NULL ,
  [DateAdded] [datetime] NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblNews]    Script Date: 13/08/2002 15:51:19 ******/
CREATE TABLE [dbo].[tblNews] (
  [NewsID] [int] IDENTITY (1, 1) NOT NULL ,
  [NewsTitle] [varchar] (50) NULL ,
  [NewsPrecis] [varchar] (255) NULL ,
  [NewsText] [varchar] (7000) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblOptionExtra]    Script Date: 13/08/2002 15:51:19 ******/
CREATE TABLE [dbo].[tblOptionExtra] (
  [ExtraID] [int] IDENTITY (1, 1) NOT NULL ,
  [ExtraText] [varchar] (50) NOT NULL ,
  [ExtraPrice] [money] NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblUsers]    Script Date: 13/08/2002 15:51:19 ******/
CREATE TABLE [dbo].[tblUsers] (
  [UserID] [varchar] (20) NOT NULL ,
  [UserPW] [varchar] (255) NOT NULL ,
  [UserName] [varchar] (50) NOT NULL ,
  [Address] [varchar] (60) NULL ,
  [City] [varchar] (30) NULL ,
  [State] [varchar] (20) NULL ,
  [Country] [varchar] (30) NULL ,
  [Phone] [varchar] (20) NULL ,
  [Email] [varchar] (50) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblCarColors]    Script Date: 13/08/2002 15:51:20 ******/
CREATE TABLE [dbo].[tblCarColors] (
  [CarID] [int] NOT NULL ,
  [ColorID] [int] NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblCarEngines]    Script Date: 13/08/2002 15:51:20 ******/
CREATE TABLE [dbo].[tblCarEngines] (
  [CarID] [int] NOT NULL ,
  [EngineID] [int] NOT NULL ,
  [Perf30] [real] NULL ,
  [Perf40] [real] NULL ,
  [Perf50] [real] NULL ,
  [Perf60] [real] NULL ,
  [Perf70] [real] NULL ,
  [Perf80] [real] NULL ,
  [Perf90] [real] NULL ,
  [Perf100] [real] NULL ,
  [Speed1st] [smallint] NULL ,
  [Speed2nd] [smallint] NULL ,
  [Speed3rd] [smallint] NULL ,
  [Speed4th] [smallint] NULL ,
  [Speed5th] [smallint] NULL ,
  [Speed6th] [smallint] NULL ,
  [Fuel30] [smallint] NULL ,
  [Fuel40] [smallint] NULL ,
  [Fuel50] [smallint] NULL ,
  [Fuel60] [smallint] NULL ,
  [Fuel70] [smallint] NULL ,
  [Fuel80] [smallint] NULL ,
  [Fuel90] [smallint] NULL ,
  [Fuel100] [smallint] NULL ,
  [CarEnginePrice] [money] NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblCarExtras]    Script Date: 13/08/2002 15:51:20 ******/
CREATE TABLE [dbo].[tblCarExtras] (
  [CarID] [int] NOT NULL ,
  [ExtraID] [int] NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblQuoteOrder]    Script Date: 13/08/2002 15:51:20 ******/
CREATE TABLE [dbo].[tblQuoteOrder] (
  [QuoteOrderID] [int] IDENTITY (1, 1) NOT NULL ,
  [IsOrder] [bit] NOT NULL ,
  [UserID] [varchar] (20) NOT NULL ,
  [OrderDate] [datetime] NOT NULL ,
  [CarID] [int] NOT NULL ,
  [CarName] [varchar] (50) NULL ,
  [EngineID] [int] NOT NULL ,
  [EngineName] [varchar] (10) NULL ,
  [ColorID] [int] NOT NULL ,
  [ColorName] [varchar] (50) NULL ,
  [BasePrice] [money] NOT NULL ,
  [Interest] [money] NULL ,
  [TotalPrice] [money] NULL ,
  [PaymentMonths] [int] NULL ,
  [PaymentAmount] [money] NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblQuoteOrderExtras]    Script Date: 13/08/2002 15:51:21 ******/
CREATE TABLE [dbo].[tblQuoteOrderExtras] (
  [ExtraOrderID] [int] IDENTITY (1, 1) NOT NULL ,
  [QuoteOrderID] [int] NOT NULL ,
  [ExtraID] [int] NOT NULL ,
  [ExtraName] [varchar] (50) NULL ,
  [ExtraPrice] [money] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblEngine] WITH NOCHECK ADD
  CONSTRAINT [PK_tblEngine] PRIMARY KEY  CLUSTERED
  (
    [EngineID]
  )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblFinancePMTData] WITH NOCHECK ADD
  CONSTRAINT [PK_tblFinancePMTData] PRIMARY KEY  CLUSTERED
  (
    [Months]
  )  ON [PRIMARY]