Database reference - AdventureWorks

Database overview

Documentation

AdventureWorks Sample OLTP Database 

Documentation generated on: Sep 15 2011 7:41PM

Database objects

typecount
tables70
views17
indexes169
procedures9
functions11
all columns725
all parameters47
types6
triggers12
defaults147
schemas18
assemblies1

Database users

namerole membership
dbodb_owner
guestpublic
INFORMATION_SCHEMApublic
syspublic

Roles

namerole idis application role
public00
db_owner163840
db_accessadmin163850
db_securityadmin163860
db_ddladmin163870
db_backupoperator163890
db_datareader163900
db_datawriter163910
db_denydatareader163920
db_denydatawriter163930

Database files

filefile groupsizemax sizegrowthusage
C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Data.mdf  PRIMARY  174080 KB  Unlimited  16384 KB  data only 
C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Log.ldf    2048 KB  2147483648 KB  16384 KB  log only 

Database properties

namevalue
nameAdventureWorks
serverSPRING\KATMAI
size172.00 MB 
createdMar 19 2009 9:08PM
statusONLINE
ownerNT AUTHORITY\SYSTEM
versionMicrosoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86) Sep 16 2010 20:09:22 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
unallocated0.66 MB 
reserved173408 KB 
data size88752 KB 
index size78568 KB 
unused6088 KB 
compatibility level100
updateabilityREAD_WRITE
user accessMULTI_USER
recoverySIMPLE
collationSQL_Latin1_General_CP1_CI_AS
SQL sort order52
auto closeno
auto shrinkno
in standbyno
torn page detectionno
ansi null defaultno
ansi nullsyes
ansi paddingyes
ansi warningsyes
arithmetic abortyes
auto create statisticsyes
auto update statisticsyes
close cursors on commitno
full textyes
local cursors defaultno
null concatyes
numeric round abortno
quoted identifiersyes
recursive triggersno
publishedno
subscribedno
sync with backupno

Code (database)

CREATE DATABASE [AdventureWorks] ON  PRIMARY 
( NAME = N'AdventureWorks_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Data.mdf' , SIZE = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
 LOG ON 
( NAME = N'AdventureWorks_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
 COLLATE SQL_Latin1_General_CP1_CI_AS
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 100
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AdventureWorks].[dbo].[sp_fulltext_database] @action = 'enable'
end
ALTER DATABASE [AdventureWorks] SET ANSI_NULL_DEFAULT OFF 
ALTER DATABASE [AdventureWorks] SET ANSI_NULLS ON 
ALTER DATABASE [AdventureWorks] SET ANSI_PADDING ON 
ALTER DATABASE [AdventureWorks] SET ANSI_WARNINGS ON 
ALTER DATABASE [AdventureWorks] SET ARITHABORT ON 
ALTER DATABASE [AdventureWorks] SET AUTO_CLOSE OFF 
ALTER DATABASE [AdventureWorks] SET AUTO_CREATE_STATISTICS ON 
ALTER DATABASE [AdventureWorks] SET AUTO_SHRINK OFF 
ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE [AdventureWorks] SET CURSOR_CLOSE_ON_COMMIT OFF 
ALTER DATABASE [AdventureWorks] SET CURSOR_DEFAULT  GLOBAL 
ALTER DATABASE [AdventureWorks] SET CONCAT_NULL_YIELDS_NULL ON 
ALTER DATABASE [AdventureWorks] SET NUMERIC_ROUNDABORT OFF 
ALTER DATABASE [AdventureWorks] SET QUOTED_IDENTIFIER ON 
ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS OFF 
ALTER DATABASE [AdventureWorks] SET  ENABLE_BROKER 
ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
ALTER DATABASE [AdventureWorks] SET DATE_CORRELATION_OPTIMIZATION OFF 
ALTER DATABASE [AdventureWorks] SET TRUSTWORTHY OFF 
ALTER DATABASE [AdventureWorks] SET ALLOW_SNAPSHOT_ISOLATION OFF 
ALTER DATABASE [AdventureWorks] SET PARAMETERIZATION SIMPLE 
ALTER DATABASE [AdventureWorks] SET READ_COMMITTED_SNAPSHOT OFF 
ALTER DATABASE [AdventureWorks] SET HONOR_BROKER_PRIORITY OFF 
ALTER DATABASE [AdventureWorks] SET  READ_WRITE 
ALTER DATABASE [AdventureWorks] SET RECOVERY SIMPLE 
ALTER DATABASE [AdventureWorks] SET  MULTI_USER 
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM  
ALTER DATABASE [AdventureWorks] SET DB_CHAINING OFF 

Code (users)

CREATE USER [dbo] FOR LOGIN [NT AUTHORITY\SYSTEM] WITH DEFAULT_SCHEMA=[dbo]

CREATE USER [guest] WITH DEFAULT_SCHEMA=[guest]

CREATE USER [INFORMATION_SCHEMA]

CREATE USER [sys]

Code (roles)

CREATE ROLE [db_owner] AUTHORIZATION [dbo]

CREATE ROLE [db_accessadmin] AUTHORIZATION [dbo]

CREATE ROLE [db_securityadmin] AUTHORIZATION [dbo]

CREATE ROLE [db_ddladmin] AUTHORIZATION [dbo]

CREATE ROLE [db_backupoperator] AUTHORIZATION [dbo]

CREATE ROLE [db_datareader] AUTHORIZATION [dbo]

CREATE ROLE [db_datawriter] AUTHORIZATION [dbo]

CREATE ROLE [db_denydatareader] AUTHORIZATION [dbo]

CREATE ROLE [db_denydatawriter] AUTHORIZATION [dbo]


Powered by Yoyodyne © 1971