Programming SQL Server 2005

3.4. Metadata

SQL Server 2005 introduces catalog views an interface to server metadata which expose information about database objects including tables, views, columns, indexes, and stored procedures. Catalog views expose user-available catalog metadata and are the most efficient way to get, transform, and present catalog metadata. Catalog views can be queried using a SELECT statement in the same way as any other database view and return data as standard result sets. Catalog views are often joined in queries to retrieve complex metadata.

Like information schema viewsanother way to retrieve metadatacatalog views expose metadata independently of the underlying implementation of catalog tables. If the underlying catalog tables change, applications that rely on catalog views will not be affected. Unlike information schema views, catalog views expose metadata specific to SQL Server.

Catalog views do not contain data about replication, backup, database maintenance plans, or SQL Server Agent data.

The following query uses a catalog view to retrieve information about all the tables in the AdventureWorks database. One record is returned in the result set for each table.

SELECT * FROM sys.tables

More information about specific catalog views appears throughout the book. For a complete list of catalog views and their organization, see Microsoft SQL Server 2005 Books Online.