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	Example
Object	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
Schema	sys.schemas
Security	sys.certificates, sys.sql_logins, sys.database_principals, sys.credentials, and so on
Database mirroring	sys.database_mirroring_witnesses
Linked server	sys.servers, sys.linked_logins, and sys.remote_logins
Error message	sys.messages
Partition function	sys.partition_functions and so on
SQLCLR integration	sys.assemblies, sys.assembly_files, and so on
Service Broker	sys.services, sys.routes, sys.transmission_queue, and so on
Endpoint	sys.endpoints, sys.tcp_endpoints, sys.http_endpoints, and so on
XML	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
Configuration	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.