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