createtables.sql

数据仓库开发的很好的一个例子

/****** Kimball Group, (c) 2006 ******/
/****** Object:  Table [dbo].[DMDemoCustTrain]    Script Date: 07/17/2006 14:27:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE AdventureWorksDW
go

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DMDemoCustTrain]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    TRUNCATE TABLE [DMDemoCustTrain]
ELSE
   CREATE TABLE [dbo].[DMDemoCustTrain](
	[CustomerKey] [int] NULL,
	[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NameStyle] [bit] NULL,
	[BirthDate] [datetime] NULL,
	[MaritalStatus] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Gender] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[YearlyIncome] [money] NULL,
	[TotalChildren] [tinyint] NULL,
	[NumberChildrenAtHome] [tinyint] NULL,
	[EnglishEducation] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EnglishOccupation] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[HouseOwnerFlag] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NumberCarsOwned] [tinyint] NULL,
	[DateFirstPurchase] [datetime] NULL,
	[CommuteDistance] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[StateProvinceName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EnglishCountryRegionName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PostalCode] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[City] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DMDemoCustTest]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    TRUNCATE TABLE [DMDemoCustTest]
ELSE
   CREATE TABLE [dbo].[DMDemoCustTest](
	[CustomerKey] [int] NULL,
	[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NameStyle] [bit] NULL,
	[BirthDate] [datetime] NULL,
	[MaritalStatus] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Gender] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[YearlyIncome] [money] NULL,
	[TotalChildren] [tinyint] NULL,
	[NumberChildrenAtHome] [tinyint] NULL,
	[EnglishEducation] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EnglishOccupation] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[HouseOwnerFlag] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NumberCarsOwned] [tinyint] NULL,
	[DateFirstPurchase] [datetime] NULL,
	[CommuteDistance] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[StateProvinceName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EnglishCountryRegionName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PostalCode] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[City] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DMDemoCustPurch]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    TRUNCATE TABLE [DMDemoCustPurch]
ELSE
   CREATE TABLE [dbo].[DMDemoCustPurch](
	[EnglishProductName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ProductLine] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ModelName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EnglishProductSubcategoryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EnglishProductCategoryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FullDateAlternateKey] [datetime] NULL,
	[SalesOrderNumber] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SalesOrderLineNumber] [tinyint] NULL,
	[OrderQuantity] [smallint] NULL,
	[SalesAmount] [money] NULL,
	[CustomerKey] [int] NULL,
	[ProductKey] [int] NULL
) ON [PRIMARY]