make-tables.sql

ASP.NET Web Forms Techniques

GO

ALTER TABLE [dbo].[tblMailingList] WITH NOCHECK ADD
  CONSTRAINT [PK_tblEmailAddr] PRIMARY KEY  CLUSTERED
  (
    [EmailAddr]
  )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblNews] WITH NOCHECK ADD
  CONSTRAINT [PK_tblNews] PRIMARY KEY  CLUSTERED
  (
    [NewsID]
  )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblOptionExtra] WITH NOCHECK ADD
  CONSTRAINT [PK_tblOptionExtra] PRIMARY KEY  CLUSTERED
  (
    [ExtraID]
  )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblUsers] WITH NOCHECK ADD
  CONSTRAINT [PK_tblUsers] PRIMARY KEY  CLUSTERED
  (
    [UserID]
  )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCarColors] WITH NOCHECK ADD
  CONSTRAINT [PK_tblCarColors] PRIMARY KEY  CLUSTERED
  (
    [CarID],
    [ColorID]
  )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCarEngines] WITH NOCHECK ADD
  CONSTRAINT [PK_tblCarEngines] PRIMARY KEY  CLUSTERED
  (
    [CarID],
    [EngineID]
  )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCarExtras] WITH NOCHECK ADD
  CONSTRAINT [PK_tblCarExtras] PRIMARY KEY  CLUSTERED
  (
    [CarID],
    [ExtraID]
  )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblQuoteOrderExtras] WITH NOCHECK ADD
  CONSTRAINT [PK_tblQuoteOrderExtras] PRIMARY KEY  CLUSTERED
  (
    [ExtraOrderID]
  )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCar] WITH NOCHECK ADD
  CONSTRAINT [aaaaatblCar_PK] PRIMARY KEY  NONCLUSTERED
  (
    [CarID]
  )  ON [PRIMARY]
GO

 CREATE  INDEX [CarID] ON [dbo].[tblCar]([CarID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblColor] WITH NOCHECK ADD
  CONSTRAINT [DF_tblColor_IsMetallic] DEFAULT (0) FOR [IsMetallic],
  CONSTRAINT [aaaaatblColour_PK] PRIMARY KEY  NONCLUSTERED
  (
    [ColorID]
  )  ON [PRIMARY] ,
  CONSTRAINT [IX_tblColor] UNIQUE  NONCLUSTERED
  (
    [Color]
  )  ON [PRIMARY]
GO

 CREATE  INDEX [ColourID] ON [dbo].[tblColor]([ColorID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblMailingList] WITH NOCHECK ADD
  CONSTRAINT [DF_tblEmailAddr_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO

ALTER TABLE [dbo].[tblQuoteOrder] WITH NOCHECK ADD
  CONSTRAINT [DF_tblQuoteOrder_IsOrder] DEFAULT (0) FOR [IsOrder],
  CONSTRAINT [DF_tblQuoteOrder_OrderDate] DEFAULT (getdate()) FOR [OrderDate],
  CONSTRAINT [aaaaatblOrder_PK] PRIMARY KEY  NONCLUSTERED
  (
    [QuoteOrderID]
  )  ON [PRIMARY]
GO

 CREATE  INDEX [fkCarID] ON [dbo].[tblQuoteOrder]([CarID]) ON [PRIMARY]
GO

 CREATE  INDEX [OrderID] ON [dbo].[tblQuoteOrder]([QuoteOrderID]) ON [PRIMARY]
GO

 CREATE  INDEX [tblCartblOrder] ON [dbo].[tblQuoteOrder]([CarID]) ON [PRIMARY]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblCar]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblCar]  TO [anon]
GO

GRANT  SELECT,   UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblColor]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblColor]  TO [anon]
GO

GRANT  SELECT,   UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblEngine]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblEngine]  TO [anon]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblFinancePMTData]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblFinancePMTData]  TO [anon]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblMailingList]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblMailingList]  TO [anon]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblNews]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblNews]  TO [anon]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblOptionExtra]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblOptionExtra]  TO [anon]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblUsers]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblUsers]  TO [anon]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblCarColors]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblCarColors]  TO [anon]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblCarEngines]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblCarEngines]  TO [anon]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblCarExtras]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblCarExtras]  TO [anon]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblQuoteOrder]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblQuoteOrder]  TO [anon]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblQuoteOrderExtras]  TO [public]
GO

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[tblQuoteOrderExtras]  TO [anon]
GO

