Database: Northwind

Properties

Name: | Northwind
Owner: | sa
Create for Attach: | 08.06.2000
Compatibility Level: | 80
Status: | Database is available for query
Full Text Search Enabled: |
Primary File: | C:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.mdf
Version: | 539
Database size, MB: | 3.94 MB
Allocated Space, KB: | 2648 KB
Data Space in Use and Reserved for Use, KB: | 1056 KB
Index Space Usage, KB: | 1312 KB
Allocated and Unused Space, KB: | 280 KB
Description: |

Data Files

File Name | Location | File Group | Allocated Space, MB | Autogrows | Grows | Maximum File Size, MB
Northwind | C:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.mdf | PRIMARY | 2 |  | 10 % | Unrestricted
Total: 1 data file(s)

Log Files

File Name | Location | Allocated Space, MB | Autogrows | Grows | Maximum File Size, MB
Northwind_log | C:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.ldf | 1 |  | 10 % | Unrestricted
Total: 1 log file(s)

Tables

Name | Owner | Data Size KB | Index Size KB | Creation Date | Cols | Rows | Description
Categories | dbo | 32 | 112 | 08.06.2000 | 4 | 8 | Categories table
CustomerCustomerDemo | dbo | 0 | 0 | 08.06.2000 | 2 | 0 | hello table aaa
CustomerDemographics | dbo | 0 | 0 | 08.06.2000 | 2 | 0 | Customer demografics
Customers | dbo | 80 | 24 | 08.06.2000 | 11 | 91 | cutomer
Employees | dbo | 48 | 232 | 08.06.2000 | 18 | 9 | Employee table
EmployeeTerritories | dbo | 24 | 8 | 08.06.2000 | 2 | 49 |
Order Details | dbo | 208 | 72 | 08.06.2000 | 5 | 2155 | order details
Orders | dbo | 320 | 160 | 08.06.2000 | 14 | 830 |
Products | dbo | 96 | 8 | 08.06.2000 | 10 | 77 |
Region | dbo | 24 | 8 | 08.06.2000 | 2 | 4 |
Shippers | dbo | 16 | 8 | 08.06.2000 | 3 | 3 |
Suppliers | dbo | 48 | 24 | 08.06.2000 | 12 | 29 |
Territories | dbo | 24 | 8 | 08.06.2000 | 3 | 53 |
Total: 13 table(s)

Views

Name | Owner | Schema bound | Encrypted | Creation Date | Description
Alphabetical list of products | dbo |  |  | 08.06.2000 | Alphabetical list of products view
Category Sales for 1997 | dbo |  |  | 08.06.2000 |
Current Product List | dbo |  |  | 08.06.2000 |
Customer and Suppliers by City | dbo |  |  | 08.06.2000 |
Invoices | dbo |  |  | 08.06.2000 |
Order Details Extended | dbo |  |  | 08.06.2000 |
Order Subtotals | dbo |  |  | 08.06.2000 |
Orders Qry | dbo |  |  | 08.06.2000 | Orders Qry
Product Sales for 1997 | dbo |  |  | 08.06.2000 |
Products Above Average Price | dbo |  |  | 08.06.2000 |
Products by Category | dbo |  |  | 08.06.2000 |
Quarterly Orders | dbo |  |  | 08.06.2000 |
Sales by Category | dbo |  |  | 08.06.2000 |
Sales Totals by Amount | dbo |  |  | 08.06.2000 |
Summary of Sales by Quarter | dbo |  |  | 08.06.2000 |
Summary of Sales by Year | dbo |  |  | 08.06.2000 |
Total: 16 view(s)

Triggers

Name | Owner | Disabled | Instead Of | Table/View | Description
TR_Categories | dbo |  |  | Categories | TR Categories trigger
Total: 1 trigger(s)

Stored procedures

Name | Owner | Description
CustOrderHist | dbo | Alphabetical list of products
CustOrdersDetail | dbo | CustOrdersDetail
CustOrdersOrders | dbo | CustOrdersOrders
Employee Sales by Country | dbo | employee
Sales by Year | dbo |
SalesByCategory | dbo |
Ten Most Expensive Products | dbo |
Total: 7 stored procedure(s)

Functions

Name | Owner | Type | Description
xxxx | dbo | SQL inlined table-valued function | hello
Total: 1 function(s)

Defaults

Name | Owner | Description
phonedflt | dbo | example default
Total: 1 default(s)

Rules

Name | Owner | Description
pattern_rule | dbo | pattern rule
RL_RULE | dbo | example rule
Total: 2 rules(s)

Datatypes

Name | Owner
DT_USERDATATYPE | dbo
Total: 1 datatype(s)

Database Options

Settings: Auto Close:
Settings: Auto Shrink:
Settings: Auto Create Statistics:
Settings: Auto Update Statistics:
Settings: Use Quoted Identifiers:
Settings: Torn Page Detection:
Settings: Recursive Triggers:
Settings: ANSI NULL default:
Access: Read Only:
Access: Single User:
Access: Members of db_owner, dbcreator or sysadmin:
Truncate Log On Checkpoint:
Select Into/Bulkcopy:
Cursor Close On Commit:
Cursors in a Batch are with Local Scope:

See Also

Server overview


List of tables

Tables

Name | Owner | Data Size KB | Index Size KB | Creation Date | Cols | Rows | Description
Categories | dbo | 32 | 112 | 08.06.2000 | 4 | 8 | Categories table
CustomerCustomerDemo | dbo | 0 | 0 | 08.06.2000 | 2 | 0 | hello table aaa
CustomerDemographics | dbo | 0 | 0 | 08.06.2000 | 2 | 0 | Customer demografics
Customers | dbo | 80 | 24 | 08.06.2000 | 11 | 91 | cutomer
Employees | dbo | 48 | 232 | 08.06.2000 | 18 | 9 | Employee table
EmployeeTerritories | dbo | 24 | 8 | 08.06.2000 | 2 | 49 |
Order Details | dbo | 208 | 72 | 08.06.2000 | 5 | 2155 | order details
Orders | dbo | 320 | 160 | 08.06.2000 | 14 | 830 |
Products | dbo | 96 | 8 | 08.06.2000 | 10 | 77 |
Region | dbo | 24 | 8 | 08.06.2000 | 2 | 4 |
Shippers | dbo | 16 | 8 | 08.06.2000 | 3 | 3 |
Suppliers | dbo | 48 | 24 | 08.06.2000 | 12 | 29 |
Territories | dbo | 24 | 8 | 08.06.2000 | 3 | 53 |
Total: 13 table(s)

See Also

Database overview


Table: Categories

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 112
Index Size KB: | 32
Rows: | 8
Description: | Categories table

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
CategoryID | int | 4 |  |  |  |  | CategoryID column description
CategoryName | nvarchar | 30 |  |  |  |  | CategoryName column description
Description | ntext | 16 |  |  |  |  | Description column
Picture | image | 16 |  |  |  |  | picture column
Total: 4 column(s)

Identity column

Name | Seed | Increment | Not for replication
CategoryID | 1 | 1 |

Indexes

Index | Primary | Unique | Description
PK_Categories |  |  | PK_Categories primary key
CategoryName |  |  | Name index
Total: 2 index(es)

Triggers

Name | Owner | Instead Of | Disabled | Table/View | Description
TR_Categories | dbo |  |  | TR_Categories | TR Categories trigger
Total: 1 trigger(s)

Referencing Tables

Table | Foreign Key | Primary Key or Unique Constraint
Products | FK_Products_Categories | PK_Categories
Total: 1 table(s)

Objects that depend on [dbo].[Categories]

Object Name | Owner | Object Type | Dep Level
Products | dbo | Table | 1
TR_Categories | dbo | Trigger | 1
Alphabetical list of products | dbo | View | 2
Current Product List | dbo | View | 2
Products Above Average Price | dbo | View | 2
Products by Category | dbo | View | 2
Order Details | dbo | Table | 2
Ten Most Expensive Products | dbo | Procedure | 2
Invoices | dbo | View | 3
Order Details Extended | dbo | View | 3
Order Subtotals | dbo | View | 3
Product Sales for 1997 | dbo | View | 3
CustOrderHist | dbo | Procedure | 3
CustOrdersDetail | dbo | Procedure | 3
SalesByCategory | dbo | Procedure | 3
Category Sales for 1997 | dbo | View | 4
Sales by Category | dbo | View | 4
Sales Totals by Amount | dbo | View | 4
Summary of Sales by Quarter | dbo | View | 4
Summary of Sales by Year | dbo | View | 4
Employee Sales by Country | dbo | Procedure | 4
Sales by Year | dbo | Procedure | 4
Total: 22 object(s)

Extended Properties

Name | Value
MS_Description1 | Categories table
Total: 1 extended property(s)

SQL

CREATE TABLE [Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Picture] [image] NULL ,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

See Also

List of tables


List of triggers on [Categories]

Triggers

Name | Owner | Disabled | Instead Of | Description
TR_Categories | dbo |  |  | TR Categories trigger
Total: 1 trigger(s)

See Also

Table [Categories]


Trigger: TR_Categories on table Categories

Properties

Owner: | dbo
Table/View: | TR_Categories
Disabled: |
Encrypted: |
Creation Date: | 09.04.2006
Description: | TR Categories trigger

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Type

Instead of:
Insert:
Update:
Delete:

Objects that [dbo].[TR_Categories] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Total: 1 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER TR_Categories
ON Categories
FOR INSERT, UPDATE
AS
BEGIN
RAISERROR ('These columns should never be updated', 16, 1)
ROLLBACK TRANSACTION
END

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of triggers


List of indexes on [Categories]

Indexes

Index | Primary | Unique | Description
PK_Categories |  |  | PK_Categories primary key
CategoryName |  |  | Name index
Total: 2 index(es)

See Also

Table [Categories]


Index: PK_Categories on table dbo.Categories

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 32
Fill Factor: | 0
Don't Recompute Statistics: |
Description: | PK_Categories primary key

Index Columns

