GHTDB script for sql 2005 server was added.

svn path=/trunk/mcs/; revision=70338

1 files changed, 1634 insertions, 0 deletions

diff --git a/mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/GHTDB.MSSQL2005.sql b/mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/GHTDB.MSSQL2005.sql
new file mode 100644
index 00000000000..907d8872a82
--- /dev/null
+++ b/mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/GHTDB.MSSQL2005.sql
@@ -0,0 +1,1634 @@
+

+CREATE DATABASE [GHTDB]  ON (NAME = N'GHTDB_dat', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\GHTDB.mdf' , SIZE = 6, FILEGROWTH = 10%) LOG ON (NAME = N'GHTDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\GHTDB.ldf' , SIZE = 2, FILEGROWTH = 10%)

+ COLLATE SQL_Latin1_General_CP1_CI_AS

+GO

+

+exec sp_dboption N'GHTDB', N'autoclose', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'bulkcopy', N'true'

+GO

+

+exec sp_dboption N'GHTDB', N'trunc. log', N'true'

+GO

+

+exec sp_dboption N'GHTDB', N'torn page detection', N'true'

+GO

+

+exec sp_dboption N'GHTDB', N'read only', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'dbo use', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'single', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'autoshrink', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'ANSI null default', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'recursive triggers', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'ANSI nulls', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'concat null yields null', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'cursor close on commit', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'default to local cursor', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'quoted identifier', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'ANSI warnings', N'false'

+GO

+

+exec sp_dboption N'GHTDB', N'auto create statistics', N'true'

+GO

+

+exec sp_dboption N'GHTDB', N'auto update statistics', N'true'

+GO

+

+if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )

+	exec sp_dboption N'GHTDB', N'db chaining', N'false'

+GO

+

+use [GHTDB]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Categories

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CustomerCustomerDemo]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[CustomerCustomerDemo] DROP CONSTRAINT FK_CustomerCustomerDemo

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CustomerCustomerDemo_Customers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[CustomerCustomerDemo] DROP CONSTRAINT FK_CustomerCustomerDemo_Customers

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Customers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Customers

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Employees_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[Employees] DROP CONSTRAINT FK_Employees_Employees

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EmployeeTerritories_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[EmployeeTerritories] DROP CONSTRAINT FK_EmployeeTerritories_Employees

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Employees

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Order_Details_Orders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Orders

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Order_Details_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Products

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Territories_Region]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[Territories] DROP CONSTRAINT FK_Territories_Region

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Shippers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Shippers

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Suppliers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Suppliers

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EmployeeTerritories_Territories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

+ALTER TABLE [dbo].[EmployeeTerritories] DROP CONSTRAINT FK_EmployeeTerritories_Territories

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustOrderHist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[CustOrderHist]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustOrdersDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[CustOrdersDetail]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustOrdersOrders]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[CustOrdersOrders]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee Sales by Country]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[Employee Sales by Country]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_CreateTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GH_CreateTable]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_MultiRecordSets]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GH_MultiRecordSets]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_INOUT1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GH_INOUT1]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_REFCURSOR1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GH_REFCURSOR1]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_REFCURSOR2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GH_REFCURSOR2]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_REFCURSOR3]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GH_REFCURSOR3]

+GO

+

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales by Year]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[Sales by Year]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SalesByCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[SalesByCategory]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ten Most Expensive Products]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[Ten Most Expensive Products]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category Sales for 1997]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Category Sales for 1997]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales Totals by Amount]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Sales Totals by Amount]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales by Category]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Sales by Category]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Summary of Sales by Quarter]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Summary of Sales by Quarter]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Summary of Sales by Year]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Summary of Sales by Year]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoices]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Invoices]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order Details Extended]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Order Details Extended]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order Subtotals]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Order Subtotals]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product Sales for 1997]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Product Sales for 1997]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Alphabetical list of products]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Alphabetical list of products]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Current Product List]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Current Product List]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders Qry]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Orders Qry]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products Above Average Price]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Products Above Average Price]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products by Category]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Products by Category]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Quarterly Orders]') and OBJECTPROPERTY(id, N'IsView') = 1)

