System Base Tables

System base tables are the underlying tables that actually store the metadata for a specific database. The master database is special in this respect because it contains some additional tables that are not found in any of the other databases. These tables contain persisted metadata that has server-wide scope. The Resource database contains the same base table schema as any other non-master database. However, because the Resource database is not visible, users cannot use or connect to it. For more information about the Resource database, see Resource Database.

Important

The system base tables are used only within the SQL Server 2005 Database Engine and are not for general customer use. They are subject to change and compatibility is not guaranteed.

System Base-Table Metadata

A grantee that has CONTROL, ALTER, or VIEW DEFINITION permission on a database can see system base table metadata in the sys.objects catalog view. The grantee can also resolve the names and object IDs of system base tables by using built-in functions such as OBJECT_NAME and OBJECT_ID.

To bind to a system base table, a user must connect to the instance of SQL Server by using the dedicated administrator connection (DAC). Trying to execute a SELECT query from a system base table without connecting by using DAC raises an error.

Important

Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.

System Base Tables

The following table lists and describes each system base table in SQL Server 2005.

Base table

Description

sys.sysschobjs

Exists in every database. Each row represents an object in the database.

sys.sysbinobjs

Exists in every database. Contains a row for each Service Broker entity in the database. Service Broker entities include the following:

• Message type
• Service contract
• Service

The names and types use binary collation that is fixed.

sys.sysclsobjs

Exists in every database. Contains a row for each classified entity that shares the same common properties that include the following:

• Assembly
• Backup device
• Full-text catalog
• Partition function
• Partition scheme
• File group
• Obfuscation key

sys.sysnsobjs

Exists in every database. Contains a row for each namespace-scoped entity. This table is used for storing XML collection entities.

sys.syscolpars

Exists in every database. Contains a row for every column in a table, view, or table-valued function.

• It also contains rows for every parameter of a procedure or function.

sys.systypedsubobjs

Exists in every database. Contains a row for each typed subentity. Currently, only parameters for partition function fall into this category.

sys.sysidxstats

Exists in every database. Contains a row for each index or statistics for tables and indexed views

Note:

Every index (except heap) is associated with a statistic that has the same name as the index.

sys.sysiscols

Exists in every database. Contains a row for each persisted index and statistics column.

sys.sysscalartypes

Exists in every database. Contains a row for each user-defined or system type.

sys.sysdbreg

Exists in the master database only. Contains a row for each registered database.

sys.sysxsrvs

Exists in the master database only. Contains a row for each local, linked, or remote server.

sys.sysrmtlgns

This system base table exists in the master database only. Contains a row for each remote login mapping. This is used to map incoming logins that claim to be coming from a corresponding server to an actual local login.

sys.syslnklgns

Exists in the master database only. Contains a row for each linked login mapping. Linked login mappings are used by remote procedure calls and distributed queries that emanate from a local server out to a corresponding linked server.

sys.sysxlgns

Exists in the master database only. Contains a row for each server principal.

sys.sysdbfiles

Exists in every database. If the column dbid is zero, the row represents a file that belongs to this database. In the master database, the column dbid can be nonzero. When this is the case, the row represents a master file.

sys.sysusermsg

Exists in the master database only. Each row represents a user-defined error message.

sys.sysprivs

Exists in every database. Contains a row for each database- or server-level permission.

Note:

Server-level permissions are stored in the master database.

sys.sysowners

Exists in every database. Each row represents a database principal.

sys.sysobjkeycrypts

Exists in every database. Contains a row for each symmetric key, encryption, or cryptographic property associated with an object.

sys.syscerts

Exists in every database. Contains a row for each certificate in a database.

sys.sysasymkeys

Exists in every database. Each row represents an asymmetric key.

sys.ftinds

Exists in every database. Contains a row for each full-text index in the database.

sys.sysxprops

Exists in every database. Contains a row for each extended property.

sys.sysallocunits

Exists in every database. Contains a row for each storage allocation unit.

sys.sysrowsets

Exists in every database. Contains a row for each partition rowset for an index or a heap.

sys.sysrowsetcolumns

Exists in every database. Contains a row for each column, of partition rowset.

sys.syshobts

Exists in every database. Contains a row for each heap or B-tree (HOBT) that can host multiple rowsets

sys.syshobtcolumns

Exists in every database. Contains a row for each column of a HOBT.

sys.sysserefs

Exists in every database. Contains a row for each general storage engine metadata reference.

sys.sysrowsetrefs

Exists in every database. Contains a row for each index to rowset reference.

sys.syslogshippers

Exists in the master database only. Contains a row for each database mirroring witness.

sys.sysremsvcbinds

Exists in every database. Contains a row for each remote service binding.

sys.sysconvgroup

Exists in every database. Contains a row for each service instance in Service Broker.

sys.sysxmitqueue

Exists in every database. Contains a row for each Service Broker transmission queue.

sys.sysdesend

Exists in every database. Contains a row for each sending endpoint of a Service Broker conversation.

sys.sysdercv

Exists in every database. Contains a row for each receiving endpoint of a Service Broker conversation.

sys.sysendpts

Exists in the master database only. Contains a row for each endpoint created in the server.

sys.syswebmethods

Exists in the master database only. Contains a row for each SOAP-method defined on a SOAP-enabled HTTP endpoint that is created in the server.

sys.sysqnames

Exists in every database. Contains a row for each namespace or qualified name to a 4-byte ID token.

sys.sysxmlcomponent

Exists in every database. Each row represents an XML schema component.

sys.sysxmlfacet

Exists in every database. Contains a row for each XML facet (restriction) of XML type definition.

sys.sysxmlplacement

Exists in every database. Contains a row for each XML placement for XML components.

sys.syssingleobjrefs

Exists in every database. Contains a row for each general N-to-1 reference.

sys.sysmultiobjrefs

Exists in every database. Contains a row for each general N-to-N reference.

sys.sysobjvalues

Exists in every database. Contains a row for each general value property of an entity.

sys.sysguidrefs

Exists in every database. Contains a row for each GUID classified ID reference.

See Also

Concepts

Querying the SQL Server System Catalog
Internal System Views

Help and Information

Getting SQL Server 2005 Assistance