Name | Descending
CategoryID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE UNIQUE CLUSTERED INDEX [PK_Categories] ON [dbo].[Categories]([CategoryID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CategoryName on table dbo.Categories

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: | Name index

Index Columns

Name | Descending
CategoryName | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [CategoryName] ON [dbo].[Categories]([CategoryName]) ON [PRIMARY]
GO

See Also

List of indexes


Table: CustomerCustomerDemo

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 0
Index Size KB: | 0
Rows: | 0
Description: | hello table aaa

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
CustomerID | nchar | 10 |  |  |  |  | CustomerID column
CustomerTypeID | nchar | 20 |  |  |  |  | customer type id
Total: 2 column(s)

Indexes

Index | Primary | Unique | Description
PK_CustomerCustomerDemo |  |  | PK_CustomerCustomerDemo description
Total: 1 index(es)

Referenced Tables

Table | Foreign Key | Primary Key or Unique Constraint
CustomerDemographics | FK_CustomerCustomerDemo | PK_CustomerDemographics
Customers | FK_CustomerCustomerDemo_Customers | PK_Customers
Total: 2 table(s)

Objects that [dbo].[CustomerCustomerDemo] depends on

Object Name | Owner | Object Type | Dep Level
CustomerDemographics | dbo | Table | 1
Customers | dbo | Table | 1
Total: 2 object(s)

SQL

CREATE TABLE [CustomerCustomerDemo] (
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY NONCLUSTERED
(
[CustomerID],
[CustomerTypeID]
) ON [PRIMARY] ,
CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY
(
[CustomerTypeID]
) REFERENCES [CustomerDemographics] (
[CustomerTypeID]
),
CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [Customers] (
[CustomerID]
)
) ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [CustomerCustomerDemo]

Indexes

Index | Primary | Unique | Description
PK_CustomerCustomerDemo |  |  | PK_CustomerCustomerDemo description
Total: 1 index(es)

See Also

Table [CustomerCustomerDemo]


Index: PK_CustomerCustomerDemo on table dbo.CustomerCustomerDemo

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 0
Fill Factor: | 0
Don't Recompute Statistics: |
Description: | PK_CustomerCustomerDemo description

Index Columns

Name | Descending
CustomerID | 0
CustomerTypeID | 0
Total: 2 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE UNIQUE INDEX [PK_CustomerCustomerDemo] ON [dbo].[CustomerCustomerDemo]([CustomerID], [CustomerTypeID]) ON [PRIMARY]
GO

See Also

List of indexes


List of relationships on [CustomerCustomerDemo]

Relationships

Table | Foreign Key | Primary Key or Unique Constraint
CustomerDemographics | FK_CustomerCustomerDemo | PK_CustomerDemographics
Customers | FK_CustomerCustomerDemo_Customers | PK_Customers
Total: 2 relationship(s)

See Also

Table [CustomerCustomerDemo]


Relationship: [FK_CustomerCustomerDemo] on [CustomerCustomerDemo]

Properties

Primary Table Owner: | dbo
Primary Table: | CustomerDemographics
Primary Key or Unique Constraint: | PK_CustomerDemographics
Delete Cascade: |
Update Cascade: |
Description: | FK_CustomerCustomerDemo description

Relationship Columns

Column | Reference Column | Type
CustomerTypeID | CustomerTypeID | nchar
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[CustomerCustomerDemo] ADD CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY
(
[CustomerTypeID]
) REFERENCES [CustomerDemographics] (
[CustomerTypeID]
)
GO

See Also

Table [CustomerDemographics]
Table [CustomerCustomerDemo]


Relationship: [FK_CustomerCustomerDemo_Customers] on [CustomerCustomerDemo]

Properties

Primary Table Owner: | dbo
Primary Table: | Customers
Primary Key or Unique Constraint: | PK_Customers
Delete Cascade: |
Update Cascade: |
Description: | FK_CustomerCustomerDemo_Customers description

Relationship Columns

Column | Reference Column | Type
CustomerID | CustomerID | nchar
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[CustomerCustomerDemo] ADD CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [Customers] (
[CustomerID]
)
GO

See Also

Table [Customers]
Table [CustomerCustomerDemo]


Table: CustomerDemographics

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 0
Index Size KB: | 0
Rows: | 0
Description: | Customer demografics

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
CustomerTypeID | nchar | 20 |  |  |  |  | CustomerTypeID column
CustomerDesc | ntext | 16 |  |  |  |  | CustomerDesc column
Total: 2 column(s)

Indexes

Index | Primary | Unique | Description
PK_CustomerDemographics |  |  | PK_CustomerDemographics primary index constraint
Total: 1 index(es)

Referencing Tables

Table | Foreign Key | Primary Key or Unique Constraint
CustomerCustomerDemo | FK_CustomerCustomerDemo | PK_CustomerDemographics
Total: 1 table(s)

Objects that depend on [dbo].[CustomerDemographics]

Object Name | Owner | Object Type | Dep Level
CustomerCustomerDemo | dbo | Table | 1
Total: 1 object(s)

SQL

CREATE TABLE [CustomerDemographics] (
[CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CustomerDesc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY NONCLUSTERED
(
[CustomerTypeID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [CustomerDemographics]

Indexes

Index | Primary | Unique | Description
PK_CustomerDemographics |  |  | PK_CustomerDemographics primary index constraint
Total: 1 index(es)

See Also

Table [CustomerDemographics]


Index: PK_CustomerDemographics on table dbo.CustomerDemographics

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 0
Fill Factor: | 0
Don't Recompute Statistics: |
Description: | PK_CustomerDemographics primary index constraint

Index Columns

Name | Descending
CustomerTypeID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE UNIQUE INDEX [PK_CustomerDemographics] ON [dbo].[CustomerDemographics]([CustomerTypeID]) ON [PRIMARY]
GO

See Also

List of indexes


Table: Customers

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 24
Index Size KB: | 80
Rows: | 91
Description: | cutomer

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
CustomerID | nchar | 10 |  |  |  |  |
CompanyName | nvarchar | 80 |  |  |  |  | company name
ContactName | nvarchar | 60 |  |  |  |  | contact name
ContactTitle | nvarchar | 60 |  |  |  |  | contact title
Address | nvarchar | 120 |  |  |  |  |
City | nvarchar | 30 |  |  |  |  |
Region | nvarchar | 30 |  |  |  |  |
PostalCode | nvarchar | 20 |  |  |  |  |
Country | nvarchar | 30 |  |  |  |  |
Phone | nvarchar | 48 |  |  |  |  |
Fax | nvarchar | 48 |  |  |  |  |
Total: 11 column(s)

Indexes

Index | Primary | Unique | Description
PK_Customers |  |  | pk customers
City |  |  | city
CompanyName |  |  |
PostalCode |  |  |
Region |  |  |
Total: 5 index(es)

Referencing Tables

Table | Foreign Key | Primary Key or Unique Constraint
Orders | FK_Orders_Customers | PK_Customers
CustomerCustomerDemo | FK_CustomerCustomerDemo_Customers | PK_Customers
Total: 2 table(s)

Objects that depend on [dbo].[Customers]

Object Name | Owner | Object Type | Dep Level
Customer and Suppliers by City | dbo | View | 1
CustomerCustomerDemo | dbo | Table | 1
Orders | dbo | Table | 1
Orders Qry | dbo | View | 2
Quarterly Orders | dbo | View | 2
Order Details | dbo | Table | 2
CustOrdersOrders | dbo | Procedure | 2
Invoices | dbo | View | 3
Order Details Extended | dbo | View | 3
Order Subtotals | dbo | View | 3
Product Sales for 1997 | dbo | View | 3
CustOrderHist | dbo | Procedure | 3
CustOrdersDetail | dbo | Procedure | 3
SalesByCategory | dbo | Procedure | 3
Category Sales for 1997 | dbo | View | 4
Sales by Category | dbo | View | 4
Sales Totals by Amount | dbo | View | 4
Summary of Sales by Quarter | dbo | View | 4
Summary of Sales by Year | dbo | View | 4
Employee Sales by Country | dbo | Procedure | 4
Sales by Year | dbo | Procedure | 4
Total: 21 object(s)

SQL

CREATE TABLE [Customers] (
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [Customers]

Indexes

Index | Primary | Unique | Description
PK_Customers |  |  | pk customers
City |  |  | city
CompanyName |  |  |
PostalCode |  |  |
Region |  |  |
Total: 5 index(es)

See Also

Table [Customers]


Index: PK_Customers on table dbo.Customers

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 80
Fill Factor: | 0
Don't Recompute Statistics: |
Description: | pk customers

Index Columns

Name | Descending
CustomerID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE UNIQUE CLUSTERED INDEX [PK_Customers] ON [dbo].[Customers]([CustomerID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: City on table dbo.Customers

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: | city

Index Columns

Name | Descending
City | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [City] ON [dbo].[Customers]([City]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CompanyName on table dbo.Customers

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
CompanyName | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [CompanyName] ON [dbo].[Customers]([CompanyName]) ON [PRIMARY]
GO

See Also

List of indexes


Index: PostalCode on table dbo.Customers

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
PostalCode | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [PostalCode] ON [dbo].[Customers]([PostalCode]) ON [PRIMARY]
GO

See Also

List of indexes


Index: Region on table dbo.Customers

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
Region | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [Region] ON [dbo].[Customers]([Region]) ON [PRIMARY]
GO

See Also

List of indexes


Table: Employees

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 232
Index Size KB: | 48
Rows: | 9
Description: | Employee table

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
EmployeeID | int | 4 |  |  |  |  | EmployeeID column
LastName | nvarchar | 40 |  |  |  |  |
FirstName | nvarchar | 20 |  |  |  |  |
Title | nvarchar | 60 |  |  |  |  |
TitleOfCourtesy | nvarchar | 50 |  |  |  |  |
BirthDate | datetime | 8 |  |  |  |  |
HireDate | datetime | 8 |  |  |  |  |
Address | nvarchar | 120 |  |  |  |  |
City | nvarchar | 30 |  |  |  |  |
Region | nvarchar | 30 |  |  |  |  |
PostalCode | nvarchar | 20 |  |  |  |  |
Country | nvarchar | 30 |  |  |  |  |
HomePhone | nvarchar | 48 |  |  |  |  |
Extension | nvarchar | 8 |  |  |  |  |
Photo | image | 16 |  |  |  |  |
Notes | ntext | 16 |  |  |  |  |
ReportsTo | int | 4 |  |  |  |  |
PhotoPath | nvarchar | 510 |  |  |  |  |
Total: 18 column(s)

Identity column

Name | Seed | Increment | Not for replication
EmployeeID | 1 | 1 |

Indexes

Index | Primary | Unique | Description
PK_Employees |  |  | PK_Employees index
LastName |  |  | LastName index
PostalCode |  |  | Postal Code index
Total: 3 index(es)

Check Constraints

Name | Expression
CK_Birthdate | ([BirthDate] < getdate())
Total: 1 constraint(s)

Referencing Tables

Table | Foreign Key | Primary Key or Unique Constraint
Orders | FK_Orders_Employees | PK_Employees
EmployeeTerritories | FK_EmployeeTerritories_Employees | PK_Employees
Employees | FK_Employees_Employees | PK_Employees
Total: 3 table(s)

Referenced Tables

Table | Foreign Key | Primary Key or Unique Constraint
Employees | FK_Employees_Employees | PK_Employees
Total: 1 table(s)

Objects that depend on [dbo].[Employees]

Object Name | Owner | Object Type | Dep Level
EmployeeTerritories | dbo | Table | 1
Orders | dbo | Table | 1
Orders Qry | dbo | View | 2
Quarterly Orders | dbo | View | 2
Order Details | dbo | Table | 2
CustOrdersOrders | dbo | Procedure | 2
Invoices | dbo | View | 3
Order Details Extended | dbo | View | 3
Order Subtotals | dbo | View | 3
Product Sales for 1997 | dbo | View | 3
CustOrderHist | dbo | Procedure | 3
CustOrdersDetail | dbo | Procedure | 3
SalesByCategory | dbo | Procedure | 3
Category Sales for 1997 | dbo | View | 4
Sales by Category | dbo | View | 4
Sales Totals by Amount | dbo | View | 4
Summary of Sales by Quarter | dbo | View | 4
Summary of Sales by Year | dbo | View | 4
Employee Sales by Country | dbo | Procedure | 4
Sales by Year | dbo | Procedure | 4
Total: 20 object(s)

SQL

CREATE TABLE [Employees] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[LastName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TitleOfCourtesy] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BirthDate] [datetime] NULL ,
[HireDate] [datetime] NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomePhone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Extension] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Photo] [image] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReportsTo] [int] NULL ,
[PhotoPath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmployeeID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Employees_Employees] FOREIGN KEY
(
[ReportsTo]
) REFERENCES [Employees] (
[EmployeeID]
),
CONSTRAINT [CK_Birthdate] CHECK ([BirthDate] < getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [Employees]

Indexes

Index | Primary | Unique | Description
PK_Employees |  |  | PK_Employees index
LastName |  |  | LastName index
PostalCode |  |  | Postal Code index
Total: 3 index(es)

See Also

Table [Employees]


Index: PK_Employees on table dbo.Employees

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 48
Fill Factor: | 0
Don't Recompute Statistics: |
Description: | PK_Employees index

Index Columns

Name | Descending
EmployeeID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE UNIQUE CLUSTERED INDEX [PK_Employees] ON [dbo].[Employees]([EmployeeID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: LastName on table dbo.Employees

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: | LastName index

Index Columns

Name | Descending
LastName | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [LastName] ON [dbo].[Employees]([LastName]) ON [PRIMARY]
GO

See Also

List of indexes


Index: PostalCode on table dbo.Employees

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: | Postal Code index

Index Columns

Name | Descending
PostalCode | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [PostalCode] ON [dbo].[Employees]([PostalCode]) ON [PRIMARY]
GO

See Also

List of indexes


List of relationships on [Employees]

Relationships

Table | Foreign Key | Primary Key or Unique Constraint
Employees | FK_Employees_Employees | PK_Employees
Total: 1 relationship(s)

See Also

Table [Employees]


Relationship: [FK_Employees_Employees] on [Employees]

Properties

Primary Table Owner: | dbo
Primary Table: | Employees
Primary Key or Unique Constraint: | PK_Employees
Delete Cascade: |
Update Cascade: |
Description: | FK_Employees_Employees foreign key

Relationship Columns

Column | Reference Column | Type
ReportsTo | EmployeeID | int
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Employees] ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY
(
[ReportsTo]
) REFERENCES [Employees] (
[EmployeeID]
)
GO

See Also

Table [Employees]
Table [Employees]


Table: EmployeeTerritories

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 8
Index Size KB: | 24
Rows: | 49
Description: |

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
EmployeeID | int | 4 |  |  |  |  |
TerritoryID | nvarchar | 40 |  |  |  |  |
Total: 2 column(s)

Indexes

Index | Primary | Unique | Description
PK_EmployeeTerritories |  |  |
_WA_Sys_TerritoryID_36B12243 |  |  |
Total: 2 index(es)

Referenced Tables

Table | Foreign Key | Primary Key or Unique Constraint
Employees | FK_EmployeeTerritories_Employees | PK_Employees
Territories | FK_EmployeeTerritories_Territories | PK_Territories
Total: 2 table(s)

Objects that [dbo].[EmployeeTerritories] depends on

Object Name | Owner | Object Type | Dep Level
Employees | dbo | Table | 1
Region | dbo | Table | 1
Territories | dbo | Table | 2
Total: 3 object(s)

SQL

CREATE TABLE [EmployeeTerritories] (
[EmployeeID] [int] NOT NULL ,
[TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY NONCLUSTERED
(
[EmployeeID],
[TerritoryID]
) ON [PRIMARY] ,
CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employees] (
[EmployeeID]
),
CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY
(
[TerritoryID]
) REFERENCES [Territories] (
[TerritoryID]
)
) ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [EmployeeTerritories]

Indexes

Index | Primary | Unique | Description
PK_EmployeeTerritories |  |  |
_WA_Sys_TerritoryID_36B12243 |  |  |
Total: 2 index(es)

See Also

Table [EmployeeTerritories]


Index: PK_EmployeeTerritories on table dbo.EmployeeTerritories

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
EmployeeID | 0
TerritoryID | 0
Total: 2 column(s)

Statistics

Updated: | Aug 6 2000 1:34AM
Average key length: | 14
Density: | 0
Rows in the table: | 49
Rows sampled for statistics data: | 49
Distribution steps: | 9

SQL

CREATE UNIQUE INDEX [PK_EmployeeTerritories] ON [dbo].[EmployeeTerritories]([EmployeeID], [TerritoryID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: _WA_Sys_TerritoryID_36B12243 on table dbo.EmployeeTerritories

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 0
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
TerritoryID | 0
Total: 1 column(s)

Statistics

Updated: | Aug 6 2000 1:34AM
Average key length: | 10
Density: | 0
Rows in the table: | 49
Rows sampled for statistics data: | 49
Distribution steps: | 49

SQL

See Also

List of indexes


List of relationships on [EmployeeTerritories]

Relationships

Table | Foreign Key | Primary Key or Unique Constraint
Employees | FK_EmployeeTerritories_Employees | PK_Employees
Territories | FK_EmployeeTerritories_Territories | PK_Territories
Total: 2 relationship(s)

See Also

Table [EmployeeTerritories]


Relationship: [FK_EmployeeTerritories_Employees] on [EmployeeTerritories]

Properties

Primary Table Owner: | dbo
Primary Table: | Employees
Primary Key or Unique Constraint: | PK_Employees
Delete Cascade: |
Update Cascade: |
Description: |

Relationship Columns

Column | Reference Column | Type
EmployeeID | EmployeeID | int
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[EmployeeTerritories] ADD CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employees] (
[EmployeeID]
)
GO

See Also

Table [Employees]
Table [EmployeeTerritories]


Relationship: [FK_EmployeeTerritories_Territories] on [EmployeeTerritories]

Properties

Primary Table Owner: | dbo
Primary Table: | Territories
Primary Key or Unique Constraint: | PK_Territories
Delete Cascade: |
Update Cascade: |
Description: |

Relationship Columns

Column | Reference Column | Type
TerritoryID | TerritoryID | nvarchar
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[EmployeeTerritories] ADD CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY
(
[TerritoryID]
) REFERENCES [Territories] (
[TerritoryID]
)
GO

See Also

Table [Territories]
Table [EmployeeTerritories]


Table: Order Details

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 72
Index Size KB: | 208
Rows: | 2155
Description: | order details

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
OrderID | int | 4 |  |  |  |  |
ProductID | int | 4 |  |  |  |  |
UnitPrice | money | 8 |  | (0) |  |  |
Quantity | smallint | 2 |  | (1) |  |  |
Discount | real | 4 |  | (0) |  |  |
Total: 5 column(s)

Indexes

Index | Primary | Unique | Description
PK_Order_Details |  |  |
OrderID |  |  |
OrdersOrder_Details |  |  |
ProductID |  |  |
ProductsOrder_Details |  |  |
Total: 5 index(es)

Check Constraints

Name | Expression
CK_Discount | ([Discount] >= 0 and [Discount] <= 1)
CK_Quantity | ([Quantity] > 0)
CK_UnitPrice | ([UnitPrice] >= 0)
Total: 3 constraint(s)

Referenced Tables

Table | Foreign Key | Primary Key or Unique Constraint
Orders | FK_Order_Details_Orders | PK_Orders
Products | FK_Order_Details_Products | PK_Products
Total: 2 table(s)

Objects that [dbo].[Order Details] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Total: 7 object(s)

Objects that depend on [dbo].[Order Details]

Object Name | Owner | Object Type | Dep Level
Invoices | dbo | View | 1
Order Details Extended | dbo | View | 1
Order Subtotals | dbo | View | 1
Product Sales for 1997 | dbo | View | 1
CustOrderHist | dbo | Procedure | 1
CustOrdersDetail | dbo | Procedure | 1
SalesByCategory | dbo | Procedure | 1
Category Sales for 1997 | dbo | View | 2
Sales by Category | dbo | View | 2
Sales Totals by Amount | dbo | View | 2
Summary of Sales by Quarter | dbo | View | 2
Summary of Sales by Year | dbo | View | 2
Employee Sales by Country | dbo | Procedure | 2
Sales by Year | dbo | Procedure | 2
Total: 14 object(s)

SQL

CREATE TABLE [Order Details] (
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[UnitPrice] [money] NOT NULL CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0),
[Quantity] [smallint] NOT NULL CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1),
[Discount] [real] NOT NULL CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0),
CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED
(
[OrderID],
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [Orders] (
[OrderID]
),
CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [Products] (
[ProductID]
),
CONSTRAINT [CK_Discount] CHECK ([Discount] >= 0 and [Discount] <= 1),
CONSTRAINT [CK_Quantity] CHECK ([Quantity] > 0),
CONSTRAINT [CK_UnitPrice] CHECK ([UnitPrice] >= 0)
) ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [Order Details]

Indexes

Index | Primary | Unique | Description
PK_Order_Details |  |  |
OrderID |  |  |
OrdersOrder_Details |  |  |
ProductID |  |  |
ProductsOrder_Details |  |  |
Total: 5 index(es)

See Also

Table [Order Details]


Index: PK_Order_Details on table dbo.Order Details

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 208
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
OrderID | 0
ProductID | 0
Total: 2 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE UNIQUE CLUSTERED INDEX [PK_Order_Details] ON [dbo].[Order Details]([OrderID], [ProductID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: OrderID on table dbo.Order Details

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
OrderID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [OrderID] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: OrdersOrder_Details on table dbo.Order Details

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
OrderID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [OrdersOrder_Details] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ProductID on table dbo.Order Details

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
ProductID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [ProductID] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ProductsOrder_Details on table dbo.Order Details

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
ProductID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [ProductsOrder_Details] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]
GO

See Also

List of indexes


List of relationships on [Order Details]

Relationships

Table | Foreign Key | Primary Key or Unique Constraint
Orders | FK_Order_Details_Orders | PK_Orders
Products | FK_Order_Details_Products | PK_Products
Total: 2 relationship(s)

See Also

Table [Order Details]


Relationship: [FK_Order_Details_Orders] on [Order Details]

Properties

Primary Table Owner: | dbo
Primary Table: | Orders
Primary Key or Unique Constraint: | PK_Orders
Delete Cascade: |
Update Cascade: |
Description: |

Relationship Columns

Column | Reference Column | Type
OrderID | OrderID | int
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Order Details] ADD CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [Orders] (
[OrderID]
)
GO

See Also

Table [Orders]
Table [Order Details]


Relationship: [FK_Order_Details_Products] on [Order Details]

Properties

Primary Table Owner: | dbo
Primary Table: | Products
Primary Key or Unique Constraint: | PK_Products
Delete Cascade: |
Update Cascade: |
Description: |

Relationship Columns

Column | Reference Column | Type
ProductID | ProductID | int
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Order Details] ADD CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [Products] (
[ProductID]
)
GO

See Also

Table [Products]
Table [Order Details]


Table: Orders

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 160
Index Size KB: | 320
Rows: | 830
Description: |

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
OrderID | int | 4 |  |  |  |  | 89898989
CustomerID | nchar | 10 |  |  |  |  | customer id
EmployeeID | int | 4 |  |  |  |  | employee id
OrderDate | datetime | 8 |  |  |  |  | order date
RequiredDate | datetime | 8 |  |  |  |  | RequiredDate1
ShippedDate | datetime | 8 |  |  |  |  | shippeddate
ShipVia | int | 4 |  |  |  |  |
Freight | money | 8 |  | (0) |  |  |
ShipName | nvarchar | 80 |  |  |  |  |
ShipAddress | nvarchar | 120 |  |  |  |  |
ShipCity | nvarchar | 30 |  |  |  |  |
ShipRegion | nvarchar | 30 |  |  |  |  | ship region
ShipPostalCode | nvarchar | 20 |  |  |  |  |
ShipCountry | nvarchar | 30 |  |  |  |  |
Total: 14 column(s)

Identity column

Name | Seed | Increment | Not for replication
OrderID | 1 | 1 |

Indexes

Index | Primary | Unique | Description
PK_Orders |  |  |
CustomerID |  |  |
CustomersOrders |  |  |
EmployeeID |  |  |
EmployeesOrders |  |  |
OrderDate |  |  |
ShippedDate |  |  |
ShippersOrders |  |  |
ShipPostalCode |  |  |
Total: 9 index(es)

Referencing Tables

Table | Foreign Key | Primary Key or Unique Constraint
Order Details | FK_Order_Details_Orders | PK_Orders
Total: 1 table(s)

Referenced Tables

Table | Foreign Key | Primary Key or Unique Constraint
Customers | FK_Orders_Customers | PK_Customers
Employees | FK_Orders_Employees | PK_Employees
Shippers | FK_Orders_Shippers | PK_Shippers
Total: 3 table(s)

Objects that [dbo].[Orders] depends on

Object Name | Owner | Object Type | Dep Level
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Total: 3 object(s)

Objects that depend on [dbo].[Orders]

Object Name | Owner | Object Type | Dep Level
Orders Qry | dbo | View | 1
Quarterly Orders | dbo | View | 1
Order Details | dbo | Table | 1
CustOrdersOrders | dbo | Procedure | 1
Invoices | dbo | View | 2
Order Details Extended | dbo | View | 2
Order Subtotals | dbo | View | 2
Product Sales for 1997 | dbo | View | 2
CustOrderHist | dbo | Procedure | 2
CustOrdersDetail | dbo | Procedure | 2
SalesByCategory | dbo | Procedure | 2
Category Sales for 1997 | dbo | View | 3
Sales by Category | dbo | View | 3
Sales Totals by Amount | dbo | View | 3
Summary of Sales by Quarter | dbo | View | 3
Summary of Sales by Year | dbo | View | 3
Employee Sales by Country | dbo | Procedure | 3
Sales by Year | dbo | Procedure | 3
Total: 18 object(s)

SQL

CREATE TABLE [Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT (0),
[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employees] (
[EmployeeID]
),
CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
(
[ShipVia]
) REFERENCES [Shippers] (
[ShipperID]
)
) ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [Orders]

Indexes

Index | Primary | Unique | Description
PK_Orders |  |  |
CustomerID |  |  |
CustomersOrders |  |  |
EmployeeID |  |  |
EmployeesOrders |  |  |
OrderDate |  |  |
ShippedDate |  |  |
ShippersOrders |  |  |
ShipPostalCode |  |  |
Total: 9 index(es)

See Also

Table [Orders]


Index: PK_Orders on table dbo.Orders

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 320
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
OrderID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE UNIQUE CLUSTERED INDEX [PK_Orders] ON [dbo].[Orders]([OrderID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CustomerID on table dbo.Orders

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
CustomerID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CustomersOrders on table dbo.Orders

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
CustomerID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: EmployeeID on table dbo.Orders

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
EmployeeID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: EmployeesOrders on table dbo.Orders

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
EmployeeID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: OrderDate on table dbo.Orders

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
OrderDate | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ShippedDate on table dbo.Orders

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
ShippedDate | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ShippersOrders on table dbo.Orders

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
ShipVia | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ShipPostalCode on table dbo.Orders

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
ShipPostalCode | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]
GO

See Also

List of indexes


List of relationships on [Orders]

Relationships

Table | Foreign Key | Primary Key or Unique Constraint
Customers | FK_Orders_Customers | PK_Customers
Employees | FK_Orders_Employees | PK_Employees
Shippers | FK_Orders_Shippers | PK_Shippers
Total: 3 relationship(s)

See Also

Table [Orders]


Relationship: [FK_Orders_Customers] on [Orders]

Properties

Primary Table Owner: | dbo
Primary Table: | Customers
Primary Key or Unique Constraint: | PK_Customers
Delete Cascade: |
Update Cascade: |
Description: | 1

Relationship Columns

Column | Reference Column | Type
CustomerID | CustomerID | nchar
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [Customers] (
[CustomerID]
)
GO

See Also

Table [Customers]
Table [Orders]


Relationship: [FK_Orders_Employees] on [Orders]

Properties

Primary Table Owner: | dbo
Primary Table: | Employees
Primary Key or Unique Constraint: | PK_Employees
Delete Cascade: |
Update Cascade: |
Description: | 2

Relationship Columns

Column | Reference Column | Type
EmployeeID | EmployeeID | int
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employees] (
[EmployeeID]
)
GO

See Also

Table [Employees]
Table [Orders]


Relationship: [FK_Orders_Shippers] on [Orders]

Properties

Primary Table Owner: | dbo
Primary Table: | Shippers
Primary Key or Unique Constraint: | PK_Shippers
Delete Cascade: |
Update Cascade: |
Description: | 3

Relationship Columns

Column | Reference Column | Type
ShipVia | ShipperID | int
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
(
[ShipVia]
) REFERENCES [Shippers] (
[ShipperID]
)
GO

See Also

Table [Shippers]
Table [Orders]


Table: Products

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 8
Index Size KB: | 96
Rows: | 77
Description: |

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
ProductID | int | 4 |  |  |  |  |
ProductName | nvarchar | 80 |  |  |  |  |
SupplierID | int | 4 |  |  |  |  |
CategoryID | int | 4 |  |  |  |  |
QuantityPerUnit | nvarchar | 40 |  |  |  |  |
UnitPrice | money | 8 |  | (0) |  |  |
UnitsInStock | smallint | 2 |  | (0) |  |  |
UnitsOnOrder | smallint | 2 |  | (0) |  |  |
ReorderLevel | smallint | 2 |  | (0) |  |  |
Discontinued | bit | 1 |  | (0) |  |  |
Total: 10 column(s)

Identity column

Name | Seed | Increment | Not for replication
ProductID | 1 | 1 |

Indexes

Index | Primary | Unique | Description
PK_Products |  |  |
CategoriesProducts |  |  |
CategoryID |  |  |
ProductName |  |  |
SupplierID |  |  |
SuppliersProducts |  |  |
_WA_Sys_Discontinued_07020F21 |  |  |
Total: 7 index(es)

Check Constraints

Name | Expression
CK_Products_UnitPrice | ([UnitPrice] >= 0)
CK_ReorderLevel | ([ReorderLevel] >= 0)
CK_UnitsInStock | ([UnitsInStock] >= 0)
CK_UnitsOnOrder | ([UnitsOnOrder] >= 0)
Total: 4 constraint(s)

Referencing Tables

Table | Foreign Key | Primary Key or Unique Constraint
Order Details | FK_Order_Details_Products | PK_Products
Total: 1 table(s)

Referenced Tables

Table | Foreign Key | Primary Key or Unique Constraint
Categories | FK_Products_Categories | PK_Categories
Suppliers | FK_Products_Suppliers | PK_Suppliers
Total: 2 table(s)

Objects that [dbo].[Products] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Suppliers | dbo | Table | 1
Total: 2 object(s)

Objects that depend on [dbo].[Products]

Object Name | Owner | Object Type | Dep Level
Alphabetical list of products | dbo | View | 1
Current Product List | dbo | View | 1
Products Above Average Price | dbo | View | 1
Products by Category | dbo | View | 1
Order Details | dbo | Table | 1
Ten Most Expensive Products | dbo | Procedure | 1
Invoices | dbo | View | 2
Order Details Extended | dbo | View | 2
Order Subtotals | dbo | View | 2
Product Sales for 1997 | dbo | View | 2
CustOrderHist | dbo | Procedure | 2
CustOrdersDetail | dbo | Procedure | 2
SalesByCategory | dbo | Procedure | 2
Category Sales for 1997 | dbo | View | 3
Sales by Category | dbo | View | 3
Sales Totals by Amount | dbo | View | 3
Summary of Sales by Quarter | dbo | View | 3
Summary of Sales by Year | dbo | View | 3
Employee Sales by Country | dbo | Procedure | 3
Sales by Year | dbo | Procedure | 3
Total: 20 object(s)

SQL

CREATE TABLE [Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SupplierID] [int] NULL ,
[CategoryID] [int] NULL ,
[QuantityPerUnit] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitPrice] [money] NULL CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0),
[UnitsInStock] [smallint] NULL CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0),
[UnitsOnOrder] [smallint] NULL CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0),
[ReorderLevel] [smallint] NULL CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0),
[Discontinued] [bit] NOT NULL CONSTRAINT [DF_Products_Discontinued] DEFAULT (0),
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Products_Categories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [Categories] (
[CategoryID]
),
CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY
(
[SupplierID]
) REFERENCES [Suppliers] (
[SupplierID]
),
CONSTRAINT [CK_Products_UnitPrice] CHECK ([UnitPrice] >= 0),
CONSTRAINT [CK_ReorderLevel] CHECK ([ReorderLevel] >= 0),
CONSTRAINT [CK_UnitsInStock] CHECK ([UnitsInStock] >= 0),
CONSTRAINT [CK_UnitsOnOrder] CHECK ([UnitsOnOrder] >= 0)
) ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [Products]

