SQLCompareExampleScripts2000.sql

 CREATE DATABASE SprocketProduction
GO
USE SprocketProduction
GO
CREATE TABLE [dbo].[SprocketPrices] (
	[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
	[SprocketID] [int] NULL ,
	[Price] [money] NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sprockets] (
	[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (50) NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SprocketReferences] (
	[SprocketID] [int] IDENTITY NOT NULL ,
	[Reference] [varchar] (50) NULL 
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SprocketReferences] WITH NOCHECK ADD 
	CONSTRAINT [PK_SprocketReferences] PRIMARY KEY  NONCLUSTERED 
	(
		[SprocketID]
	)  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[SprocketPrices] WITH NOCHECK ADD 
	CONSTRAINT [PK_SprocketPrices] PRIMARY KEY  NONCLUSTERED 
	(
		[RecordID]
	)  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[Sprockets] WITH NOCHECK ADD 
	CONSTRAINT [PK_Sprockets] PRIMARY KEY  NONCLUSTERED 
	(
		[RecordID]
	)  ON [PRIMARY] 
GO
SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO
CREATE VIEW dbo.CurrentPrices
AS
SELECT SprocketID, Price, Description
FROM Sprockets INNER JOIN
    SprocketPrices ON Sprockets.RecordID = SprocketPrices.SprocketID
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO
CREATE DATABASE SprocketDevelopment
GO
USE SprocketDevelopment
GO
EXEC sp_addtype 'length', 'int', 'NULL'
EXEC sp_addtype 'width', 'int', 'NULL'
EXEC sp_addtype 'height', 'int', 'NULL'
GO
CREATE TABLE [dbo].[SprocketPrices] (
	[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
	[SprocketID] [int] NULL ,
	[Price] [money] NULL ,
	[DateValidFrom] [datetime] NULL ,
	[DateValidTo] [datetime] NULL ,
	[Active] [char] (1) NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sprockets] (
	[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
	[SprocketName] [varchar] (50) NULL ,
	[SKU] [varchar] (20) NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SprocketReferences] (
	[SprocketID] [int] NOT NULL ,
	[Reference] [varchar] (50) NULL 
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SprocketReferences] WITH NOCHECK ADD 
	CONSTRAINT [PK_SprocketReferences] PRIMARY KEY  NONCLUSTERED 
	(
		[SprocketID]
	)  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[SprocketPrices] WITH NOCHECK ADD 
	CONSTRAINT [DF_SprocketPrices_DateValidFrom] DEFAULT (getdate()) FOR [DateValidFrom],
	CONSTRAINT [DF_SprocketPrices_Active] DEFAULT ('N') FOR [Active],
	CONSTRAINT [PK_SprocketPrices] PRIMARY KEY  NONCLUSTERED 
	(
		[RecordID]
	)  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[Sprockets] WITH NOCHECK ADD 
	CONSTRAINT [PK_Sprockets] PRIMARY KEY  NONCLUSTERED 
	(
		[RecordID]
	)  ON [PRIMARY] 
GO
 CREATE  INDEX [IX_SprocketPrices] ON [dbo].[SprocketPrices]([SprocketID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_SprocketPrices_1] ON [dbo].[SprocketPrices]([DateValidFrom]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_SprocketPrices_2] ON [dbo].[SprocketPrices]([DateValidTo]) ON [PRIMARY]
GO
GRANT  SELECT  ON [dbo].[SprocketPrices]  TO [public]
GO
DENY  REFERENCES ,  INSERT ,  DELETE ,  UPDATE  ON [dbo].[SprocketPrices]  TO [public] CASCADE 
GO
GRANT  SELECT  ON [dbo].[Sprockets]  TO [public]
GO
DENY  REFERENCES ,  INSERT ,  DELETE ,  UPDATE  ON [dbo].[Sprockets]  TO [public] CASCADE 
GO
ALTER TABLE [dbo].[SprocketPrices] ADD 
	CONSTRAINT [FK_SprocketPrices_Sprockets] FOREIGN KEY 
	(
		[SprocketID]
	) REFERENCES [dbo].[Sprockets] (
		[RecordID]
	)
GO
SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO
CREATE VIEW dbo.CurrentPrices
AS
SELECT SprocketPrices.SprocketID, SprocketPrices.Price, 
    Sprockets.SprocketName
FROM dbo.Sprockets INNER JOIN
    dbo.SprocketPrices ON 
    dbo.Sprockets.RecordID = dbo.SprocketPrices.SprocketID
WHERE dbo.SprocketPrices.Active = 'Y'
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO
GRANT  SELECT  ON [dbo].[CurrentPrices]  TO [public]
GO
DENY  INSERT ,  DELETE ,  UPDATE  ON [dbo].[CurrentPrices]  TO [public] CASCADE 
GO
SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO
CREATE PROCEDURE prcActivatePrices  AS
UPDATE SprocketPrices SET Active='N' WHERE GetDate()<DateValidTo OR GetDate()>DateValidFrom
UPDATE SprocketPrices SET Active='Y' WHERE GetDate()>=DateValidFrom OR GetDate()<=DateValidFrom

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO
DENY  EXECUTE  ON [dbo].[prcActivatePrices]  TO [public] CASCADE 
GO
EXEC sp_addrole 'dispatch'
GO
CREATE TABLE [dispatch].[SprocketPackaging]
(
[RecordID] [int] NOT NULL IDENTITY(1, 1),
[SprocketID] [int] NOT NULL,
[PackagingLength] [length] NOT NULL,
[PackagingWidth] [width] NOT NULL,
[PackagingHeight] [height] NOT NULL,
[Packing Description] [varchar] (250) COLLATE Latin1_General_CS_AS_KS_WS NULL
)
GO
ALTER TABLE [dispatch].[SprocketPackaging] ADD CONSTRAINT [PK_SprocketPacking] PRIMARY KEY NONCLUSTERED  ([RecordID])
GO
ALTER TABLE [dispatch].[SprocketPackaging] ADD CONSTRAINT [FK_Sprocket_SprocketPacking] FOREIGN KEY ([SprocketID]) REFERENCES [dbo].[Sprockets] ([RecordID])
GO
CREATE VIEW [dispatch].[SprocketPackagingDetails] 
AS  SELECT  w.SprocketName AS 'Name',
            wp.[Packing Description],
            wp.[PackagingHeight]*wp.[PackagingWidth] AS 'Front Area',
            wp.[PackagingWidth]*wp.[PackagingLength] AS 'Top Area',
            wp.[PackagingHeight]*wp.[PackagingLength] AS 'Side Area',
            wp.[PackagingHeight]*wp.[PackagingWidth]*wp.[PackagingLength] AS 'Volume'
	FROM [dispatch].[SprocketPackaging] wp JOIN [dbo].Sprockets w ON  w.RecordID = wp.SprocketID
GO