+drop view [dbo].[Quarterly Orders]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Categories]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerCustomerDemo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[CustomerCustomerDemo]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerDemographics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[CustomerDemographics]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Customers]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EmployeeTerritories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[EmployeeTerritories]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Employees]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_EMPTYTABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[GH_EMPTYTABLE]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Order Details]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Orders]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Products]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Region]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Region]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Shippers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Shippers]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Suppliers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Suppliers]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Territories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Territories]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Types_Simple]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Types_Simple]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Types_Extended]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Types_Extended]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Types_Specific]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Types_Specific]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GHSP_TYPES_SIMPLE_1]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GHSP_TYPES_SIMPLE_2]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_3]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GHSP_TYPES_SIMPLE_3]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_4]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GHSP_TYPES_SIMPLE_4]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_5]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GHSP_TYPES_SIMPLE_5]

+GO

+

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS OFF 

+GO

+

+

+CREATE TABLE [dbo].[TYPES_SIMPLE] (

+-- ID

+	[ID] char(10) NULL,

+	[T_BIT] [bit] NULL ,

+-- integer

+	[T_TINYINT] [tinyint] NULL ,

+	[T_SMALLINT] [smallint] NULL ,

+	[T_INT] [int] NULL ,

+	[T_BIGINT] [bigint] NULL ,

+-- float

+	[T_DECIMAL] [decimal](18, 0) NULL ,

+	[T_NUMERIC] [numeric](18, 0) NULL ,

+	[T_FLOAT] [float] NULL ,

+	[T_REAL] [real] NULL ,

+-- text

+	[T_CHAR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[T_NCHAR] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[T_VARCHAR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[T_NVARCHAR] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[TYPES_EXTENDED] (

+-- ID

+	[ID] char(10) NULL,

+-- Text

+	[T_TEXT] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[T_NTEXT] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+-- Binary

+	[T_BINARY] [binary] (50) NULL ,

+	[T_VARBINARY] [varbinary] (50) NULL ,

+--Time

+	[T_DATETIME] [datetime] NULL ,

+	[T_SMALLDATETIME] [smalldatetime] NULL 

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+

+CREATE TABLE [dbo].[TYPES_SPECIFIC] (

+-- ID

+	[ID] char(10) NULL,

+	[T_SMALLMONEY] [smallmoney] NULL ,

+	[T_MONEY] [money] NULL ,

+	[T_IMAGE] [image] NULL ,

+	[T_UNIQUEIDENTIFIER] [uniqueidentifier] NULL ,

+	[T_SQL_VARIANT] [sql_variant] NULL ,

+	[T_TIMESTAMP] [timestamp] NULL

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[Categories] (

+	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,

+	[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Picture] [image] NULL 

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[CustomerCustomerDemo] (

+	[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[CustomerDemographics] (

+	[CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[CustomerDesc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[Customers] (

+	[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[EmployeeTerritories] (

+	[EmployeeID] [int] NOT NULL ,

+	[TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[Employees] (

+	[EmployeeID] [int] NOT NULL ,

+	[LastName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[FirstName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[Title] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[TitleOfCourtesy] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[BirthDate] [datetime] NULL ,

+	[HireDate] [datetime] NULL ,

+	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[HomePhone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Extension] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Photo] [image] NULL ,

+	[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[ReportsTo] [int] NULL ,

+	[PhotoPath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[GH_EMPTYTABLE] (

+	[Col1] [int] NULL ,

+	[Col2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[Order Details] (

+	[OrderID] [int] NOT NULL ,

+	[ProductID] [int] NOT NULL ,

+	[UnitPrice] [money] NOT NULL ,

+	[Quantity] [smallint] NOT NULL ,

+	[Discount] [real] NOT NULL 

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[Orders] (

+	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,

+	[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[EmployeeID] [int] NULL ,

+	[OrderDate] [datetime] NULL ,

+	[RequiredDate] [datetime] NULL ,

+	[ShippedDate] [datetime] NULL ,

+	[ShipVia] [int] NULL ,

+	[Freight] [money] NULL ,

+	[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[Products] (

+	[ProductID] [int] IDENTITY (1, 1) NOT NULL ,

+	[ProductName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[SupplierID] [int] NULL ,

+	[CategoryID] [int] NULL ,

+	[QuantityPerUnit] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[UnitPrice] [money] NULL ,

+	[UnitsInStock] [smallint] NULL ,

+	[UnitsOnOrder] [smallint] NULL ,

+	[ReorderLevel] [smallint] NULL ,

+	[Discontinued] [bit] NOT NULL 

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[Region] (

+	[RegionID] [int] NOT NULL ,

+	[RegionDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[Shippers] (

+	[ShipperID] [int] IDENTITY (1, 1) NOT NULL ,

+	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[Suppliers] (

+	[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,

+	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[HomePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[Territories] (

+	[TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[TerritoryDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[RegionID] [int] NOT NULL 

+) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[Categories] WITH NOCHECK ADD 

+	CONSTRAINT [PK_Categories] PRIMARY KEY  CLUSTERED 

+	(

+		[CategoryID]

+	)  ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD 

+	CONSTRAINT [PK_Customers] PRIMARY KEY  CLUSTERED 

+	(

+		[CustomerID]

+	)  ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD 

+	CONSTRAINT [PK_Employees] PRIMARY KEY  CLUSTERED 

+	(

+		[EmployeeID]

+	)  ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD 

+	CONSTRAINT [PK_Order_Details] PRIMARY KEY  CLUSTERED 

+	(

+		[OrderID],

+		[ProductID]

+	)  ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD 

+	CONSTRAINT [PK_Orders] PRIMARY KEY  CLUSTERED 

+	(

+		[OrderID]

+	)  ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[Products] WITH NOCHECK ADD 

+	CONSTRAINT [PK_Products] PRIMARY KEY  CLUSTERED 

+	(

+		[ProductID]

+	)  ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[Shippers] WITH NOCHECK ADD 

+	CONSTRAINT [PK_Shippers] PRIMARY KEY  CLUSTERED 

+	(

+		[ShipperID]

+	)  ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[Suppliers] WITH NOCHECK ADD 

+	CONSTRAINT [PK_Suppliers] PRIMARY KEY  CLUSTERED 

+	(

+		[SupplierID]

+	)  ON [PRIMARY] 

+GO

+

+ CREATE  INDEX [CategoryName] ON [dbo].[Categories]([CategoryName]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[CustomerCustomerDemo] ADD 

+	CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY  NONCLUSTERED 

+	(

+		[CustomerID],

+		[CustomerTypeID]

+	)  ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[CustomerDemographics] ADD 

+	CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY  NONCLUSTERED 

+	(

+		[CustomerTypeID]

+	)  ON [PRIMARY] 

+GO

+

+ CREATE  INDEX [City] ON [dbo].[Customers]([City]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [CompanyName] ON [dbo].[Customers]([CompanyName]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [PostalCode] ON [dbo].[Customers]([PostalCode]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [Region] ON [dbo].[Customers]([Region]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[EmployeeTerritories] ADD 

+	CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY  NONCLUSTERED 

+	(

+		[EmployeeID],

+		[TerritoryID]

+	)  ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[Employees] ADD 

+	CONSTRAINT [CK_Birthdate] CHECK ([BirthDate] < getdate())

+GO

+

+ CREATE  INDEX [LastName] ON [dbo].[Employees]([LastName]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [PostalCode] ON [dbo].[Employees]([PostalCode]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[Order Details] ADD 

+	CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0) FOR [UnitPrice],

+	CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1) FOR [Quantity],

+	CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0) FOR [Discount],

+	CONSTRAINT [CK_Discount] CHECK ([Discount] >= 0 and [Discount] <= 1),

+	CONSTRAINT [CK_Quantity] CHECK ([Quantity] > 0),

+	CONSTRAINT [CK_UnitPrice] CHECK ([UnitPrice] >= 0)

+GO

+

+ CREATE  INDEX [OrderID] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [OrdersOrder_Details] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [ProductID] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [ProductsOrder_Details] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[Orders] ADD 

+	CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]

+GO

+

+ CREATE  INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[Products] ADD 

+	CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0) FOR [UnitPrice],

+	CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0) FOR [UnitsInStock],

+	CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0) FOR [UnitsOnOrder],

+	CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0) FOR [ReorderLevel],

+	CONSTRAINT [DF_Products_Discontinued] DEFAULT (0) FOR [Discontinued],

+	CONSTRAINT [CK_Products_UnitPrice] CHECK ([UnitPrice] >= 0),

+	CONSTRAINT [CK_ReorderLevel] CHECK ([ReorderLevel] >= 0),

+	CONSTRAINT [CK_UnitsInStock] CHECK ([UnitsInStock] >= 0),

+	CONSTRAINT [CK_UnitsOnOrder] CHECK ([UnitsOnOrder] >= 0)

+GO

+

+ CREATE  INDEX [CategoriesProducts] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [CategoryID] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [ProductName] ON [dbo].[Products]([ProductName]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [SupplierID] ON [dbo].[Products]([SupplierID]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [SuppliersProducts] ON [dbo].[Products]([SupplierID]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[Region] ADD 

+	CONSTRAINT [PK_Region] PRIMARY KEY  NONCLUSTERED 

+	(

+		[RegionID]

+	)  ON [PRIMARY] 

+GO

+

+ CREATE  INDEX [CompanyName] ON [dbo].[Suppliers]([CompanyName]) ON [PRIMARY]

+GO

+

+ CREATE  INDEX [PostalCode] ON [dbo].[Suppliers]([PostalCode]) ON [PRIMARY]

+GO

+

+ALTER TABLE [dbo].[Territories] ADD 

+	CONSTRAINT [PK_Territories] PRIMARY KEY  NONCLUSTERED 

+	(

+		[TerritoryID]

+	)  ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[CustomerCustomerDemo] ADD 

+	CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY 

+	(

+		[CustomerTypeID]

+	) REFERENCES [dbo].[CustomerDemographics] (

+		[CustomerTypeID]

+	),

+	CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY 

+	(

+		[CustomerID]

+	) REFERENCES [dbo].[Customers] (

+		[CustomerID]

+	)

+GO

+

+ALTER TABLE [dbo].[EmployeeTerritories] ADD 

+	CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY 

+	(

+		[EmployeeID]

+	) REFERENCES [dbo].[Employees] (

+		[EmployeeID]

+	),

+	CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY 

+	(

+		[TerritoryID]

+	) REFERENCES [dbo].[Territories] (

+		[TerritoryID]

+	)

+GO

+

+ALTER TABLE [dbo].[Employees] ADD 

+	CONSTRAINT [FK_Employees_Employees] FOREIGN KEY 

+	(

+		[ReportsTo]

+	) REFERENCES [dbo].[Employees] (

+		[EmployeeID]

+	)

+GO

+

+ALTER TABLE [dbo].[Order Details] ADD 

+	CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY 

+	(

+		[OrderID]

+	) REFERENCES [dbo].[Orders] (

+		[OrderID]

+	),

+	CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY 

+	(

+		[ProductID]

+	) REFERENCES [dbo].[Products] (

+		[ProductID]

+	)

+GO

+

+ALTER TABLE [dbo].[Orders] ADD 

+	CONSTRAINT [FK_Orders_Customers] FOREIGN KEY 

+	(

+		[CustomerID]

+	) REFERENCES [dbo].[Customers] (

+		[CustomerID]

+	),

+	CONSTRAINT [FK_Orders_Employees] FOREIGN KEY 

+	(

+		[EmployeeID]

+	) REFERENCES [dbo].[Employees] (

+		[EmployeeID]

+	),

+	CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY 

+	(

+		[ShipVia]

+	) REFERENCES [dbo].[Shippers] (

+		[ShipperID]

+	)

+GO

+

+ALTER TABLE [dbo].[Products] ADD 

+	CONSTRAINT [FK_Products_Categories] FOREIGN KEY 

+	(

+		[CategoryID]

+	) REFERENCES [dbo].[Categories] (

+		[CategoryID]

+	),

+	CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY 

+	(

+		[SupplierID]

+	) REFERENCES [dbo].[Suppliers] (

+		[SupplierID]

+	)

+GO

+

+ALTER TABLE [dbo].[Territories] ADD 

+	CONSTRAINT [FK_Territories_Region] FOREIGN KEY 

+	(

+		[RegionID]

+	) REFERENCES [dbo].[Region] (

+		[RegionID]

+	)

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Current Product List" AS

+SELECT Product_List.ProductID, Product_List.ProductName

+FROM Products AS Product_List

+WHERE (((Product_List.Discontinued)=0))

+--ORDER BY Product_List.ProductName

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Orders Qry" AS

+SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, 

+	Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, 

+	Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, 

+	Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country

+FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Products Above Average Price" AS

+SELECT Products.ProductName, Products.UnitPrice

+FROM Products

+WHERE Products.UnitPrice>(SELECT AVG(UnitPrice) From Products)

+--ORDER BY Products.UnitPrice DESC

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Products by Category" AS

+SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued

+FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

+WHERE Products.Discontinued <> 1

+--ORDER BY Categories.CategoryName, Products.ProductName

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Quarterly Orders" AS

+SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country

+FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

+WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view Invoices AS

+SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, 

+	Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, 

+	Customers.Region, Customers.PostalCode, Customers.Country, 

+	(FirstName + ' ' + LastName) AS Salesperson, 

+	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, 

+	"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, 

+	"Order Details".Discount, 

+	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight

+FROM 	Shippers INNER JOIN 

+		(Products INNER JOIN 

+			(

+				(Employees INNER JOIN 

+					(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 

+				ON Employees.EmployeeID = Orders.EmployeeID) 

+			INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) 

+		ON Products.ProductID = "Order Details".ProductID) 

+	ON Shippers.ShipperID = Orders.ShipVia

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Order Details Extended" AS

+SELECT "Order Details".OrderID, "Order Details".ProductID, Products.ProductName, 

+	"Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount, 

+	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice

+FROM Products INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID

+--ORDER BY "Order Details".OrderID

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Order Subtotals" AS

+SELECT "Order Details".OrderID, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal

+FROM "Order Details"

+GROUP BY "Order Details".OrderID

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Product Sales for 1997" AS

+SELECT Categories.CategoryName, Products.ProductName, 

+Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSales

+FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) 

+	INNER JOIN (Orders 

+		INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) 

+	ON Products.ProductID = "Order Details".ProductID

+WHERE (((Orders.ShippedDate) Between '19970101' And '19971231'))

+GROUP BY Categories.CategoryName, Products.ProductName

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Category Sales for 1997" AS

+SELECT "Product Sales for 1997".CategoryName, Sum("Product Sales for 1997".ProductSales) AS CategorySales

+FROM "Product Sales for 1997"

+GROUP BY "Product Sales for 1997".CategoryName

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Sales Totals by Amount" AS

+SELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate

+FROM 	Customers INNER JOIN 

+		(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 

+	ON Customers.CustomerID = Orders.CustomerID

+WHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231')

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Sales by Category" AS

+SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, 

+	Sum("Order Details Extended".ExtendedPrice) AS ProductSales

+FROM 	Categories INNER JOIN 

+		(Products INNER JOIN 

+			(Orders INNER JOIN "Order Details Extended" ON Orders.OrderID = "Order Details Extended".OrderID) 

+		ON Products.ProductID = "Order Details Extended".ProductID) 

+	ON Categories.CategoryID = Products.CategoryID

+WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'

+GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName

+--ORDER BY Products.ProductName

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Summary of Sales by Quarter" AS

+SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal

+FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID

+WHERE Orders.ShippedDate IS NOT NULL

+--ORDER BY Orders.ShippedDate

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create view "Summary of Sales by Year" AS

+SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal

+FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID

+WHERE Orders.ShippedDate IS NOT NULL

+--ORDER BY Orders.ShippedDate

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)

+AS

+SELECT ProductName, Total=SUM(Quantity)

+FROM Products P, [Order Details] OD, Orders O, Customers C

+WHERE C.CustomerID = @CustomerID

+AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

+GROUP BY ProductName

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+CREATE PROCEDURE CustOrdersDetail @OrderID int

+AS

+SELECT ProductName,

+    UnitPrice=ROUND(Od.UnitPrice, 2),

+    Quantity,

+    Discount=CONVERT(int, Discount * 100), 

+    ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)

+FROM Products P, [Order Details] Od

+WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)

+AS

+SELECT OrderID, 

+	OrderDate,

+	RequiredDate,

+	ShippedDate

+FROM Orders

+WHERE CustomerID = @CustomerID

+ORDER BY OrderID

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create procedure "Employee Sales by Country" 

+@Beginning_Date DateTime, @Ending_Date DateTime AS

+SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount

+FROM Employees INNER JOIN 

+	(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 

+	ON Employees.EmployeeID = Orders.EmployeeID

+WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+CREATE PROCEDURE GH_CREATETABLE

+AS

+Begin

+    --craete a temporary table

+     Create Table #temp_tbl (

+                            Col1 int,

+                            Col2 int

+                            )

+          --insert values to the table

+         insert into #temp_tbl values (11,12)

+         insert into #temp_tbl values (21,22)

+         insert into #temp_tbl values (31,32)

+         --execute select on the created table

+         select col1 as Value1, col2 as Value2 from #temp_tbl;

+         --Update Return code

+         end

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+CREATE PROCEDURE GH_MultiRecordSets 

+as BEGIN

+    -- Declare cursor

+          SELECT EmployeeId, LastName FROM Employees where EmployeeId in (1,2) order by EmployeeId asc;

+          SELECT CustomerId, CompanyName,ContactName FROM Customers  where CustomerId in ('MORGK','NORTS') order by customerid asc;

+            -- return empty result set

+          SELECT OrderId, ShipAddress,ShipVia, ShipCity FROM Orders where OrderId=-1  ;

+END

+GO

+

+CREATE procedure GH_INOUT1

+@INPARAM varchar(20) ,

+@OUTPARAM  int output

+AS

+declare @L_INPARAM varchar(30) 

+select L_INPARAM = @INPARAM

+select @OUTPARAM = 100

+GO

+

+

+CREATE procedure GH_REFCURSOR1

+AS

+SELECT EmployeeId, LastName FROM Employees

+WHERE EmployeeId=1;

+GO

+

+CREATE procedure GH_REFCURSOR2

+@IN_EMPLOYEEID int

+AS

+SELECT EmployeeId, LastName FROM Employees 

+where EmployeeId = @IN_EMPLOYEEID

+GO

+

+

+CREATE procedure GH_REFCURSOR3

+@IN_LASTNAME varchar(20) AS

+SELECT EmployeeId, LastName FROM Employees  

+where LastName = @IN_LASTNAME

+GO

+

+

+

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create procedure "Sales by Year" 

+	@Beginning_Date DateTime, @Ending_Date DateTime AS

+SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year

+FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID

+WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+CREATE PROCEDURE SalesByCategory

+    @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'

+AS

+IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' 

+BEGIN

+	SELECT @OrdYear = '1998'

+END

+SELECT ProductName,

+	TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)

+FROM [Order Details] OD, Orders O, Products P, Categories C

+WHERE OD.OrderID = O.OrderID 

+	AND OD.ProductID = P.ProductID 

+	AND P.CategoryID = C.CategoryID

+	AND C.CategoryName = @CategoryName

+	AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear

+GROUP BY ProductName

+ORDER BY ProductName

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+create procedure "Ten Most Expensive Products" AS

+SET ROWCOUNT 10

+SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice

+FROM Products

+ORDER BY Products.UnitPrice DESC

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+CREATE PROCEDURE GHSP_TYPES_SIMPLE_1

+@T_BIT bit ,

+@T_TINYINT tinyint,

+@T_SMALLINT smallint ,

+@T_INT int,

+@T_BIGINT bigint,

+@T_DECIMAL decimal(18, 0),

+@T_NUMERIC numeric(18, 0) ,

+@T_FLOAT float  ,

+@T_REAL  real  ,

+@T_CHAR char (10),

+@T_NCHAR nchar (10),

+@T_VARCHAR varchar (50) ,

+@T_NVARCHAR nvarchar (50)

+ AS

+SELECT @T_BIT as 'T_BIT', @T_TINYINT as 'T_TINYINT', @T_SMALLINT as 'T_SMALLINT' , @T_INT as 'T_INT', @T_BIGINT as 'T_BIGINT', @T_DECIMAL as 'T_DECIMAL',

+@T_NUMERIC as 'T_NUMERIC' , @T_FLOAT as 'T_FLOAT'  , @T_REAL  as 'T_REAL'  , @T_CHAR as 'T_CHAR', @T_NCHAR as 'T_NCHAR', @T_VARCHAR as 'T_VARCHAR' , @T_NVARCHAR as 'T_NVARCHAR'

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS OFF 

+GO

+

+CREATE PROCEDURE GHSP_TYPES_SIMPLE_2

+@T_BIT bit output,

+@T_TINYINT tinyint output,

+@T_SMALLINT smallint output,

+@T_INT int output,

+@T_BIGINT bigint output,

+@T_DECIMAL decimal(18, 0) output,

+@T_NUMERIC numeric(18, 0)  output,

+@T_FLOAT float output,

+@T_REAL  real output,

+@T_CHAR char (10) output,

+@T_NCHAR nchar (10) output,

+@T_VARCHAR varchar (50) output,

+@T_NVARCHAR nvarchar (50) output

+ AS

+IF @T_BIT=0

+	SELECT @T_BIT=1

+ELSE IF (@T_BIT=1)

+	SELECT @T_BIT=0

+SELECT @T_TINYINT = @T_TINYINT*2

+SELECT @T_SMALLINT = @T_SMALLINT*2 

+SELECT @T_INT = @T_INT*2

+SELECT @T_BIGINT = @T_BIGINT*2

+SELECT @T_DECIMAL = @T_DECIMAL*2

+SELECT @T_NUMERIC = @T_NUMERIC*2 

+SELECT @T_FLOAT = @T_FLOAT*2  

+SELECT @T_REAL = @T_REAL*2  

+SELECT @T_CHAR = UPPER(@T_CHAR)

+SELECT @T_NCHAR =UPPER(@T_NCHAR)

+SELECT @T_VARCHAR = UPPER(@T_VARCHAR)

+SELECT @T_NVARCHAR = UPPER(@T_NVARCHAR)

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS OFF 

+GO

+

+CREATE PROCEDURE GHSP_TYPES_SIMPLE_3

+@ID char,

+@T_BIT bit output,

+@T_TINYINT tinyint output,

+@T_SMALLINT smallint output,

+@T_INT int output,

+@T_BIGINT bigint output,

+@T_DECIMAL decimal(18, 0) output,

+@T_NUMERIC numeric(18, 0)  output,

+@T_FLOAT float output,

+@T_REAL  real output,

+@T_CHAR char (10) output,

+@T_NCHAR nchar (10) output,

+@T_VARCHAR varchar (50) output,

+@T_NVARCHAR nvarchar (50) output

+AS

+SELECT @T_BIT = T_BIT, @T_TINYINT = T_TINYINT, @T_SMALLINT = T_SMALLINT , @T_INT = T_INT, @T_BIGINT = T_BIGINT, @T_DECIMAL = T_DECIMAL ,

+@T_NUMERIC = T_NUMERIC , @T_FLOAT = T_FLOAT  , @T_REAL = T_REAL  , @T_CHAR = T_CHAR, @T_NCHAR = T_NCHAR, 

+@T_VARCHAR = T_VARCHAR, @T_NVARCHAR = T_NVARCHAR FROM TYPES_SIMPLE WHERE ID = @ID

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS OFF 

+GO

+

+CREATE PROCEDURE GHSP_TYPES_SIMPLE_4

+@ID char

+AS

+/*Insert*/

+insert into TYPES_SIMPLE(ID,T_INT) values (@ID,50)

+SELECT * FROM TYPES_SIMPLE WHERE ID = @ID

+/*Update*/

+update TYPES_SIMPLE set T_INT=60 where Id = @ID

+SELECT * FROM TYPES_SIMPLE WHERE ID = @ID

+/*Delete*/

+delete from TYPES_SIMPLE WHERE ID = @ID

+SELECT * FROM TYPES_SIMPLE WHERE ID = @ID

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS OFF 

+GO

+

+CREATE PROCEDURE GHSP_TYPES_SIMPLE_5

+AS

+DECLARE @T_BIT bit

+DECLARE @T_TINYINT tinyint

+DECLARE @T_SMALLINT smallint

+DECLARE @T_INT int

+DECLARE @T_BIGINT bigint

+DECLARE @T_DECIMAL decimal(18,0)

+DECLARE @T_NUMERIC numeric(18,0)

+DECLARE @T_FLOAT float

+DECLARE @T_REAL real

+DECLARE @T_CHAR char(10)

+DECLARE @T_NCHAR nchar(10)

+DECLARE @T_VARCHAR varchar(50)

+DECLARE @T_NVARCHAR nvarchar(50)

+

+SELECT @T_BIT = 1

+SELECT @T_TINYINT = 25

+SELECT @T_SMALLINT = 77

+SELECT @T_INT = 2525

+SELECT @T_BIGINT = 25251414

+SELECT @T_DECIMAL = 10

+SELECT @T_NUMERIC = 123123

+SELECT @T_FLOAT = 17.1414257

+SELECT @T_REAL = 0.71425

+SELECT @T_CHAR = 'abcdefghij'

+SELECT @T_NCHAR = N'klmnopqrst'

+SELECT @T_VARCHAR = 'qwertasdfg'

+SELECT @T_NVARCHAR = N'qwertasdfg'

+ 

+SELECT @T_BIT as 'T_BIT', @T_TINYINT as 'T_TINYINT', @T_SMALLINT as 'T_SMALLINT' , @T_INT as 'T_INT', @T_BIGINT as 'T_BIGINT', @T_DECIMAL as 'T_DECIMAL', @T_NUMERIC as 'T_NUMERIC' , @T_FLOAT as 'T_FLOAT'  , @T_REAL  as 'T_REAL'  , @T_CHAR as 'T_CHAR', @T_NCHAR as 'T_NCHAR', @T_VARCHAR as 'T_VARCHAR' , @T_NVARCHAR as 'T_NVARCHAR'

+

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+

+if not exists (select * from master.dbo.syslogins where loginname = N'mainsoft')

+BEGIN

+	declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'GHTDB', @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'mainsoft', N'Sql123', @logindb, @loginlang

+END

+GO

+

+exec sp_addsrvrolemember N'mainsoft', sysadmin

+GO

+

+

+if not exists (select * from dbo.sysusers where name = N'mainsoft' and uid < 16382)

+	EXEC sp_grantdbaccess N'mainsoft', N'mainsoft'

+GO

+

+SET QUOTED_IDENTIFIER ON 

+GO

+SET ANSI_NULLS ON 

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[mainsoft].[CategoriesNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [mainsoft].[CategoriesNew]

+GO

+

+CREATE TABLE [mainsoft].[CategoriesNew] (

+	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,

+	[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Picture] [image] NULL 

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+CREATE TABLE [mainsoft].[Categories] (

+	[CategoryID] [nvarchar] (15) NOT NULL ,

+	[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Picture] [int] NULL 

+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

+GO

+

+CREATE  procedure [mainsoft].[GH_DUMMY]

+@EmployeeIdPrm  char (10)

+AS

+SELECT * FROM Employees where EmployeeID > @EmployeeIdPrm

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+

+------------------------------------------------------------------------------------------------------------------

+------------------------------------------------------------------------------------------------------------------

+------------------------------------------------------------------------------------------------------------------

+------------------------------------------------------------------------------------------------------------------

+

+

+CREATE DATABASE [GHTDB_EX]  ON (NAME = N'GHTDB_dat_EX', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\GHTDB_EX.mdf' , SIZE = 6, FILEGROWTH = 10%) LOG ON (NAME = N'GHTDB_EX_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\GHTDB_EX.ldf' , SIZE = 2, FILEGROWTH = 10%)

+ COLLATE SQL_Latin1_General_CP1_CI_AS

+GO

+

+exec sp_dboption N'GHTDB_EX', N'autoclose', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'bulkcopy', N'true'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'trunc. log', N'true'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'torn page detection', N'true'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'read only', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'dbo use', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'single', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'autoshrink', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'ANSI null default', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'recursive triggers', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'ANSI nulls', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'concat null yields null', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'cursor close on commit', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'default to local cursor', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'quoted identifier', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'ANSI warnings', N'false'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'auto create statistics', N'true'

+GO

+

+exec sp_dboption N'GHTDB_EX', N'auto update statistics', N'true'

+GO

+

+if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )

+	exec sp_dboption N'GHTDB_EX', N'db chaining', N'false'

+GO

+

+use [GHTDB_EX]

+GO

+

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_DUMMY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

+drop procedure [dbo].[GH_DUMMY]

+GO

+

+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[Customers]

+GO

+

+CREATE TABLE [dbo].[Customers] (

+	[CustomerID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

+	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

+	[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

+) ON [PRIMARY]

+GO

+

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS OFF 

+GO

+

+print '------------------------------'

+print 'create another GH_DUMMY which select from a different table'

+print 'customers instead of employees'

+print '------------------------------'

+go

+

+CREATE  procedure GH_DUMMY

+@CustomerIdPrm  char (10)

+AS

+SELECT * FROM Customers where CustomerID = @CustomerIdPrm

+GO

+SET QUOTED_IDENTIFIER OFF 

+GO

+SET ANSI_NULLS ON 

+GO

+