Duration (Pacific Standard Time):
To (Pacific Standard Time):
Impact:
  • None
User Action:
  • None

System Base Tables

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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.

System_CAPS_ICON_important.jpg Important


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

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.

System_CAPS_ICON_important.jpg Important


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

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

Base tableDescription
sys.sysschobjsExists in every database. Each row represents an object in the database.
sys.sysbinobjsExists 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.sysclsobjsExists 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.sysnsobjsExists in every database. Contains a row for each namespace-scoped entity. This table is used for storing XML collection entities.
sys.syscolparsExists 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.systypedsubobjsExists in every database. Contains a row for each typed subentity. Only parameters for partition function fall into this category.
sys.sysidxstatsExists 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.sysiscolsExists in every database. Contains a row for each persisted index and statistics column.
sys.sysscalartypesExists in every database. Contains a row for each user-defined or system type.
sys.sysdbregExists in the master database only. Contains a row for each registered database.
sys.sysxsrvsExists in the master database only. Contains a row for each local, linked, or remote server.
sys.sysrmtlgnsThis 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.syslnklgnsExists 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.sysxlgnsExists in the master database only. Contains a row for each server principal.
sys.sysdbfilesExists 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.sysusermsgExists in the master database only. Each row represents a user-defined error message.
sys.sysprivsExists in every database. Contains a row for each database- or server-level permission.

Note: Server-level permissions are stored in the master database.
sys.sysownersExists in every database. Each row represents a database principal.
sys.sysobjkeycryptsExists in every database. Contains a row for each symmetric key, encryption, or cryptographic property associated with an object.
sys.syscertsExists in every database. Contains a row for each certificate in a database.
sys.sysasymkeysExists in every database. Each row represents an asymmetric key.
sys.ftindsExists in every database. Contains a row for each full-text index in the database.
sys.sysxpropsExists in every database. Contains a row for each extended property.
sys.sysallocunitsExists in every database. Contains a row for each storage allocation unit.
sys.sysrowsetsExists in every database. Contains a row for each partition rowset for an index or a heap.
sys.sysrowsetrefsExists in every database. Contains a row for each index to rowset reference.
sys.syslogshippersExists in the master database only. Contains a row for each database mirroring witness.
sys.sysremsvcbindsExists in every database. Contains a row for each remote service binding.
sys.sysconvgroupExists in every database. Contains a row for each service instance in Service Broker.
sys.sysxmitqueueExists in every database. Contains a row for each Service Broker transmission queue.
sys.sysdesendExists in every database. Contains a row for each sending endpoint of a Service Broker conversation.
sys.sysdercvExists in every database. Contains a row for each receiving endpoint of a Service Broker conversation.
sys.sysendptsExists in the master database only. Contains a row for each endpoint created in the server.
sys.syswebmethodsExists 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.sysqnamesExists in every database. Contains a row for each namespace or qualified name to a 4-byte ID token.
sys.sysxmlcomponentExists in every database. Each row represents an XML schema component.
sys.sysxmlfacetExists in every database. Contains a row for each XML facet (restriction) of XML type definition.
sys.sysxmlplacementExists in every database. Contains a row for each XML placement for XML components.
sys.syssingleobjrefsExists in every database. Contains a row for each general N-to-1 reference.
sys.sysmultiobjrefsExists in every database. Contains a row for each general N-to-N reference.
sys.sysobjvaluesExists in every database. Contains a row for each general value property of an entity.
sys.sysguidrefsExists in every database. Contains a row for each GUID classified ID reference.