SQL System Tables

Mapping System Tables to System Views 

System table	System views or functions
sysaltfiles	sys.master_files
syscacheobjects	sys.dm_exec_cached_plans sys.dm_exec_plan_attributes (Transact-SQL) sys.dm_exec_sql_text sys.dm_exec_cached_plan_dependent_objects
syscharsets	sys.syscharsets
sysconfigures	sys.configurations
syscurconfigs	sys.configurations
sysdatabases	sys.databases
sysdevices	sys.backup_devices
syslanguages	sys.syslanguages
syslockinfo	sys.dm_tran_locks
syslocks	sys.dm_tran_locks
syslogins	sys.server_principals sys.sql_logins
sysmessages	sys.messages
sysoledbusers	sys.linked_logins
sysopentapes	sys.dm_io_backup_tapes
sysperfinfo	sys.dm_os_performance_counters
sysprocesses	sys.dm_exec_connections sys.dm_exec_sessions sys.dm_exec_requests
sysremotelogins	sys.remote_logins
sysservers	sys.servers

The following table maps the system tables or functions that are in every database in SQL Server 2000 (8.x) to their corresponding system views or functions in SQL Server 2017.

System table or function	System view or function
fn_virtualfilestats	sys.dm_io_virtual_file_stats
syscolumns	sys.columns
syscomments	sys.sql_modules
sysconstraints	sys.check_constraints sys.default_constraints sys.key_constraints sys.foreign_keys
sysdepends	sys.sql_expression_dependencies
sysfilegroups	sys.filegroups
sysfiles	sys.database_files
sysforeignkeys	sys.foreign_key_columns
sysindexes	sys.indexes sys.partitions sys.allocation_units sys.dm_db_partition_stats
sysindexkeys	sys.index_columns
sysmembers	sys.database_role_members
sysobjects	sys.objects
syspermissions	sys.database_permissions sys.server_permissions
sysprotects	sys.database_permissions sys.server_permissions
sysreferences	sys.foreign_keys
systypes	sys.types
sysusers	sys.database_principals
sysfulltextcatalogs	sys.fulltext_catalogs

  

System Base Tables

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. 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.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.

 

SOURCE : Microsoft