ALTER TABLE [dbo].[tblCarColors] ADD
  CONSTRAINT [FK_tblCarColor_tblColor] FOREIGN KEY
  (
    [ColorID]
  ) REFERENCES [dbo].[tblColor] (
    [ColorID]
  ),
  CONSTRAINT [FK_tblCarColors_tblCar] FOREIGN KEY
  (
    [CarID]
  ) REFERENCES [dbo].[tblCar] (
    [CarID]
  )
GO

ALTER TABLE [dbo].[tblCarEngines] ADD
  CONSTRAINT [FK_tblCarEngines_tblCar] FOREIGN KEY
  (
    [CarID]
  ) REFERENCES [dbo].[tblCar] (
    [CarID]
  ),
  CONSTRAINT [FK_tblCarEngines_tblEngine] FOREIGN KEY
  (
    [EngineID]
  ) REFERENCES [dbo].[tblEngine] (
    [EngineID]
  )
GO

ALTER TABLE [dbo].[tblCarExtras] ADD
  CONSTRAINT [FK_tblCarExtras_tblCar] FOREIGN KEY
  (
    [CarID]
  ) REFERENCES [dbo].[tblCar] (
    [CarID]
  ),
  CONSTRAINT [FK_tblCarExtras_tblOptionExtra] FOREIGN KEY
  (
    [ExtraID]
  ) REFERENCES [dbo].[tblOptionExtra] (
    [ExtraID]
  )
GO

ALTER TABLE [dbo].[tblQuoteOrder] ADD
  CONSTRAINT [FK_tblOrder_tblColor] FOREIGN KEY
  (
    [ColorID]
  ) REFERENCES [dbo].[tblColor] (
    [ColorID]
  ),
  CONSTRAINT [FK_tblQuoteOrder_tblCar] FOREIGN KEY
  (
    [CarID]
  ) REFERENCES [dbo].[tblCar] (
    [CarID]
  ),
  CONSTRAINT [FK_tblQuoteOrder_tblEngine] FOREIGN KEY
  (
    [EngineID]
  ) REFERENCES [dbo].[tblEngine] (
    [EngineID]
  )
GO

ALTER TABLE [dbo].[tblQuoteOrderExtras] ADD
  CONSTRAINT [FK_tblQuoteOrderExtras_tblOptionExtra] FOREIGN KEY
  (
    [ExtraID]
  ) REFERENCES [dbo].[tblOptionExtra] (
    [ExtraID]
  ),
  CONSTRAINT [FK_tblQuoteOrderExtras_tblQuoteOrder] FOREIGN KEY
  (
    [QuoteOrderID]
  ) REFERENCES [dbo].[tblQuoteOrder] (
    [QuoteOrderID]
  )
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.AddMailingListAddress    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE AddMailingListAddress
@Email varchar(255), @Result int OUTPUT
AS
SELECT EmailAddr FROM tblMailingList WHERE EmailAddr = @Email
IF @@ROWCOUNT = 0
    INSERT INTO tblMailingList (EmailAddr) VALUES (@Email)
SELECT @Result =  @@ROWCOUNT

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[AddMailingListAddress]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[AddMailingListAddress]  TO [anon]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.AuthenticateUser    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE AuthenticateUser
@UserID varchar(20), @UserPW varchar(255)
AS
SELECT UserID FROM tblUsers WHERE UserID = @UserID AND UserPW = @UserPW

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[AuthenticateUser]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[AuthenticateUser]  TO [anon]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.GetFinancePMTRates    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE GetFinancePMTRates AS
SELECT Months, Payment FROM tblFinancePMTData
ORDER BY Months

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[GetFinancePMTRates]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[GetFinancePMTRates]  TO [anon]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.GetNewsItem    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE GetNewsItem
@ItemID int
AS
SELECT * FROM tblNews
WHERE NewsID =  @ItemID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[GetNewsItem]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[GetNewsItem]  TO [anon]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.GetNewsList    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE GetNewsList AS
SELECT NewsID, NewsTitle, NewsPrecis
FROM tblNews
ORDER BY NewsID DESC

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO