1
1
-- WideWorldImporters Database Metadata Population
2
2
--
3
3
-- Creates the WWI_Prep Database
4
- --
4
+ --
5
5
6
6
USE master ;
7
7
GO
@@ -76,7 +76,7 @@ CREATE TABLE Metadata.[Constraints]
76
76
[ConstraintID] int IDENTITY (1 ,1 )
77
77
CONSTRAINT PK_Metadata_Constraints PRIMARY KEY ,
78
78
TableName sysname NOT NULL ,
79
- ConstraintName sysname NOT NULL ,
79
+ ConstraintName sysname NOT NULL ,
80
80
ConstraintDefinition nvarchar (max ) NOT NULL ,
81
81
ConstraintDescription nvarchar (max ) NOT NULL
82
82
);
@@ -87,7 +87,7 @@ CREATE TABLE Metadata.[Indexes]
87
87
[IndexID] int IDENTITY (1 ,1 )
88
88
CONSTRAINT PK_Metadata_Indexes PRIMARY KEY ,
89
89
TableName sysname NOT NULL ,
90
- IndexName sysname NOT NULL ,
90
+ IndexName sysname NOT NULL ,
91
91
IndexColumns nvarchar (max ) NOT NULL ,
92
92
IncludedColumns nvarchar (max ) NULL ,
93
93
IsUnique bit NOT NULL ,
@@ -97,9 +97,9 @@ CREATE TABLE Metadata.[Indexes]
97
97
GO
98
98
99
99
-- Schemas
100
- INSERT Metadata.[Schemas]
100
+ INSERT Metadata.[Schemas]
101
101
(SchemaName, SchemaDescription)
102
- VALUES
102
+ VALUES
103
103
(N ' Application' , N ' Tables common across the application. Used for categorization and lookup lists, system parameters and people (users and contacts)' ),
104
104
(N ' DataLoadSimulation' , N ' Tables and procedures used only during simulated data loading operations' ),
105
105
(N ' Integration' , ' Tables and procedures required for integration with the data warehouse' ),
@@ -184,7 +184,7 @@ VALUES
184
184
(N ' PaymentMethods' , N ' PaymentMethodName' , 0 , N ' nvarchar' , 0 , 50 , NULL , NULL , 0 , 0 , NULL , NULL , 1 , 0 , NULL , NULL , 0 , NULL , N ' Full name of ways that customers can make payments or that suppliers can be paid' );
185
185
GO
186
186
187
- -- Application.People Table
187
+ -- Application.People Table
188
188
INSERT Metadata.[Tables]
189
189
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
190
190
VALUES (10 , N ' Application' , N ' People' , 1 , 1 , N ' People known to the application (staff, customer contacts, supplier contacts)' );
217
217
218
218
INSERT Metadata.[Indexes]
219
219
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
220
- VALUES
220
+ VALUES
221
221
(N ' People' , N ' IX_Application_People_IsEmployee' , N ' [IsEmployee]' , NULL , 0 , NULL , N ' Allows quickly locating employees' );
222
222
GO
223
223
224
224
INSERT Metadata.[Indexes]
225
225
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
226
- VALUES
226
+ VALUES
227
227
(N ' People' , N ' IX_Application_People_IsSalesperson' , N ' [IsSalesperson]' , NULL , 0 , NULL , N ' Allows quickly locating salespeople' );
228
228
GO
229
229
230
230
INSERT Metadata.[Indexes]
231
231
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
232
- VALUES
232
+ VALUES
233
233
(N ' People' , N ' IX_Application_People_FullName' , N ' [FullName]' , NULL , 0 , NULL , N ' Improves performance of name-related queries' );
234
234
GO
235
235
236
236
INSERT Metadata.[Indexes]
237
237
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
238
- VALUES
238
+ VALUES
239
239
(N ' People' , N ' IX_Application_People_Perf_20160301_05' , N ' [IsPermittedToLogon],[PersonID]' , N ' [FullName], [EmailAddress]' , 0 , NULL , N ' Improves performance of order picking and invoicing' );
240
240
GO
241
241
261
261
262
262
INSERT Metadata.[Indexes]
263
263
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
264
- VALUES
264
+ VALUES
265
265
(N ' StateProvinces' , N ' IX_Application_StateProvinces_SalesTerritory' , N ' [SalesTerritory]' , NULL , 0 , NULL , N ' Index used to quickly locate sales territories' );
266
266
GO
267
267
268
- -- Application.SystemParameters Table
268
+ -- Application.SystemParameters Table
269
269
INSERT Metadata.[Tables]
270
270
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
271
271
VALUES (190 , N ' Application' , N ' SystemParameters' , 0 , 1 , N ' Any configurable parameters for the whole system' );
@@ -304,7 +304,7 @@ VALUES
304
304
(N ' TransactionTypes' , N ' TransactionTypeName' , 0 , N ' nvarchar' , 0 , 50 , NULL , NULL , 0 , 0 , NULL , NULL , 1 , 0 , NULL , NULL , 0 , NULL , N ' Full name of the transaction type' );
305
305
GO
306
306
307
- -- Purchasing.PurchaseOrderLines Table
307
+ -- Purchasing.PurchaseOrderLines Table
308
308
INSERT Metadata.[Tables]
309
309
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
310
310
VALUES (250 , N ' Purchasing' , N ' PurchaseOrderLines' , 0 , 1 , N ' Detail lines from supplier purchase orders' );
329
329
330
330
INSERT Metadata.[Indexes]
331
331
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
332
- VALUES
332
+ VALUES
333
333
(N ' PurchaseOrderLines' , N ' IX_Purchasing_PurchaseOrderLines_Perf_20160301_4' , N ' [IsOrderLineFinalized], [StockItemID]' , N ' [OrderedOuters], [ReceivedOuters]' , 0 , NULL , N ' Improves performance of order picking and invoicing' );
334
334
GO
335
335
336
- -- Purchasing.PurchaseOrders Table
336
+ -- Purchasing.PurchaseOrders Table
337
337
INSERT Metadata.[Tables]
338
338
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
339
339
VALUES (220 , N ' Purchasing' , N ' PurchaseOrders' , 0 , 1 , N ' Details of supplier purchase orders' );
@@ -371,7 +371,7 @@ VALUES
371
371
(N ' SupplierCategories' , N ' SupplierCategoryName' , 0 , N ' nvarchar' , 0 , 50 , NULL , NULL , 0 , 0 , NULL , NULL , 1 , 0 , NULL , NULL , 0 , NULL , N ' Full name of the category that suppliers can be assigned to' );
372
372
GO
373
373
374
- -- Purchasing.Suppliers Table
374
+ -- Purchasing.Suppliers Table
375
375
INSERT Metadata.[Tables]
376
376
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
377
377
VALUES (200 , N ' Purchasing' , N ' Suppliers' , 1 , 1 , N ' Main entity table for suppliers (organizations)' );
@@ -410,7 +410,7 @@ VALUES
410
410
(N ' Suppliers' , N ' PostalPostalCode' , 0 , N ' nvarchar' , 0 , 10 , NULL , NULL , 0 , 0 , NULL , NULL , 0 , 0 , NULL , NULL , 0 , NULL , N ' Postal code for the supplier when sending by mail' );
411
411
GO
412
412
413
- -- Purchasing.SupplierTransactions Table
413
+ -- Purchasing.SupplierTransactions Table
414
414
INSERT Metadata.[Tables]
415
415
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
416
416
VALUES (260 , N ' Purchasing' , N ' SupplierTransactions' , 0 , 1 , N ' All financial transactions that are supplier-related' );
438
438
439
439
INSERT Metadata.[Indexes]
440
440
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
441
- VALUES
441
+ VALUES
442
442
(N ' SupplierTransactions' , N ' IX_Purchasing_SupplierTransactions_IsFinalized' , N ' [IsFinalized]' , NULL , 0 , NULL , N ' Index used to quickly locate unfinalized transactions' );
443
443
GO
444
444
@@ -472,7 +472,7 @@ VALUES
472
472
(N ' CustomerCategories' , N ' CustomerCategoryName' , 0 , N ' nvarchar' , 0 , 50 , NULL , NULL , 0 , 0 , NULL , NULL , 1 , 0 , NULL , NULL , 0 , NULL , N ' Full name of the category that customers can be assigned to' );
473
473
GO
474
474
475
- -- Sales.Customers Table
475
+ -- Sales.Customers Table
476
476
INSERT Metadata.[Tables]
477
477
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
478
478
VALUES (210 , N ' Sales' , N ' Customers' , 1 , 1 , N ' Main entity tables for customers (organizations or individuals)' );
515
515
516
516
INSERT Metadata.[Indexes]
517
517
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
518
- VALUES
518
+ VALUES
519
519
(N ' Customers' , N ' IX_Sales_Customers_Perf_20160301_06' , N ' [IsOnCreditHold], [CustomerID], [BillToCustomerID]' , N ' [PrimaryContactPersonID]' , 0 , NULL , N ' Improves performance of order picking and invoicing' );
520
520
GO
521
521
@@ -557,7 +557,7 @@ VALUES (N'SpecialDeals', N'CK_Sales_SpecialDeals_Unit_Price_Deal_Requires_Specia
557
557
N ' Ensures that if a specific price is allocated that it applies to a specific stock item' );
558
558
GO
559
559
560
- -- Sales.CustomerTransactions Table
560
+ -- Sales.CustomerTransactions Table
561
561
INSERT Metadata.[Tables]
562
562
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
563
563
VALUES (360 , N ' Sales' , N ' CustomerTransactions' , 0 , 1 , N ' All financial transactions that are customer-related' );
584
584
585
585
INSERT Metadata.[Indexes]
586
586
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
587
- VALUES
587
+ VALUES
588
588
(N ' CustomerTransactions' , N ' IX_Sales_CustomerTransactions_IsFinalized' , N ' [IsFinalized]' , NULL , 0 , NULL , N ' Allows quick location of unfinalized transactions' );
589
589
GO
590
590
591
- -- Sales.InvoiceLines Table
591
+ -- Sales.InvoiceLines Table
592
592
INSERT Metadata.[Tables]
593
593
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
594
594
VALUES (370 , N ' Sales' , N ' InvoiceLines' , 0 , 1 , N ' Detail lines from customer invoices' );
@@ -612,7 +612,7 @@ VALUES
612
612
(N ' InvoiceLines' , N ' ExtendedPrice' , 0 , N ' decimal' , 0 , NULL , 18 , 2 , 0 , 0 , NULL , NULL , 0 , 0 , NULL , NULL , 0 , NULL , N ' Extended line price charged' );
613
613
GO
614
614
615
- -- Sales.Invoices Table
615
+ -- Sales.Invoices Table
616
616
INSERT Metadata.[Tables]
617
617
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
618
618
VALUES (310 , N ' Sales' , N ' Invoices' , 0 , 1 , N ' Details of customer invoices' );
650
650
651
651
INSERT Metadata.[Indexes]
652
652
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
653
- VALUES
653
+ VALUES
654
654
(N ' Invoices' , N ' IX_Sales_Invoices_ConfirmedDeliveryTime' , N ' [ConfirmedDeliveryTime]' , N ' [ConfirmedReceivedBy]' , 0 , NULL , N ' Allows quick retrieval of invoices confirmed to have been delivered in a given time period' );
655
655
GO
656
656
@@ -660,7 +660,7 @@ VALUES (N'Invoices', N'CK_Sales_Invoices_ReturnedDeliveryData_Must_Be_Valid_JSON
660
660
N ' CHECK (ReturnedDeliveryData IS NULL OR ISJSON(ReturnedDeliveryData) <> 0)' ,
661
661
N ' Ensures that if returned delivery data is present that it is valid JSON' );
662
662
663
- -- Sales.OrderLines Table
663
+ -- Sales.OrderLines Table
664
664
INSERT Metadata.[Tables]
665
665
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
666
666
VALUES (320 , N ' Sales' , N ' OrderLines' , 0 , 1 , N ' Detail lines from customer orders' );
685
685
686
686
INSERT Metadata.[Indexes]
687
687
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
688
- VALUES
688
+ VALUES
689
689
(N ' OrderLines' , N ' IX_Sales_OrderLines_AllocatedStockItems' , N ' [StockItemID]' , N ' [PickedQuantity]' , 0 , NULL , N ' Allows quick summation of stock item quantites already allocated to uninvoiced orders' );
690
690
GO
691
691
692
692
INSERT Metadata.[Indexes]
693
693
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
694
- VALUES
694
+ VALUES
695
695
(N ' OrderLines' , N ' IX_Sales_OrderLines_Perf_20160301_01' , N ' [PickingCompletedWhen], [OrderID], [OrderLineID]' , N ' [Quantity], [StockItemID]' , 0 , NULL , N ' Improves performance of order picking and invoicing' );
696
696
GO
697
697
698
698
INSERT Metadata.[Indexes]
699
699
(TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
700
- VALUES
700
+ VALUES
701
701
(N ' OrderLines' , N ' IX_Sales_OrderLines_Perf_20160301_02' , N ' [StockItemID], [PickingCompletedWhen]' , N ' [OrderID], [PickedQuantity]' , 0 , NULL , N ' Improves performance of order picking and invoicing' );
702
702
GO
703
703
704
- -- Sales.Orders Table
704
+ -- Sales.Orders Table
705
705
INSERT Metadata.[Tables]
706
706
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
707
707
VALUES (230 , N ' Sales' , N ' Orders' , 0 , 1 , N ' Detail of customer orders' );
@@ -745,6 +745,12 @@ VALUES
745
745
(N ' ColdRoomTemperatures' , N ' Temperature' , 0 , N ' decimal' , 0 , NULL , 10 , 2 , 0 , 0 , NULL , NULL , 0 , 0 , NULL , NULL , 0 , NULL , N ' Temperature at the time of recording' );
746
746
GO
747
747
748
+ INSERT Metadata.[Indexes]
749
+ (TableName, IndexName, IndexColumns, IncludedColumns, IsUnique, FilterClause, IndexDescription)
750
+ VALUES
751
+ (N ' ColdRoomTemperatures' , N ' IX_Warehouse_ColdRoomTemperatures_ColdRoomSensorNumber' , N ' [ColdRoomSensorNumber]' , NULL , 0 , NULL , N ' Allows quickly locating sensors' );
752
+ GO
753
+
748
754
-- Warehouse.Colors Table
749
755
INSERT Metadata.[Tables]
750
756
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
@@ -790,7 +796,7 @@ VALUES
790
796
(N ' StockGroups' , N ' StockGroupName' , 0 , N ' nvarchar' , 0 , 50 , NULL , NULL , 0 , 0 , NULL , NULL , 1 , 0 , NULL , NULL , 0 , NULL , N ' Full name of groups used to categorize stock items' );
791
797
GO
792
798
793
- -- Warehouse.StockItemHoldings Table
799
+ -- Warehouse.StockItemHoldings Table
794
800
INSERT Metadata.[Tables]
795
801
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
796
802
VALUES (245 , N ' Warehouse' , N ' StockItemHoldings' , 0 , 1 , N ' Non-temporal attributes for stock items' );
@@ -810,7 +816,7 @@ VALUES
810
816
(N ' StockItemHoldings' , N ' TargetStockLevel' , 0 , N ' int' , 0 , NULL , NULL , NULL , 0 , 0 , NULL , NULL , 0 , 0 , NULL , NULL , 0 , NULL , N ' Typical quantity ordered' );
811
817
GO
812
818
813
- -- Warehouse.StockItems Table
819
+ -- Warehouse.StockItems Table
814
820
INSERT Metadata.[Tables]
815
821
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
816
822
VALUES (240 , N ' Warehouse' , N ' StockItems' , 1 , 1 , N ' Main entity table for stock items' );
@@ -845,7 +851,7 @@ VALUES
845
851
(N ' StockItems' , N ' SearchDetails' , 0 , N ' AS CONCAT([StockItemName], N'' '' , [MarketingComments])' , 1 , NULL , NULL , NULL , 0 , 0 , NULL , NULL , 0 , 0 , NULL , NULL , 0 , NULL , N ' Combination of columns used by full text search' );
846
852
GO
847
853
848
- -- Warehouse.StockItemStockGroups Table
854
+ -- Warehouse.StockItemStockGroups Table
849
855
INSERT Metadata.[Tables]
850
856
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
851
857
VALUES (350 , N ' Warehouse' , N ' StockItemStockGroups' , 0 , 1 , N ' Which stock items are in which stock groups' );
@@ -868,7 +874,7 @@ VALUES
868
874
(N ' StockItemStockGroups' , N ' UQ_StockItemStockGroups_StockGroupID_Lookup' , N ' UNIQUE(StockGroupID, StockItemID)' , N ' Enforces uniqueness and indexes one side of the many to many relationship' );
869
875
GO
870
876
871
- -- Warehouse.StockItemTransactions Table
877
+ -- Warehouse.StockItemTransactions Table
872
878
INSERT Metadata.[Tables]
873
879
(TableCreationOrder, SchemaName, TableName, IncludeTemporalColumns, IncludeModificationTrackingColumns, TableDescription)
874
880
VALUES (380 , N ' Warehouse' , N ' StockItemTransactions' , 0 , 1 , N ' Transactions covering all movements of all stock items' );
@@ -927,7 +933,7 @@ AS BEGIN
927
933
DECLARE @StringToExecute nvarchar (max ) = N ' ' ;
928
934
DECLARE @NextLineEnd int ;
929
935
DECLARE @Counter int ;
930
-
936
+
931
937
WHILE LEN (@StringToPrint) > 0
932
938
BEGIN
933
939
SET @NextLineEnd = CHARINDEX (NCHAR (13 ), @StringToPrint, 1 );
0 commit comments