View:
[AdventureWorks2016CTP3].[Sales].[vStoreWithContacts]
View properties
| Name | Value |
| Schema | [Sales] |
| Owner | [dbo] |
| Creation date | 16.11.2015 |
| Is schema bound | |
| Encrypted | |
| ID | 1277247605 |
Creation options
| Name | Value |
| QUOTED_IDENTIFIER | ON |
| ANSI_NULLS | ON |
Resultset
| Name | Description | Data type | Max length |
| BusinessEntityID | int | 4 | |
| Name | [dbo].[Name] | 50 | |
| ContactType | [dbo].[Name] | 50 | |
| Title | nvarchar | 8 | |
| FirstName | [dbo].[Name] | 50 | |
| MiddleName | [dbo].[Name] | 50 | |
| LastName | [dbo].[Name] | 50 | |
| Suffix | nvarchar | 10 | |
| PhoneNumber | [dbo].[Phone] | 25 | |
| PhoneNumberType | [dbo].[Name] | 50 | |
| EmailAddress | nvarchar | 50 | |
| EmailPromotion | int | 4 |
Objects that [Sales].[vStoreWithContacts] depends on
| Object name | Object type | Dep level |
| [Sales] | Schema | 1 |
| [Person].[BusinessEntityContact] | Table | 1 |
| [Person].[ContactType] | Table | 1 |
| [Person].[EmailAddress] | Table | 1 |
| [Person].[Person] | Table | 1 |
| [Person].[PersonPhone] | Table | 1 |
| [Person].[PhoneNumberType] | Table | 1 |
| [Sales].[Store] | Table | 1 |
Extended properties
| Name | Value |
| MS_Description | Stores (including store contacts) that sell Adventure Works Cycles products to consumers. |
SQL
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE VIEW [Sales].[vStoreWithContacts] AS SELECT s.[BusinessEntityID] ,s.[Name] ,ct.[Name] AS [ContactType] ,p.[Title] ,p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,p.[Suffix] ,pp.[PhoneNumber] ,pnt.[Name] AS [PhoneNumberType] ,ea.[EmailAddress] ,p.[EmailPromotion] FROM [Sales].[Store] s INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = s.[BusinessEntityID] INNER JOIN [Person].[ContactType] ct ON ct.[ContactTypeID] = bec.[ContactTypeID] INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = bec.[PersonID] 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]; GO EXEC sp_addextendedproperty N'MS_Description', N'Stores (including store contacts) that sell Adventure Works Cycles products to consumers.', 'SCHEMA', N'Sales', 'VIEW', N'vStoreWithContacts', NULL, NULL GO |
See also