Search notes:

SQL Server: database

A database belongs to an instance.

The data of a database is stored in files (of a file system). These files can be grouped into so called file groups.

The name of the currently connected database can be queried with the db_name() function.

The default schema for a newly created database is dbo.

Schemas

A database has one or more schemas. A schema is a group of objects (such as tables, views, stored procedures etc.) with a specific ownership.

Each database has a sys schema which stores (among others?) system stored procedures (sp_…).

Some special objects such as certificates and asymmetric keys are not found in a schema but in the database itself.

When fully qualifying an object name, the database is the third last, the schema the second last element:

select * from     DB.SCH.OBJ;
select * from SRV.DB.SCH.OBJ;

Templates for newly created databases

Newly created database inherit some charactersistics from the model system database.

Server logins's default database

Each server login (server principal) is assigned a default database. This database can be queried from the column default_database_name in sys.server_principals.

Database owner

A database has an owner:

select
   suser_sname(owner_sid) database_owner,
   name                   database_name, 
   state_desc        , -- Online?
   owner_sid
from
   sys.databases

Within a database, its owner is known as the dbo of the database.

The owner of a database (except for master, model and tempdb) can be changed with sp_changedbowner (which is in so-called maintenance mode) or alter authorization.

Operating system files

Every database is associated with at least one data file and one log file.

Data files physically store data of objects such as tables, indexes, stored procedures and views.

Log files store the necessary information to recover transactions.

Attached and detached database

In order to use the data that is stored in a database data file, the data file (that is: the database) must be attached to an instance.

A database file can be attached to an instance with a variation of the create database statement:

create database tq84_db_copy on (filename = 'd:\ata\base\file.mdf') for attach;
go

An attached database can be detached with

sp_detach_db tq84_db_copy;