View: vIndividualCustomer

Properties

Property	Value
Name	vIndividualCustomer
ID	1851153640
Schema	Sales
Owner	dbo
Encrypted
Schema Bound
With Check Option
ANSI NULLS
Uses Quoted Identifier
Has Opaque Metadata
Published
Schema Published
Replicated
Has Replication Filter
Has Unchecked Assembly Data
Date Correlation View
Uses Database Collation
Tracked by CDC
MS Shipped
Modify Date	8/8/2010 5:23:45 PM
Create Date	8/8/2010 5:23:45 PM

Extended Properties

Name	Value
MS_Description	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.

View Columns

Column Name	Datatype	Nullable	Is Identity
BusinessEntityID	int
Title	nvarchar(8)
FirstName	Name
MiddleName	Name
LastName	Name
Suffix	nvarchar(10)
PhoneNumber	Phone
PhoneNumberType	Name
EmailAddress	nvarchar(50)
EmailPromotion	int
AddressType	Name
AddressLine1	nvarchar(60)
AddressLine2	nvarchar(60)
City	nvarchar(30)
StateProvinceName	Name
PostalCode	nvarchar(15)
CountryRegionName	Name
Demographics	xml

Indexes

No view indexes defined.

Object Level Permissions

No object level permissions defined.

Column Level Permissions

No explicit column permissions granted to this object

Objects that depend on [vIndividualCustomer]

Sales.vIndividualCustomer

Objects on which [vIndividualCustomer] depends

Sales.vIndividualCustomer

SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE VIEW [Sales].[vIndividualCustomer] AS SELECT p.[BusinessEntityID] ,p.[Title] ,p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,p.[Suffix] ,pp.[PhoneNumber] ,pnt.[Name] AS [PhoneNumberType] ,ea.[EmailAddress] ,p.[EmailPromotion] ,at.[Name] AS [AddressType] ,a.[AddressLine1] ,a.[AddressLine2] ,a.[City] ,[StateProvinceName] = sp.[Name] ,a.[PostalCode] ,[CountryRegionName] = cr.[Name] ,p.[Demographics] FROM [Person].[Person] p INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = p.[BusinessEntityID] INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID] INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID] INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode] INNER JOIN [Person].[AddressType] at ON at.[AddressTypeID] = bea.[AddressTypeID] INNER JOIN [Sales].[Customer] c ON c.[PersonID] = p.[BusinessEntityID] LEFT OUTER JOIN [Person].[EmailAddress] ea ON ea.[BusinessEntityID] = p.[BusinessEntityID] LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.[BusinessEntityID] = p.[BusinessEntityID] LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID] WHERE c.StoreID IS NULL; EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Individual customers (names and addresses) that purchase Adventure Works Cycles products online.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'VIEW',@level1name=N'vIndividualCustomer'

Script

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [Sales].[vIndividualCustomer] AS SELECT p.[BusinessEntityID] ,p.[Title] ,p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,p.[Suffix] ,pp.[PhoneNumber] ,pnt.[Name] AS [PhoneNumberType] ,ea.[EmailAddress] ,p.[EmailPromotion] ,at.[Name] AS [AddressType] ,a.[AddressLine1] ,a.[AddressLine2] ,a.[City] ,[StateProvinceName] = sp.[Name] ,a.[PostalCode] ,[CountryRegionName] = cr.[Name] ,p.[Demographics] FROM [Person].[Person] p INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = p.[BusinessEntityID] INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID] INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID] INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode] INNER JOIN [Person].[AddressType] at ON at.[AddressTypeID] = bea.[AddressTypeID] INNER JOIN [Sales].[Customer] c ON c.[PersonID] = p.[BusinessEntityID] LEFT OUTER JOIN [Person].[EmailAddress] ea ON ea.[BusinessEntityID] = p.[BusinessEntityID] LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.[BusinessEntityID] = p.[BusinessEntityID] LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID] WHERE c.StoreID IS NULL;
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Individual customers (names and addresses) that purchase Adventure Works Cycles products online.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'VIEW',@level1name=N'vIndividualCustomer'