Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

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

Discussion in 'SQL Server 2008 General Discussion' started by Peter Schmitz, Oct 1, 2012.

  1. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    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 2000SQL Server2005Description
    sysaltfilessys.master_filesContains a row per file of a database as stored in the master database.
    syscacheobjectssys.dm_exec_cached_plansReturns a row for each query plan that is cached by SQL Server for faster query execution.
    sys.dm_exec_plan_attributesReturns one row per plan attribute for the plan specified by the plan handle.
    sys.dm_exec_sql_textReturns the text of the SQL batch that is identified by the specified sql_handle.
    sys.dm_exec_cached_plan_dependent_objectsReturns a row for each Transact-SQL execution plan, common language runtime (CLR) execution plan, and cursor associated with a plan.
    syscharsetssys.syscharsetsContains one row for each character set and sort order defined for use by the SQL Server Database Engine.
    sysconfiguressys.configurationsContains a row per server-wide configuration option value in the system.
    syscurconfigssys.configurationsContains a row per server-wide configuration option value in the system.
    sysdatabasessys.databasesContains one row per database in the instance of Microsoft SQL Server.
    sysdevicessys.backup_devicesContains a row for each backup-device registered by using sp_addumpdevice or created in SQL Server Management Studio.
    syslanguagessys.syslanguagesContains one row for each language present in the instance of SQL Server.
    syslockinfosys.dm_tran_locksReturns information about currently active lock manager resources
    syslockssys.dm_tran_locksReturns information about currently active lock manager resources
    sysloginssys.server_principalsContains a row for every server-level principal.
    sys.sql_loginsReturns one row for every SQL login.
    sysmessagessys.messagesContains a row for each message_id or language_id of the error messages in the system, for both system-defined and user-defined messages.
    sysoledbuserssys.linked_loginsReturns a row per linked-server-login mapping, for use by RPC and distributed queries from local server to the corresponding linked server.
    sysopentapessys.dm_io_backup_tapesReturns the list of tape devices and the status of mount requests for backups.
    sysperfinfosys.dm_os_performance_countersReturns a row per performance counter maintained by the server.
    sysprocessessys.dm_exec_connectionsReturns information about the connections established to this instance of SQL Server and the details of each connection.
    sys.dm_exec_sessionsReturns one row per authenticated session on SQL Server.
    sys.dm_exec_requestsReturns information about each request that is executing within SQL Server.
    sysremoteloginssys.remote_loginsReturns 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.
    sysserverssys.serversContains 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 2000SQL Server 2005Description
    fn_virtualfilestatssys.dm_io_virtual_file_statsReturns I/O statistics for data and log files.
    syscolumnssys.columnsReturns a row for each column of an object that has columns, such as views or tables.
    syscommentssys.sql_modulesReturnsa 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.
    sysconstraintssys.check_constraintsContains a row for each object that is a CHECK constraint, with sys.objects.type = 'C'.
    sys.default_constraintsContains 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_constraintsContains a row for each object that is a primary key or unique constraint. Includes sys.objects.type 'PK' and 'UQ'.
    sys.foreign_keysContains a row per object that is a FOREIGN KEY constraint, with sys.object.type = 'F'.
    sysdependssys.sql_expression_dependenciesContains 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.
    sysfilegroupssys.filegroupsContains a row for each data space that is a filegroup.
    sysfilessys.database_filesContains a row per file of a database as stored in the database itself. This is a per-database view.
    sysforeignkeyssys.foreign_key_columnsContains a row for each column, or set of columns, that comprise a foreign key.
    sysindexessys.indexesContains a row per index or heap of a tabular object, such as a table, view, or table-valued function.
    sys.partitionsContains 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_unitsContains a row for each allocation unit in the database.
    sys.dm_db_partition_statsReturns page and row-count information for every partition in the current database.
    sysindexkeyssys.index_columnsContains one row per column that is part of a sys.indexes index or unordered table (heap).
    sysmemberssys.database_role_membersReturns one row for each member of each database role.
    sysobjectssys.objectsContains a row for each user-defined, schema-scoped object that is created within a database.
    syspermissionssys.database_permissionsReturns 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_permissionsReturns one row for each server-level permission.
    sysprotectssys.database_permissionsReturns 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_permissionsReturns one row for each server-level permission.
    sysreferencessys.foreign_keysContains a row per object that is a FOREIGN KEY constraint, with sys.object.type = 'F'.
    systypessys.typesContains a row for each system and user-defined type.
    sysuserssys.database_principalsReturns a row for each principal in a database.
    sysfulltextcatalogssys.fulltext_catalogsContains a row for each full-text catalog.

Share This Page