[SQLServer]Wide World Importers

1. スキーマの一覧とその概要を取得する

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. テーブルの一覧とその概要を取得する

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