Search notes:
SQL Server: Schemas
A schema is a named set (a container) of database objects. It allows to group objects into namespaces.
A schema can be assigned security rules which will be inhertied to objects as they are created within the schema.
A schema has an owner which can be any database principal. (The same principal can own multiple schemas).
Default schema name
The default schema name for a newly created
database is
dbo.
Query objects in a schema
The following query finds all objects (tables, views etc.) in a schema:
select
sch.name schema_name,
obj.name object_name,
obj.type_desc object_type
from
sys.schemas sch join
sys.objects obj on sch.schema_id = obj.schema_id
where
sch.name = 'sys'
;
The stored procedure
sp_tables can be used to find
tables and views in a given schema.
Default schemas
SQL Server comes with 10 (?) predefined schemas:
- db_accessadmin
- db_backupoperator
- db_datareader
- db_datawriter
- db_ddladmin
- db_denydatareader
- db_denydatawriter
- db_owner
- dbo
- guest
- INFORMATION_SCHEMA
- sys
All of these schemas except dbo, guest, sys and INFORMATION_SCHEMA can be dropped.
See also
The available schemas can be queried in