Metadata Views and Dynamic Management Views

With SQL Server 2000, T-SQL developers often used system tables, system stored procedures, and SQL-99 INFORMATION_SCHEMA views to access system metadata to get answers about things such as numbers and names of tables in a database; find out about constraints, indexes, and keys defined for a table; determine the number of columns in a table or the number and names of databases on a server; and so on.

The SQL-99 INFORMATION_SCHEMA views are still available in SQL Server 2005, but system tables are no longer directly accessible. SQL Server 2005 introduces the concept of catalog views, which is a recommended way to access the system metadata. The catalog views provide a consistent and secure interface to access system metadata in SQL Server 2005. All the catalog views are defined in the sys schema. For backward compatibility with previous releases, SQL Server 2005 also provides the compatibility views, which have the same names and return the same data as the system tables. However, using the compatibility views is discouraged, and accessing catalog views is the recommended option if you need to access the system metadata. The INFORMATION_SCHEMA views are not updated to cover all the new features, such as Service Broker, introduced in SQL Server 2005. The catalog views are the only metadata-access interface that covers all the new SQL Server 2005 features. The catalog views are also more efficient than compatibility views and INFORMATION_SCHEMA views.

Table 6.1 lists the catalog view categories and examples of catalog views in each category.

Table 6.1. SQL Server 2005 Catalog Views

Catalog View Category



sys.columns, sys.procedures, sys.objects, sys.indexes, sys.triggers, sys.xml_indexes, sys.synonyms, and so on

Database and file

sys.databases, sys.database_files, sys.backup_devices, and so on

Scalar types

sys.types and sys.assembly_types




sys.certificates, sys.sql_logins, sys.database_principals, sys.credentials, and so on

Database mirroring


Linked server

sys.servers, sys.linked_logins, and sys.remote_logins

Error message


Partition function

sys.partition_functions and so on

SQLCLR integration

sys.assemblies, sys.assembly_files, and so on

Service Broker, sys.routes, sys.transmission_queue, and so on


sys.endpoints, sys.tcp_endpoints, sys.http_endpoints, and so on


sys.xml_indexes, sys.xml_schema_collections, and so on

Data space and Full-Text

sys.data_spaces, sys.filegroups, sys.fulltext_catalogs, and so on


sys.configurations and sys.fulltext_languages

Dynamic management views (DMVs) are one more type of view provided with SQL Server 2005. DMVs can be used to view the current state of the SQL Server 2005 system. In other words, DMVs provide a real-time snapshot of internal memory structures that indicate the server state. This means that instead of using sp_who, you can now use the sys.dm_exec_sessions DMV to effectively get more current details on users logged in. Like catalog views, DMVs are also defined in the sys schema. All DMV names begin with dm_, and the next word in the name indicates the DMV category (for example, sys.dm_exec_sessions refers to the execution category). SQL Server 2005 contains several DMVs to get current memory, I/O, an index, a full-text index, Service Broker information, and transaction activity. DMVs are discussed in more detail in Chapter 9.

Microsoft SQL Server 2005(c) Changing the Paradigm
Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)
ISBN: 0672327783
EAN: 2147483647
Year: 2005
Pages: 150 © 2008-2017.
If you may any questions please contact us: