View: [AdventureWorks2016CTP3].[Sales].[vStoreWithAddresses]

CollapseAll image

Collapse image View properties


Name  Value 
 Schema   [Sales] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Is schema bound   
 Encrypted   
 ID   1293247662 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Resultset


Name  Description Data type Max length
BusinessEntityID   int 4
Name   [dbo].[Name] 50
AddressType   [dbo].[Name] 50
AddressLine1   nvarchar 60
AddressLine2   nvarchar 60
City   nvarchar 30
StateProvinceName   [dbo].[Name] 50
PostalCode   nvarchar 15
CountryRegionName   [dbo].[Name] 50
Total: 9 column(s)

Collapse image Objects that [Sales].[vStoreWithAddresses] depends on


Object name Object type Dep level
[Sales] Schema 1
[Person].[Address] Table 1
[Person].[AddressType] Table 1
[Person].[BusinessEntityAddress] Table 1
[Person].[CountryRegion] Table 1
[Person].[StateProvince] Table 1
[Sales].[Store] Table 1
Total 7 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Stores (including store addresses) that sell Adventure Works Cycles products to consumers.

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [Sales].[vStoreWithAddresses] AS 
SELECT 
    s.[BusinessEntityID] 
    ,s.[Name] 
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1] 
    ,a.[AddressLine2] 
    ,a.[City] 
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode] 
    ,cr.[Name] AS [CountryRegionName] 
FROM [Sales].[Store] s
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = s.[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];
GO
EXEC sp_addextendedproperty N'MS_Description', N'Stores (including store addresses) that sell Adventure Works Cycles products to consumers.', 'SCHEMA', N'Sales', 'VIEW', N'vStoreWithAddresses', NULL, NULL
GO

Collapse image See also


List of views