Metadata (Transact-SQL)

Microsoft SQL Server 2005 introduces major changes to the way in which users can access system metadata information.

Catalog Views

Catalog views are a completely new relational interface to the system catalog metadata. These views provide access to metadata that is stored in every database on the server. For more information, see Catalog Views (Transact-SQL).

Dynamic Management Views

Dynamic management views contain nonpersisted metadata that represents ongoing server activity, dynamically changing state, and diagnostic information. Frequently, dynamic management views offer point-in-time snapshot of the internal memory structures of the server. Because the dynamic management views show dynamically changing data, read consistency is not guaranteed. For more information, see Dynamic Management Views and Functions.

Information Schema Views

Information schema views with columns of the format xxx_SCHEMA now return the schema name. In earlier versions of SQL Server, such information schema views return the user name. For more information, see Information Schema Views (Transact-SQL).

System Tables

In SQL Server 2005, the Database Engine system tables from earlier versions of SQL Server have been implemented as read-only views for backward compatibility. You cannot directly work with the data in the system tables. For more information, see System Tables (Transact-SQL).

Replication System Tables

Many of the existing replication system tables have been updated to support new replication functionalities. For more information, see Replication Enhancements. The following table lists the new replication system tables.

New system table Description


Contains replication agent parameter information and is used to specify the parameters that can be set for a specific agent type.


Used to track the log sequence number (LSN) values in the transaction log that are used to determine which commands to return to a specific Subscriber in peer-to-peer replication.


Contains a master list of all versions of the database management systems (DBMS) supported by replication for heterogeneous publishing.


Defines the complete list of the host data types at each supported DBMS.


Defines the allowed data type mappings from the data type in the source DBMS to a specific data type in the destination DBMS


Contains the source data type information for a mapping. This information is used to describe specific instances of a data type at the source BDMS. This information also defines the two DBMS systems that make up the mapping.


Tracks conflicts that occur when synchronizing a subscription to a merge publication.


Stores one row for each partition ID that a specific changed row belongs to.


Tracks the location of the dynamic snapshot for each partition defined for a merge publication.


Tracks changes to partitions in a merge publication.


Stores one row for each partition group in a specific database.


Stores historical information about the partitions to which a specified row in a published table used to belong.


Maps each transaction to a subscription in a peer-to-peer replication topology.


Maintains a record of all tracer records that have been inserted into the transaction log at the Publisher.


Contains one row for each table column that is published in a snapshot or transactional publication, and maps each column to its merge article.


Contains one row for each table column that is published in a merge publication, and maps each column to its merge article.


Provides information about partitions for each article.


Tracks schema changes in articles published in transactional and snapshot publications.

For more information, see Replication Tables (Transact-SQL).

See Also

Other Resources

What's New and Enhanced in Transact-SQL (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance