[SQLServer]Wide World Importers

# 1. スキーマの一覧とその概要を取得する
```sql:SELECT_schema.sql
SELECT 
    s.name AS schema_name,
    ep.value AS property_value
FROM 
    sys.schemas s
INNER JOIN 
    sys.extended_properties ep
ON 
    s.schema_id = ep.major_id
    AND ep.minor_id = 0 --スキーマ自体のプロパティを取得
    AND ep.class = 3    --クラス3はスキーマを示す
WHERE 
    ep.value IS NOT NULL;
```

### 実行結果

|schema_name|property_value|
|:-|:-|
|Application|Tables common across the application. Used for categorization and lookup lists, system parameters and people (users and contacts)|
|DataLoadSimulation|Tables and procedures used only during simulated data loading operations|
|Integration|Tables and procedures required for integration with the data warehouse|
|PowerBI|Views and stored procedures that provide the only access for the Power BI dashboard system|
|Purchasing|Details of suppliers and of purchasing of stock items|
|Reports|Views and stored procedures that provide the only access for the reporting system|
|Sales|Details of customers, salespeople, and of sales of stock items|
|Sequences|Holds sequences used by all tables in the application|
|Warehouse|Details of stock items, their holdings and transactions|
|Website|Views and stored procedures that provide the only access for the application website|

# 2. テーブルの一覧とその概要を取得する
```sql:SELECT_table.sql
SELECT 
    s.name AS schema_name,
    t.name AS table_name,
    ep.value AS property_value
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s
ON 
    t.schema_id = s.schema_id
INNER JOIN 
    sys.extended_properties ep
ON 
    t.object_id = ep.major_id
    AND ep.minor_id = 0
    AND ep.class = 1
WHERE 
    ep.value IS NOT NULL
ORDER BY 
    s.name, t.name;
```

### 実行結果

|schema_name|table_name|property_value|
|:-|:-|:-|
|Application|Cities|Cities that are part of any address (including geographic location)|
|Application|Countries|Countries that contain the states or provinces (including geographic boundaries)|
|Application|DeliveryMethods|Ways that stock items can be delivered (ie: truck/van, post, pickup, courier, etc.|
|Application|PaymentMethods|Ways that payments can be made (ie: cash, check, EFT, etc.|
|Application|People|People known to the application (staff, customer contacts, supplier contacts)|
|Application|StateProvinces|States or provinces that contain cities (including geographic location)|
|Application|SystemParameters|Any configurable parameters for the whole system|
|Application|TransactionTypes|Types of customer, supplier, or stock transactions (ie: invoice, credit note, etc.)|
|Purchasing|PurchaseOrderLines|Detail lines from supplier purchase orders|
|Purchasing|PurchaseOrders|Details of supplier purchase orders|
|Purchasing|SupplierCategories|Categories for suppliers (ie novelties, toys, clothing, packaging, etc.)|
|Purchasing|Suppliers|Main entity table for suppliers (organizations)|
|Purchasing|SupplierTransactions|All financial transactions that are supplier-related|
|Sales|BuyingGroups|Customer organizations can be part of groups that exert greater buying power|
|Sales|CustomerCategories|Categories for customers (ie restaurants, cafes, supermarkets, etc.)|
|Sales|Customers|Main entity tables for customers (organizations or individuals)|
|Sales|CustomerTransactions|All financial transactions that are customer-related|
|Sales|InvoiceLines|Detail lines from customer invoices|
|Sales|Invoices|Details of customer invoices|
|Sales|OrderLines|Detail lines from customer orders|
|Sales|Orders|Detail of customer orders|
|Sales|SpecialDeals|Special pricing (can include fixed prices, discount $ or discount %)|
|Warehouse|Colors|Stock items can (optionally) have colors|
|Warehouse|PackageTypes|Ways that stock items can be packaged (ie: each, box, carton, pallet, kg, etc.|
|Warehouse|StockGroups|Groups for categorizing stock items (ie: novelties, toys, edible novelties, etc.)|
|Warehouse|StockItemHoldings|Non-temporal attributes for stock items|
|Warehouse|StockItems|Main entity table for stock items|
|Warehouse|StockItemStockGroups|Which stock items are in which stock groups|
|Warehouse|StockItemTransactions|Transactions covering all movements of all stock items|