Indexes

Index | Primary | Unique | Description
PK_Products |  |  |
CategoriesProducts |  |  |
CategoryID |  |  |
ProductName |  |  |
SupplierID |  |  |
SuppliersProducts |  |  |
_WA_Sys_Discontinued_07020F21 |  |  |
Total: 7 index(es)

See Also

Table [Products]


Index: PK_Products on table dbo.Products

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 96
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
ProductID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE UNIQUE CLUSTERED INDEX [PK_Products] ON [dbo].[Products]([ProductID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CategoriesProducts on table dbo.Products

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
CategoryID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [CategoriesProducts] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CategoryID on table dbo.Products

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
CategoryID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [CategoryID] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ProductName on table dbo.Products

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
ProductName | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [ProductName] ON [dbo].[Products]([ProductName]) ON [PRIMARY]
GO

See Also

List of indexes


Index: SupplierID on table dbo.Products

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
SupplierID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [SupplierID] ON [dbo].[Products]([SupplierID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: SuppliersProducts on table dbo.Products

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
SupplierID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [SuppliersProducts] ON [dbo].[Products]([SupplierID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: _WA_Sys_Discontinued_07020F21 on table dbo.Products

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 0
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
Discontinued | 0
Total: 1 column(s)

Statistics

Updated: | Sep 4 2006 9:09AM
Average key length: | 5
Density: | 0
Rows in the table: | 77
Rows sampled for statistics data: | 77
Distribution steps: | 2

SQL

See Also

List of indexes


List of relationships on [Products]

Relationships

Table | Foreign Key | Primary Key or Unique Constraint
Categories | FK_Products_Categories | PK_Categories
Suppliers | FK_Products_Suppliers | PK_Suppliers
Total: 2 relationship(s)

See Also

Table [Products]


Relationship: [FK_Products_Categories] on [Products]

Properties

Primary Table Owner: | dbo
Primary Table: | Categories
Primary Key or Unique Constraint: | PK_Categories
Delete Cascade: |
Update Cascade: |
Description: |

Relationship Columns

Column | Reference Column | Type
CategoryID | CategoryID | int
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Products] ADD CONSTRAINT [FK_Products_Categories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [Categories] (
[CategoryID]
)
GO

See Also

Table [Categories]
Table [Products]


Relationship: [FK_Products_Suppliers] on [Products]

Properties

Primary Table Owner: | dbo
Primary Table: | Suppliers
Primary Key or Unique Constraint: | PK_Suppliers
Delete Cascade: |
Update Cascade: |
Description: |

Relationship Columns

Column | Reference Column | Type
SupplierID | SupplierID | int
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Products] ADD CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY
(
[SupplierID]
) REFERENCES [Suppliers] (
[SupplierID]
)
GO

See Also

Table [Suppliers]
Table [Products]


Table: Region

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 8
Index Size KB: | 24
Rows: | 4
Description: |

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
RegionID | int | 4 |  |  |  |  |
RegionDescription | nchar | 100 |  |  |  |  |
Total: 2 column(s)

Indexes

Index | Primary | Unique | Description
PK_Region |  |  |
Total: 1 index(es)

Referencing Tables

Table | Foreign Key | Primary Key or Unique Constraint
Territories | FK_Territories_Region | PK_Region
Total: 1 table(s)

Objects that depend on [dbo].[Region]

Object Name | Owner | Object Type | Dep Level
Territories | dbo | Table | 1
EmployeeTerritories | dbo | Table | 2
Total: 2 object(s)

SQL

CREATE TABLE [Region] (
[RegionID] [int] NOT NULL ,
[RegionDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Region] PRIMARY KEY NONCLUSTERED
(
[RegionID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [Region]

Indexes

Index | Primary | Unique | Description
PK_Region |  |  |
Total: 1 index(es)

See Also

Table [Region]


Index: PK_Region on table dbo.Region

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
RegionID | 0
Total: 1 column(s)

Statistics

Updated: | Aug 6 2000 1:34AM
Average key length: | 4
Density: | 0,25
Rows in the table: | 4
Rows sampled for statistics data: | 4
Distribution steps: | 3

SQL

CREATE UNIQUE INDEX [PK_Region] ON [dbo].[Region]([RegionID]) ON [PRIMARY]
GO

See Also

List of indexes


Table: Shippers

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 8
Index Size KB: | 16
Rows: | 3
Description: |

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
ShipperID | int | 4 |  |  |  |  |
CompanyName | nvarchar | 80 |  |  |  |  |
Phone | nvarchar | 48 |  |  |  |  |
Total: 3 column(s)

Identity column

Name | Seed | Increment | Not for replication
ShipperID | 1 | 1 |

Indexes

Index | Primary | Unique | Description
PK_Shippers |  |  |
Total: 1 index(es)

Referencing Tables

Table | Foreign Key | Primary Key or Unique Constraint
Orders | FK_Orders_Shippers | PK_Shippers
Total: 1 table(s)

Objects that depend on [dbo].[Shippers]

Object Name | Owner | Object Type | Dep Level
Orders | dbo | Table | 1
Orders Qry | dbo | View | 2
Quarterly Orders | dbo | View | 2
Order Details | dbo | Table | 2
CustOrdersOrders | dbo | Procedure | 2
Invoices | dbo | View | 3
Order Details Extended | dbo | View | 3
Order Subtotals | dbo | View | 3
Product Sales for 1997 | dbo | View | 3
CustOrderHist | dbo | Procedure | 3
CustOrdersDetail | dbo | Procedure | 3
SalesByCategory | dbo | Procedure | 3
Category Sales for 1997 | dbo | View | 4
Sales by Category | dbo | View | 4
Sales Totals by Amount | dbo | View | 4
Summary of Sales by Quarter | dbo | View | 4
Summary of Sales by Year | dbo | View | 4
Employee Sales by Country | dbo | Procedure | 4
Sales by Year | dbo | Procedure | 4
Total: 19 object(s)

SQL

CREATE TABLE [Shippers] (
[ShipperID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Shippers] PRIMARY KEY CLUSTERED
(
[ShipperID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [Shippers]

Indexes

Index | Primary | Unique | Description
PK_Shippers |  |  |
Total: 1 index(es)

See Also

Table [Shippers]


Index: PK_Shippers on table dbo.Shippers

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
ShipperID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE UNIQUE CLUSTERED INDEX [PK_Shippers] ON [dbo].[Shippers]([ShipperID]) ON [PRIMARY]
GO

See Also

List of indexes


Table: Suppliers

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 24
Index Size KB: | 48
Rows: | 29
Description: |

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
SupplierID | int | 4 |  |  |  |  |
CompanyName | nvarchar | 80 |  |  |  |  |
ContactName | nvarchar | 60 |  |  |  |  |
ContactTitle | nvarchar | 60 |  |  |  |  |
Address | nvarchar | 120 |  |  |  |  |
City | nvarchar | 30 |  |  |  |  |
Region | nvarchar | 30 |  |  |  |  |
PostalCode | nvarchar | 20 |  |  |  |  |
Country | nvarchar | 30 |  |  |  |  |
Phone | nvarchar | 48 |  |  |  |  |
Fax | nvarchar | 48 |  |  |  |  |
HomePage | ntext | 16 |  |  |  |  |
Total: 12 column(s)

Identity column

Name | Seed | Increment | Not for replication
SupplierID | 1 | 1 |

Indexes

Index | Primary | Unique | Description
PK_Suppliers |  |  |
CompanyName |  |  |
PostalCode |  |  |
Total: 3 index(es)

Referencing Tables

Table | Foreign Key | Primary Key or Unique Constraint
Products | FK_Products_Suppliers | PK_Suppliers
Total: 1 table(s)

Objects that depend on [dbo].[Suppliers]

Object Name | Owner | Object Type | Dep Level
Customer and Suppliers by City | dbo | View | 1
Products | dbo | Table | 1
Alphabetical list of products | dbo | View | 2
Current Product List | dbo | View | 2
Products Above Average Price | dbo | View | 2
Products by Category | dbo | View | 2
Order Details | dbo | Table | 2
Ten Most Expensive Products | dbo | Procedure | 2
Invoices | dbo | View | 3
Order Details Extended | dbo | View | 3
Order Subtotals | dbo | View | 3
Product Sales for 1997 | dbo | View | 3
CustOrderHist | dbo | Procedure | 3
CustOrdersDetail | dbo | Procedure | 3
SalesByCategory | dbo | Procedure | 3
Category Sales for 1997 | dbo | View | 4
Sales by Category | dbo | View | 4
Sales Totals by Amount | dbo | View | 4
Summary of Sales by Quarter | dbo | View | 4
Summary of Sales by Year | dbo | View | 4
Employee Sales by Country | dbo | Procedure | 4
Sales by Year | dbo | Procedure | 4
Total: 22 object(s)

SQL

CREATE TABLE [Suppliers] (
[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED
(
[SupplierID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [Suppliers]

Indexes

Index | Primary | Unique | Description
PK_Suppliers |  |  |
CompanyName |  |  |
PostalCode |  |  |
Total: 3 index(es)

See Also

Table [Suppliers]


Index: PK_Suppliers on table dbo.Suppliers

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 48
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
SupplierID | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE UNIQUE CLUSTERED INDEX [PK_Suppliers] ON [dbo].[Suppliers]([SupplierID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CompanyName on table dbo.Suppliers

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
CompanyName | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [CompanyName] ON [dbo].[Suppliers]([CompanyName]) ON [PRIMARY]
GO

See Also

List of indexes


Index: PostalCode on table dbo.Suppliers

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
PostalCode | 0
Total: 1 column(s)

Statistics

Updated:
Average key length:
Density:
Rows in the table:
Rows sampled for statistics data:
Distribution steps:

SQL

CREATE INDEX [PostalCode] ON [dbo].[Suppliers]([PostalCode]) ON [PRIMARY]
GO

See Also

List of indexes


Table: Territories

Properties

Owner: | dbo
Creation Date: | 08.06.2000
Located On: | PRIMARY
Data Size KB: | 8
Index Size KB: | 24
Rows: | 53
Description: |

Columns

Name | Data Type | Length | NULL | Default | IsIdentity | IsGUID | Description
TerritoryID | nvarchar | 40 |  |  |  |  |
TerritoryDescription | nchar | 100 |  |  |  |  |
RegionID | int | 4 |  |  |  |  |
Total: 3 column(s)

Indexes

Index | Primary | Unique | Description
PK_Territories |  |  |
_WA_Sys_RegionID_35BCFE0A |  |  |
Total: 2 index(es)

Referencing Tables

Table | Foreign Key | Primary Key or Unique Constraint
EmployeeTerritories | FK_EmployeeTerritories_Territories | PK_Territories
Total: 1 table(s)

Referenced Tables

Table | Foreign Key | Primary Key or Unique Constraint
Region | FK_Territories_Region | PK_Region
Total: 1 table(s)

Objects that [dbo].[Territories] depends on

Object Name | Owner | Object Type | Dep Level
Region | dbo | Table | 1
Total: 1 object(s)

Objects that depend on [dbo].[Territories]

Object Name | Owner | Object Type | Dep Level
EmployeeTerritories | dbo | Table | 1
Total: 1 object(s)

SQL

CREATE TABLE [Territories] (
[TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TerritoryDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RegionID] [int] NOT NULL ,
CONSTRAINT [PK_Territories] PRIMARY KEY NONCLUSTERED
(
[TerritoryID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Territories_Region] FOREIGN KEY
(
[RegionID]
) REFERENCES [Region] (
[RegionID]
)
) ON [PRIMARY]
GO

See Also

List of tables


List of indexes on [Territories]

Indexes

Index | Primary | Unique | Description
PK_Territories |  |  |
_WA_Sys_RegionID_35BCFE0A |  |  |
Total: 2 index(es)

See Also

Table [Territories]


Index: PK_Territories on table dbo.Territories

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 16
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
TerritoryID | 0
Total: 1 column(s)

Statistics

Updated: | Aug 6 2000 1:34AM
Average key length: | 10
Density: | 1,886792E-02
Rows in the table: | 53
Rows sampled for statistics data: | 53
Distribution steps: | 53

SQL

CREATE UNIQUE INDEX [PK_Territories] ON [dbo].[Territories]([TerritoryID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: _WA_Sys_RegionID_35BCFE0A on table dbo.Territories

Properties

Clustered Index: |
Primary Key Index: |
Unique Index: |
Ignore Duplicates: |
Pad Index: |
Allow Row Locks: |
Allow Page Locks: |
Is computed column in index: |
Located On: | PRIMARY
Size KB: | 0
Fill Factor: | 0
Don't Recompute Statistics: |
Description: |

Index Columns

Name | Descending
RegionID | 0
Total: 1 column(s)

Statistics

Updated: | Aug 6 2000 1:34AM
Average key length: | 4
Density: | 0
Rows in the table: | 53
Rows sampled for statistics data: | 53
Distribution steps: | 4

SQL

See Also

List of indexes


List of relationships on [Territories]

Relationships

Table | Foreign Key | Primary Key or Unique Constraint
Region | FK_Territories_Region | PK_Region
Total: 1 relationship(s)

See Also

Table [Territories]


Relationship: [FK_Territories_Region] on [Territories]

Properties

Primary Table Owner: | dbo
Primary Table: | Region
Primary Key or Unique Constraint: | PK_Region
Delete Cascade: |
Update Cascade: |
Description: | Region foreignkey

Relationship Columns

Column | Reference Column | Type
RegionID | RegionID | int
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Territories] ADD CONSTRAINT [FK_Territories_Region] FOREIGN KEY
(
[RegionID]
) REFERENCES [Region] (
[RegionID]
)
GO

See Also

Table [Region]
Table [Territories]


List of views

Views

Name | Owner | Schema bound | Encrypted | Creation Date | Description
Alphabetical list of products | dbo |  |  | 08.06.2000 | Alphabetical list of products view
Category Sales for 1997 | dbo |  |  | 08.06.2000 |
Current Product List | dbo |  |  | 08.06.2000 |
Customer and Suppliers by City | dbo |  |  | 08.06.2000 |
Invoices | dbo |  |  | 08.06.2000 |
Order Details Extended | dbo |  |  | 08.06.2000 |
Order Subtotals | dbo |  |  | 08.06.2000 |
Orders Qry | dbo |  |  | 08.06.2000 | Orders Qry
Product Sales for 1997 | dbo |  |  | 08.06.2000 |
Products Above Average Price | dbo |  |  | 08.06.2000 |
Products by Category | dbo |  |  | 08.06.2000 |
Quarterly Orders | dbo |  |  | 08.06.2000 |
Sales by Category | dbo |  |  | 08.06.2000 |
Sales Totals by Amount | dbo |  |  | 08.06.2000 |
Summary of Sales by Quarter | dbo |  |  | 08.06.2000 |
Summary of Sales by Year | dbo |  |  | 08.06.2000 |
Total: 16 view(s)

See Also

Database overview


View: Alphabetical list of products

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: | Alphabetical list of products view

Columns

Name | Data Type | Length | NULL | IsGUID | Description
ProductID | int | 4 |  |  | [ProductId] column in [Alphabetical list of products] view
ProductName | nvarchar | 80 |  |  | [ProductName] column in [Alphabetical list of products] view
SupplierID | int | 4 |  |  | SupplierID column
CategoryID | int | 4 |  |  |
QuantityPerUnit | nvarchar | 40 |  |  |
UnitPrice | money | 8 |  |  |
UnitsInStock | smallint | 2 |  |  |
UnitsOnOrder | smallint | 2 |  |  |
ReorderLevel | smallint | 2 |  |  |
Discontinued | bit | 1 |  |  |
CategoryName | nvarchar | 30 |  |  |
Total: 11 column(s)

Objects that [dbo].[Alphabetical list of products] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Suppliers | dbo | Table | 1
Products | dbo | Table | 2
Total: 3 objects

SQL

create view "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))

See Also

List of views


View: Category Sales for 1997

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
CategoryName | nvarchar | 30 |  |  |
CategorySales | money | 8 |  |  |
Total: 2 column(s)

Objects that [dbo].[Category Sales for 1997] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Product Sales for 1997 | dbo | View | 4
Total: 9 objects

SQL

create view "Category Sales for 1997" AS
SELECT "Product Sales for 1997".CategoryName, Sum("Product Sales for 1997".ProductSales) AS CategorySales
FROM "Product Sales for 1997"
GROUP BY "Product Sales for 1997".CategoryName

See Also

List of views


View: Current Product List

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
ProductID | int | 4 |  |  |
ProductName | nvarchar | 80 |  |  |
Total: 2 column(s)

Objects that [dbo].[Current Product List] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Suppliers | dbo | Table | 1
Products | dbo | Table | 2
Total: 3 objects

SQL

create view "Current Product List" AS
SELECT Product_List.ProductID, Product_List.ProductName
FROM Products AS Product_List
WHERE (((Product_List.Discontinued)=0))
--ORDER BY Product_List.ProductName

See Also

List of views


View: Customer and Suppliers by City

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
City | nvarchar | 30 |  |  |
CompanyName | nvarchar | 80 |  |  |
ContactName | nvarchar | 60 |  |  |
Relationship | varchar | 9 |  |  |
Total: 4 column(s)

Objects that [dbo].[Customer and Suppliers by City] depends on

Object Name | Owner | Object Type | Dep Level
Customers | dbo | Table | 1
Suppliers | dbo | Table | 1
Total: 2 objects

SQL

create view "Customer and Suppliers by City" AS
SELECT City, CompanyName, ContactName, 'Customers' AS Relationship
FROM Customers
UNION SELECT City, CompanyName, ContactName, 'Suppliers'
FROM Suppliers
--ORDER BY City, CompanyName

See Also

List of views


View: Invoices

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
ShipName | nvarchar | 80 |  |  |
ShipAddress | nvarchar | 120 |  |  |
ShipCity | nvarchar | 30 |  |  |
ShipRegion | nvarchar | 30 |  |  |
ShipPostalCode | nvarchar | 20 |  |  |
ShipCountry | nvarchar | 30 |  |  |
CustomerID | nchar | 10 |  |  |
CustomerName | nvarchar | 80 |  |  |
Address | nvarchar | 120 |  |  |
City | nvarchar | 30 |  |  |
Region | nvarchar | 30 |  |  |
PostalCode | nvarchar | 20 |  |  |
Country | nvarchar | 30 |  |  |
Salesperson | nvarchar | 62 |  |  |
OrderID | int | 4 |  |  |
OrderDate | datetime | 8 |  |  |
RequiredDate | datetime | 8 |  |  |
ShippedDate | datetime | 8 |  |  |
ShipperName | nvarchar | 80 |  |  |
ProductID | int | 4 |  |  |
ProductName | nvarchar | 80 |  |  |
UnitPrice | money | 8 |  |  |
Quantity | smallint | 2 |  |  |
Discount | real | 4 |  |  |
ExtendedPrice | money | 8 |  |  |
Freight | money | 8 |  |  |
Total: 26 column(s)

Objects that [dbo].[Invoices] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Total: 8 objects

SQL

create view Invoices AS
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City,
Customers.Region, Customers.PostalCode, Customers.Country,
(FirstName + ' ' + LastName) AS Salesperson,
Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName,
"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,
"Order Details".Discount,
(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM Shippers INNER JOIN
(Products INNER JOIN
(
(Employees INNER JOIN
(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
ON Employees.EmployeeID = Orders.EmployeeID)
INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
ON Products.ProductID = "Order Details".ProductID)
ON Shippers.ShipperID = Orders.ShipVia

See Also

List of views


View: Order Details Extended

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
OrderID | int | 4 |  |  |
ProductID | int | 4 |  |  |
ProductName | nvarchar | 80 |  |  |
UnitPrice | money | 8 |  |  |
Quantity | smallint | 2 |  |  |
Discount | real | 4 |  |  |
ExtendedPrice | money | 8 |  |  |
Total: 7 column(s)

Objects that [dbo].[Order Details Extended] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Total: 8 objects

Objects that depend on [dbo].[Order Details Extended]

Object Name | Owner | Object Type | Dep Level
Sales by Category | dbo | View | 1
Total: 1 objects

SQL

create view "Order Details Extended" AS
SELECT "Order Details".OrderID, "Order Details".ProductID, Products.ProductName,
"Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount,
(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice
FROM Products INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID
--ORDER BY "Order Details".OrderID

See Also

List of views


View: Order Subtotals

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
OrderID | int | 4 |  |  |
Subtotal | money | 8 |  |  |
Total: 2 column(s)

Objects that [dbo].[Order Subtotals] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Total: 8 objects

Objects that depend on [dbo].[Order Subtotals]

Object Name | Owner | Object Type | Dep Level
Sales Totals by Amount | dbo | View | 1
Summary of Sales by Quarter | dbo | View | 1
Summary of Sales by Year | dbo | View | 1
Employee Sales by Country | dbo | Procedure | 1
Sales by Year | dbo | Procedure | 1
Total: 5 objects

SQL

create view "Order Subtotals" AS
SELECT "Order Details".OrderID, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal
FROM "Order Details"
GROUP BY "Order Details".OrderID

See Also

List of views


View: Orders Qry

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: | Orders Qry

Columns

Name | Data Type | Length | NULL | IsGUID | Description
OrderID | int | 4 |  |  |
CustomerID | nchar | 10 |  |  |
EmployeeID | int | 4 |  |  |
OrderDate | datetime | 8 |  |  |
RequiredDate | datetime | 8 |  |  |
ShippedDate | datetime | 8 |  |  |
ShipVia | int | 4 |  |  |
Freight | money | 8 |  |  |
ShipName | nvarchar | 80 |  |  |
ShipAddress | nvarchar | 120 |  |  |
ShipCity | nvarchar | 30 |  |  |
ShipRegion | nvarchar | 30 |  |  |
ShipPostalCode | nvarchar | 20 |  |  |
ShipCountry | nvarchar | 30 |  |  |
CompanyName | nvarchar | 80 |  |  |
Address | nvarchar | 120 |  |  |
City | nvarchar | 30 |  |  |
Region | nvarchar | 30 |  |  |
PostalCode | nvarchar | 20 |  |  |
Country | nvarchar | 30 |  |  |
Total: 20 column(s)

Objects that [dbo].[Orders Qry] depends on

Object Name | Owner | Object Type | Dep Level
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Orders | dbo | Table | 2
Total: 4 objects

SQL

create view "Orders Qry" AS
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate,
Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry,
Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

See Also

List of views


View: Product Sales for 1997

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
CategoryName | nvarchar | 30 |  |  |
ProductName | nvarchar | 80 |  |  |
ProductSales | money | 8 |  |  |
Total: 3 column(s)

Objects that [dbo].[Product Sales for 1997] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Total: 8 objects

Objects that depend on [dbo].[Product Sales for 1997]

Object Name | Owner | Object Type | Dep Level
Category Sales for 1997 | dbo | View | 1
Total: 1 objects

SQL

create view "Product Sales for 1997" AS
SELECT Categories.CategoryName, Products.ProductName,
Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSales
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)
INNER JOIN (Orders
INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
ON Products.ProductID = "Order Details".ProductID
WHERE (((Orders.ShippedDate) Between '19970101' And '19971231'))
GROUP BY Categories.CategoryName, Products.ProductName

See Also

List of views


View: Products Above Average Price

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
ProductName | nvarchar | 80 |  |  |
UnitPrice | money | 8 |  |  |
Total: 2 column(s)

Objects that [dbo].[Products Above Average Price] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Suppliers | dbo | Table | 1
Products | dbo | Table | 2
Total: 3 objects

SQL

create view "Products Above Average Price" AS
SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE Products.UnitPrice>(SELECT AVG(UnitPrice) From Products)
--ORDER BY Products.UnitPrice DESC

See Also

List of views


View: Products by Category

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
CategoryName | nvarchar | 30 |  |  |
ProductName | nvarchar | 80 |  |  |
QuantityPerUnit | nvarchar | 40 |  |  |
UnitsInStock | smallint | 2 |  |  |
Discontinued | bit | 1 |  |  |
Total: 5 column(s)

Objects that [dbo].[Products by Category] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Suppliers | dbo | Table | 1
Products | dbo | Table | 2
Total: 3 objects

SQL

create view "Products by Category" AS
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued <> 1
--ORDER BY Categories.CategoryName, Products.ProductName

See Also

List of views


View: Quarterly Orders

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
CustomerID | nchar | 10 |  |  |
CompanyName | nvarchar | 80 |  |  |
City | nvarchar | 30 |  |  |
Country | nvarchar | 30 |  |  |
Total: 4 column(s)

Objects that [dbo].[Quarterly Orders] depends on

Object Name | Owner | Object Type | Dep Level
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Orders | dbo | Table | 2
Total: 4 objects

SQL

create view "Quarterly Orders" AS
SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'

See Also

List of views


View: Sales by Category

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
CategoryID | int | 4 |  |  |
CategoryName | nvarchar | 30 |  |  |
ProductName | nvarchar | 80 |  |  |
ProductSales | money | 8 |  |  |
Total: 4 column(s)

Objects that [dbo].[Sales by Category] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Order Details Extended | dbo | View | 4
Total: 9 objects

SQL

create view "Sales by Category" AS
SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName,
Sum("Order Details Extended".ExtendedPrice) AS ProductSales
FROM Categories INNER JOIN
(Products INNER JOIN
(Orders INNER JOIN "Order Details Extended" ON Orders.OrderID = "Order Details Extended".OrderID)
ON Products.ProductID = "Order Details Extended".ProductID)
ON Categories.CategoryID = Products.CategoryID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName
--ORDER BY Products.ProductName

See Also

List of views


View: Sales Totals by Amount

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
SaleAmount | money | 8 |  |  |
OrderID | int | 4 |  |  |
CompanyName | nvarchar | 80 |  |  |
ShippedDate | datetime | 8 |  |  |
Total: 4 column(s)

Objects that [dbo].[Sales Totals by Amount] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Order Subtotals | dbo | View | 4
Total: 9 objects

SQL

create view "Sales Totals by Amount" AS
SELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate
FROM Customers INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Customers.CustomerID = Orders.CustomerID
WHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231')

See Also

List of views


View: Summary of Sales by Quarter

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
ShippedDate | datetime | 8 |  |  |
OrderID | int | 4 |  |  |
Subtotal | money | 8 |  |  |
Total: 3 column(s)

Objects that [dbo].[Summary of Sales by Quarter] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Order Subtotals | dbo | View | 4
Total: 9 objects

SQL

create view "Summary of Sales by Quarter" AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate

See Also

List of views


View: Summary of Sales by Year

Properties

Owner: | dbo
Schema bound: |
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Columns

Name | Data Type | Length | NULL | IsGUID | Description
ShippedDate | datetime | 8 |  |  |
OrderID | int | 4 |  |  |
Subtotal | money | 8 |  |  |
Total: 3 column(s)

Objects that [dbo].[Summary of Sales by Year] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Order Subtotals | dbo | View | 4
Total: 9 objects

SQL

create view "Summary of Sales by Year" AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate

See Also

List of views


List of stored procedures

Stored procedures

Name | Owner | Description
CustOrderHist | dbo | Alphabetical list of products
CustOrdersDetail | dbo | CustOrdersDetail
CustOrdersOrders | dbo | CustOrdersOrders
Employee Sales by Country | dbo | employee
Sales by Year | dbo |
SalesByCategory | dbo |
Ten Most Expensive Products | dbo |
Total: 7 stored procedure(s)

See Also

Database overview


Stored Procedure: CustOrderHist

Properties

Owner: | dbo
Encrypted: |
Creation Date: | 08.06.2000
Description: | Alphabetical list of products

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

Name | Direction | DataType | Length
@CustomerID | INPUT | nchar | 10
Total: 1 parameter(s)

Objects that [dbo].[CustOrderHist] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Total: 8 object(s)

Permissions

User/Role | Select | Insert | Delete | Update | Execute | DRI
public |  |  |  |  | GRANT |
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: CustOrdersDetail

Properties

Owner: | dbo
Encrypted: |
Creation Date: | 08.06.2000
Description: | CustOrdersDetail

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

Name | Direction | DataType | Length
@OrderID | INPUT | int | 4
Total: 1 parameter(s)

Objects that [dbo].[CustOrdersDetail] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Total: 8 object(s)

Permissions

User/Role | Select | Insert | Delete | Update | Execute | DRI
public |  |  |  |  | GRANT |
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE CustOrdersDetail @OrderID int
AS
SELECT ProductName,
UnitPrice=ROUND(Od.UnitPrice, 2),
Quantity,
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: CustOrdersOrders

Properties

Owner: | dbo
Encrypted: |
Creation Date: | 08.06.2000
Description: | CustOrdersOrders

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

Name | Direction | DataType | Length
@CustomerID | INPUT | nchar | 10
Total: 1 parameter(s)

Objects that [dbo].[CustOrdersOrders] depends on

Object Name | Owner | Object Type | Dep Level
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Orders | dbo | Table | 2
Total: 4 object(s)

Permissions

User/Role | Select | Insert | Delete | Update | Execute | DRI
public |  |  |  |  | GRANT |
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID,
OrderDate,
RequiredDate,
ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: Employee Sales by Country

Properties

Owner: | dbo
Encrypted: |
Creation Date: | 08.06.2000
Description: | employee

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

Name | Direction | DataType | Length
@Beginning_Date | INPUT | datetime | 8
@Ending_Date | INPUT | datetime | 8
Total: 2 parameter(s)

Objects that [dbo].[Employee Sales by Country] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Order Subtotals | dbo | View | 4
Total: 9 object(s)

Permissions

User/Role | Select | Insert | Delete | Update | Execute | DRI
public |  |  |  |  | GRANT |
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure "Employee Sales by Country"
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: Sales by Year

Properties

Owner: | dbo
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

Name | Direction | DataType | Length
@Beginning_Date | INPUT | datetime | 8
@Ending_Date | INPUT | datetime | 8
Total: 2 parameter(s)

Objects that [dbo].[Sales by Year] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Order Subtotals | dbo | View | 4
Total: 9 object(s)

Permissions

User/Role | Select | Insert | Delete | Update | Execute | DRI
public |  |  |  |  | GRANT |
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure "Sales by Year"
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: SalesByCategory

Properties

Owner: | dbo
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

Name | Direction | DataType | Length
@CategoryName | INPUT | nvarchar | 30
@OrdYear | INPUT | nvarchar | 8
Total: 2 parameter(s)

Objects that [dbo].[SalesByCategory] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Customers | dbo | Table | 1
Employees | dbo | Table | 1
Shippers | dbo | Table | 1
Suppliers | dbo | Table | 1
Orders | dbo | Table | 2
Products | dbo | Table | 2
Order Details | dbo | Table | 3
Total: 8 object(s)

Permissions

User/Role | Select | Insert | Delete | Update | Execute | DRI
public |  |  |  |  | GRANT |
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE SalesByCategory
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END

SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: Ten Most Expensive Products

Properties

Owner: | dbo
Encrypted: |
Creation Date: | 08.06.2000
Description: |

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Objects that [dbo].[Ten Most Expensive Products] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Suppliers | dbo | Table | 1
Products | dbo | Table | 2
Total: 3 object(s)

Permissions

User/Role | Select | Insert | Delete | Update | Execute | DRI
public |  |  |  |  | GRANT |
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure "Ten Most Expensive Products" AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


List of functions

Functions

Name | Owner | Type | Description
xxxx | dbo | SQL inlined table-valued function | hello
Total: 1 function(s)

See Also

Database overview


Function: xxxx

Properties

Owner: | dbo
Type: | SQL inlined table-valued function
Encrypted: |
Creation Date: | 09.04.2006
Description: | hello

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

Name | Direction | DataType | Length | Description
@mname | INPUT | int | 4 | @mname parameter
Total: 1 parameter(s)

Columns

Name | Data Type | Length | NULL | IsGUID | Description
name | int | 4 |  |  | name is column
Total: 1 column(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION xxxx
(@mname as int)
RETURNS TABLE
AS
RETURN SELECT 1 as name

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of functions


List of table/view triggers

Table/view triggers

Name | Owner | Disabled | Instead Of | Table/View | Description
TR_Categories | dbo |  |  | Categories | TR Categories trigger
Total: 1 trigger(s)

See Also

Database overview


Trigger: TR_Categories on table Northwind

Properties

Owner: | dbo
Creation Date: | 09.04.2006
Encrypted: |
Description: | TR Categories trigger

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Type

Instead of:
Insert:
Update:
Delete:

Objects that [dbo].[TR_Categories] depends on

Object Name | Owner | Object Type | Dep Level
Categories | dbo | Table | 1
Total: 1 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER TR_Categories
ON Categories
FOR INSERT, UPDATE
AS
BEGIN
RAISERROR ('These columns should never be updated', 16, 1)
ROLLBACK TRANSACTION
END

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of table/view triggers
Table [Categories]


List of defaults

Default

Name | Owner | Creation Date | Description
phonedflt | dbo | 09.04.2006 | example default
Total: 1 default(s)

See Also

Database overview


Default: phonedflt

Properties

Name: | phonedflt
Owner: | dbo
Create Date: | 09.04.2006
Description: | example default

Bound datatypes

Object Name | Owner
DT_USERDATATYPE | dbo
Total: 1 bound datatype(s)

SQL

CREATE DEFAULT phonedflt AS 'unknown'

See Also

List of defaults


List of rules

rule

Name | Owner | Creation Date | Description
pattern_rule | dbo | 09.04.2006 | pattern rule
RL_RULE | dbo | 09.04.2006 | example rule
Total: 2 rule(s)

See Also

Database overview


Rule: pattern_rule

Properties

Owner: | dbo
Deleted: |
Description: | pattern rule

SQL

CREATE RULE pattern_rule
AS
@value LIKE '_ _-%[0-9]'

See Also

List of rules


Rule: RL_RULE

Properties

Owner: | dbo
Deleted: |
Description: | example rule

Bound objects

Object Name | Owner
DT_USERDATATYPE | dbo
Total: 1 bound datatype(s)

SQL

create rule [RL_RULE] as @list IN ('1389', '0736', '0877')

See Also

List of rules


List of datatypes

datatype

Name | Owner
DT_USERDATATYPE | dbo
Total: 1 datatype(s)

See Also

Database overview


Datatype: DT_USERDATATYPE

Properties

Owner: | dbo
Base Type: | bigint
Dafault Name: | phonedflt
Default Owner: | dbo
Nullable: |
Precision: |
Scale: |
Flags: | 0
Allow Identity: |
Length: | 0
Variable Length: |
Max Length: | 8
Rule: | RL_RULE
Rule Owner: | dbo
Collation: |
Description: |

SQL

EXEC sp_addtype N'DT_USERDATATYPE', N'bigint', N'not null'
GO

See Also

List of datatypes


List of users

Users

Name
dbo
guest
Total: 2 user(s)

See Also

Database overview


User: dbo

User Properties

Name: dbo

SQL

if not exists (select * from dbo.sysusers where name = N'dbo' and uid < 16382)
EXEC sp_grantdbaccess N'sa', N'dbo'
GO

See Also

List of users


User: guest

User Properties

Name: guest

SQL

if not exists (select * from dbo.sysusers where name = N'guest' and uid < 16382 and hasdbaccess = 1)
EXEC sp_grantdbaccess N'guest'
GO

See Also

List of users


List of roles

Roles

Name
public
db_owner
Total: 2 role(s)

See Also

Database overview


Role: public

Properties

Fixed:

SQL

if not exists (select * from dbo.sysusers where name = N'public' and uid > 16399)
EXEC sp_addrole N'public'
GO

See Also

List of roles


Role: db_owner

Properties

Fixed:

Users

Name
dbo
Total: 1 user(s)

SQL

See Also

List of roles


Server Info

Properties

Network Name: | TEZORRO
Language: | SQL_Latin1_General_CP1_CI_AS

Version

Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Databases

Database | Owner | System
Northwind | sa |
Total: 1 database(s)

Server Configuration

Name | Value
Maximum recovery interval in minutes | 0
Allow updates to system tables | 0
Number of user connections allowed | 0
Number of locks for all users | 0
Number of open database objects | 0
Default fill factor percentage | 0
Allow triggers to be invoked within triggers | 1
Allow remote access | 1
default language | 0
Maximum worker threads | 255
Network packet size | 4096
show advanced options | 0
Create DTC transaction for remote procedures | 0
c2 audit mode | 0
default full-text language | 1033
two digit year cutoff | 2049
Memory for index create sorts (kBytes) | 0
Priority boost | 0
remote login timeout | 20
remote query timeout | 600
cursor threshold | -1
set working set size | 0
user options | 0
affinity mask | 0
Maximum size of a text field in replication. | 65536
Tape retention period in days | 0
cost threshold for parallelism | 5
maximum degree of parallelism | 0
minimum memory per query (kBytes) | 1024
maximum time to wait for query memory (s) | -1
Minimum size of server memory (MB) | 0
Maximum size of server memory (MB) | 2147483647
Maximum estimated cost allowed by query governor | 0
User mode scheduler uses lightweight pooling | 0
scan for startup stored procedures | 0
AWE enabled in the server | 0
affinity64 mask | 0


List of logins

Logins

Name | Login Type | Default Database
sa | SQL Server standard | master
BUILTIN\Administrators | Windows security group | master
Total: 2 login(s)

See Also

Server overview


Login: sa

Properties

Name: | sa
Friendly Name: | English
System: |
Default Database: | master
Deny Windows Security Authenticated Connections: |
Type: | SQL Server standard
Permissions to Access the Server: |

Users with this login

Database | User
master | dbo
model | dbo
msdb | dbo
Northwind | dbo
pubs | dbo
tempdb | dbo

Roles with this Login

Role
sysadmin

SQL

if not exists (select * from master.dbo.syslogins where loginname = N'sa')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'sa', null, @logindb, @loginlang
END
GO

See Also

Server overview
Logins list


Login: BUILTIN\Administrators

Properties

Name: | BUILTIN\Administrators
Friendly Name: | English
System: |
Default Database: | master
Deny Windows Security Authenticated Connections: |
Type: | Windows security group
Permissions to Access the Server: |

Roles with this Login

Role
sysadmin

SQL

if not exists (select * from master.dbo.syslogins where loginname = N'BUILTIN\Administrators')
exec sp_grantlogin N'BUILTIN\Administrators'
exec sp_defaultdb N'BUILTIN\Administrators', N'master'
exec sp_defaultlanguage N'BUILTIN\Administrators', N'us_english'
GO

See Also

Server overview
Logins list


About

Documentation details

Customer: |
Project: |
Created: | 10.11.2006
Generator: | DBScribe 1.2 for SQL Server