SQL Server 2000 system tables and their 2005 Dynamic Management View (DMV) equivalents

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.

Sponsored link:

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.
syscomments	sys.sql_modules	Returnsa row for each object that is an SQL language-defined module. Objectsof type 'P', 'RF', 'V', 'TR', 'FN', 'IF', 'TF', and 'R' have an associated SQL module.
sysconstraints	sys.check_constraints	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'.
systypes	sys.types	Contains a row for each system and user-defined type.
sysusers	sys.database_principals	Returns a row for each principal in a database.
sysfulltextcatalogs	sys.fulltext_catalogs	Contains a row for each full-text catalog.