Querying SQL Server System Catalogs

Dynamic applications that are not hard-coded to work with a specific set of tables and views must have a mechanism for determining the structure and attributes of the objects in any database to which it connects. The applications may need information such as:

• The number and names of the tables and views in a database.
  
  
• The number of columns in a table or view, along with each column's name, data type, scale, and precision.
  
  
• The constraints defined on a table.
  
  
• The indexes and keys defined for a table.

The Microsoft® SQL Server™ 2000 system catalog provides this information for SQL Server databases. The core of the SQL Server system catalogs is a set of system tables containing meta data describing the objects in a SQL Server database. Meta data is data that describes the attributes of objects in a system.

SQL Server applications can access the information in the system catalogs in several ways by using:

• Information Schema Views

  Information Schema Views are based on catalog view definitions in the SQL-92 standard. They present the catalog information in a format independent of any catalog table implementation, thus are not affected by changes in the underlying catalog tables. Applications that use these views are portable between heterogeneous SQL-92 compliant database systems. For more information, see Information Schema Views.

• OLE DB schema rowsets

  The OLE DB specification defines an IDBSchemaRowset interface that exposes a set of schema rowsets that contain the catalog information. The OLE DB schema rowsets are a standard method of presenting catalog information supported by different OLE DB providers. The rowsets are independent of the structure of the underlying catalog tables. For more information, see Schema Rowset Support in SQLOLEDB.

  The OLE DB Provider for SQL Server supports an extension to IDBSchemaRowset that reports catalog information for the linked servers used in distributed queries. For more information, see LINKEDSERVERS Rowset.

• ODBC catalog functions

  The ODBC specification defines a set of catalog functions that return result sets that contain the catalog information. These functions are a standard method of presenting catalog information supported by different ODBC drivers. The result sets are independent of the structure of the underlying catalog tables.

  The SQL Server ODBC driver supports two driver-specific functions that report catalog information for the linked servers used in distributed queries. For more information, see Using Catalog Functions.

• System stored procedures and functions

  Transact-SQL defines server system stored procedures and system functions that return catalog information. Although these stored procedures and functions are specific to SQL Server, they insulate users from the structure of the underlying system catalog tables. For more information, see Metadata Functions and System Stored Procedures.

It is not recommended that users query the system catalog tables directly.  This should only be done if none of the methods above supply the needed information. The structure of the system catalog tables is dependent on the underlying architecture of SQL Server, and changes from one version to another. Even an application that only issues SELECT statements may have to be at least partially rewritten when migrating to a new version of SQL Server if it directly queries system tables that change or are not present in the new version.

Updating, deleting, or inserting data in a system table can cause unpredictable effects in a SQL Server system. Such updates are not supported by Microsoft.