Roles of default databases and default tables in SQL Server

4 System databases are installed on SQL Server. They are master database, model database, tempdb database, and MSDB database. These databases are the heart and soul of SQL Server.

In addition, two instance databases are installed: northwind and pubs.

Four System databases

Master Database:Master Database Control SQL
Server. This database contains all the configuration information, user logon information, and information about the running process on the server.

ModelDatabase:The model database is the template used to create all user databases. When you create a new database
Server creates a copy of all objects in the model database and moves them to the new database. After the template object is copied to the new user database, all the extra space of the database will be filled with blank pages.

TempdbDatabase:The tempdb database is a very special database for all to access your SQL
Server users. This library is used to save all temporary tables, stored procedures, and other SQL Server-created temporary items. For example, the tempdb database is used for sorting. The data is put into the tempdb database. After sorting, the results are returned to the user. Each SQL statement
Server restarts. It clears the tempdb database and recreates the database. Never create a table to be permanently saved in the tempdb database.

MSDBDatabase:MSDB database is SQL
Server. If you look at the actual definition of this database, you will find that it is actually a user database. The difference is what SQL server uses this database. All task scheduling, alarms, and operators are stored in the MSDB database. Another function of this database is to store all backup history. SQL
Server Agent will use this library.

Two sample Databases

Northwind sample database:

The northwind traders Sample Database contains sales data from a fictitious company called northwind traders, which is engaged in the import and export of specialty foods from all over the world.

IfNorthwindThe database has been changed. You can run
The script in the install directory installed on Microsoft SQL Server reinstalls it:

1. At the command prompt, change to the MSSQL7 \ install directory.
2. UseOsqlRun the instnwnd. SQL script:

<! -- [If! Supportlists] --> 3. <! -- [Endif] --> osql/USA/psapassword/sservername/iinstnwnd. SQL/oinstnwnd. rpt

1. Check for errors reported in instnwnd. Rpt.

The database is created in the data directory of the SQL Server installer.

Pubs example database:

The pubs sample database uses a book publishing company as a model to demonstrate many options available in the Microsoft SQL Server database. This database and its tables are often used in the example described in the document.

If you have changedPubsDatabase, you can use the files in the install directory in SQL Server installation to re-install. The installation process requires two steps:

1. UseOsqlRun the instpubs. SQL script. This operation will remove the existingPubsDatabase, create a newPubsDatabase and define all objects.
2. Run pubimage. bat from the command prompt line. In this tutorialPub_infoInsert the image value in the table.

Roles of default tables in databases created in sqlserver

Name	Address	Description
Sysaltfiles	Primary Database	Save database files
Syscharsets	Primary Database	Character Set and sorting order
Sysconfigures	Primary Database	Configuration Options
Syscurconfigs	Primary Database	Current configuration option
Sysdatabases	Primary Database	Databases in the server
Syslanguages	Primary Database	Language
Syslogins	Primary Database	Login Account Information
Sysoledbusers	Primary Database	Link Server login information
Sysprocesses	Primary Database	Process
Sysremotelogins	Primary Database	Remote Logon account
Syscolumns	Each database	Column
Sysconstrains	Each database	Restrictions
Sysfilegroups	Each database	File Group
Sysfiles	Each database	File
Sysforeignkeys	Each database	External keywords
Sysindexes	Each database	Index
Sysmenbers	Each database	Role Member
Sysobjects	Each database	All database objects
Syspermissions	Each database	Permission
Policypes	Each database	User-Defined Data Type
Sysusers	Each database	User