Home » SQL Server » SQL Server 2000 system tables and their equivalent DMV in SQL Server 2005

SQL Server 2000 system tables and their equivalent DMV in SQL Server 2005

Those who have been working with SQL Server administration for a while now undoubtedly have at times referred 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.

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

1 Comment

  1. I’ll right away grab your rss feed as I can not in finding your e-mail subscription hyperlink or newsletter service. Do you have any? Kindly allow me know in order that I could subscribe. Thanks.

Leave a Reply

%d bloggers like this: