Those who have been working with SQL Server administration for a while now undoubtedly have at times refered to the old SQL Server system tables in order to automate some processes, or document their tables by for example combining the sysobjects and syscolumns tables. As per SQL Server 2005 and onwards, Microsoft added a number of Dynamic Management Views (DMV) that take simplify all kinds of management tasks.

This article will give a list of SQL Server 2000 system tables and their 2005 equivalent management views, as well as a brief description what kind of information to find in the views.

Dynamic Management Views existing in the Master database

SQL Server 2000

SQL Server2005

Description

sysaltfiles

sys.master_files

Contains a row per file of a database as stored in the master database.

syscacheobjects

sys.dm_exec_cached_plans

Returns a row for each query plan that is cached by SQL Server for faster query execution.

sys.dm_exec_plan_attributes

Returns one row per plan attribute for the plan specified by the plan handle.

sys.dm_exec_sql_text

Returns the text of the SQL batch that is identified by the specified sql_handle.

sys.dm_exec_cached_plan_dependent_objects

Returns a row for each Transact-SQL execution plan, common language runtime (CLR) execution plan, and cursor associated with a plan.

syscharsets

sys.syscharsets

Contains one row for each character set and sort order defined for use by the SQL Server Database Engine.

sysconfigures

sys.configurations

Contains a row per server-wide configuration option value in the system.

syscurconfigs

sys.configurations

Contains a row per server-wide configuration option value in the system.

sysdatabases

sys.databases

Contains one row per database in the instance of Microsoft SQL Server.

sysdevices

sys.backup_devices

Contains a row for each backup-device registered by using sp_addumpdevice or created in SQL Server Management Studio.

syslanguages

sys.syslanguages

Contains one row for each language present in the instance of SQL Server.

syslockinfo

sys.dm_tran_locks

Returns information about currently active lock manager resources

syslocks

[

sys.dm_tran_locks

Returns information about currently active lock manager resources

syslogins

sys.server_principals

Contains a row for every server-level principal.

sys.sql_logins

Returns one row for every SQL login.

sysmessages

sys.messages

Contains a row for each message_id or language_id of the error messages in the system, for both system-defined and user-defined messages.

sysoledbusers

sys.linked_logins

Returns a row per linked-server-login mapping, for use by RPC and distributed queries from local server to the corresponding linked server.

sysopentapes

sys.dm_io_backup_tapes

Returns the list of tape devices and the status of mount requests for backups.

sysperfinfo

sys.dm_os_performance_counters

Returns a row per performance counter maintained by the server.

sysprocesses

sys.dm_exec_connections

Returns information about the connections established to this instance of SQL Server and the details of each connection.

sys.dm_exec_sessions

Returns one row per authenticated session on SQL Server.

sys.dm_exec_requests

Returns information about each request that is executing within SQL Server.

sysremotelogins

sys.remote_logins

Returns a row per remote-login mapping. This catalog view is used to map incoming local logins that claim to be coming from a corresponding server to an actual local login.

sysservers

sys.servers

Contains a row per linked or remote server registered, and a row for the local server that has server_id = 0.

Dynamic Management Views existing in every database.

SQL Server 2000

SQL Server 2005

Description

fn_virtualfilestats

sys.dm_io_virtual_file_stats

Returns I/O statistics for data and log files.

syscolumns

sys.columns

Returns a row for each column of an object that has columns, such as views or tables.

Contains a row for each object that is a CHECK constraint, with sys.objects.type = 'C'.

sys.default_constraints

Contains a row for each object that is a default definition (created as part of a CREATE TABLE or ALTER TABLE statement instead of a CREATE DEFAULT statement), with sys.objects.type = 'D'.

sys.key_constraints

Contains a row for each object that is a primary key or unique constraint. Includes sys.objects.type 'PK' and 'UQ'.

sys.foreign_keys

Contains a row per object that is a FOREIGN KEY constraint, with sys.object.type = 'F'.

sysdepends

sys.sql_expression_dependencies

Contains one row for each by-name dependency on a user-defined entity in the current database. A dependency between two entities is created when one entity, called the referenced entity, appears by name in a persisted SQL expression of another entity, called the referencing entity.

sysfilegroups

sys.filegroups

Contains a row for each data space that is a filegroup.

sysfiles

sys.database_files

Contains a row per file of a database as stored in the database itself. This is a per-database view.

sysforeignkeys

sys.foreign_key_columns

Contains a row for each column, or set of columns, that comprise a foreign key.

sysindexes

sys.indexes

Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.

sys.partitions

Contains a row for each partition of all the tables and most types of indexes in the database. Special index types like Fulltext, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.

sys.allocation_units

Contains a row for each allocation unit in the database.

sys.dm_db_partition_stats

Returns page and row-count information for every partition in the current database.

sysindexkeys

sys.index_columns

Contains one row per column that is part of a sys.indexes index or unordered table (heap).

sysmembers

sys.database_role_members

Returns one row for each member of each database role.

sysobjects

sys.objects

Contains a row for each user-defined, schema-scoped object that is created within a database.

syspermissions

sys.database_permissions

Returns a row for every permission or column-exception permission in the database. For columns, there is a row for every permission that is different from the corresponding object-level permission. If the column permission is the same as the corresponding object permission, there will be no row for it and the actual permission used will be that of the object.

sys.server_permissions

Returns one row for each server-level permission.

sysprotects

sys.database_permissions

Returns a row for every permission or column-exception permission in the database. For columns, there is a row for every permission that is different from the corresponding object-level permission. If the column permission is the same as the corresponding object permission, there will be no row for it and the actual permission used will be that of the object.

sys.server_permissions

Returns one row for each server-level permission.

sysreferences

sys.foreign_keys

Contains a row per object that is a FOREIGN KEY constraint, with sys.object.type = 'F'.