Querying the SQL Server System Catalog
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 they connect. These applications may require information such as the following:
The number and names of the tables and views in a database.
The number of columns in a table or view, together with the name, data type, scale, and precision of each column.
The constraints that are defined on a table.
The indexes and keys that are defined for a table.
The system catalog provides this information for SQL Server databases. The core of the SQL Server system catalogs is a set of views that show metadata that describes the objects in an instance of SQL Server. Metadata is data that describes the attributes of objects in a system. SQL Server-based applications can access the information in the system catalogs by using the following:
Catalog views. We recommended this access method.
Information schema views.
OLE DB schema rowsets.
ODBC catalog functions.
System stored procedures and functions.
Catalog Views
Catalog views provide access to metadata that is stored in every database on the server.
Note
Catalog views do not provide access to replication, SQL Server Agent, or backup metadata.
We recommend using catalog views to access metadata for the following reasons:
All metadata is made available as catalog views.
Catalog views present metadata in a format that is independent of any catalog table implementation, therefore catalog views are not affected by changes in the underlying catalog tables.
Catalog views are the most efficient way to access core server metadata.
Catalog views are the general interface to catalog metadata and provide the most direct way to obtain, transform, and present customized forms of this metadata.
Catalog view names and the names of their columns are descriptive. Query results match what might be expected by a user who has a moderate knowledge of the feature that corresponds to the metadata that is being queried.
For example, the following query uses the sys.objects catalog view to return all database objects that have been modified in the last 10 days.
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 10
ORDER BY modify_date;
For more examples of querying the system catalog by using catalog views, see Querying the SQL Server System Catalog FAQ.
Important
In future releases of SQL Server, Microsoft may augment the definition of any system catalog view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM sys.catalog_view_name in production code because the number of columns returned might change and break your application.
Information Schema Views
Information schema views are based on catalog view definitions in the ISO standard. They present the catalog information in a format that is independent of any catalog table implementation and therefore are not affected by changes in the underlying catalog tables. Applications that use these views are portable between heterogeneous ISO compliant database systems. For more information, see Information Schema Views (Transact-SQL).
Note
Information schema views do not contain metadata specific to SQL Server 2008.
The following example queries the INFORMATION_SCHEMA.COLUMNS view to return all columns for the Person table in the AdventureWorks2008R2 database.
SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks2008R2.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Person';
Compatibility Views
Many of the system tables from earlier versions of SQL Server are now implemented as a set of views. These views are known as compatibility views, and they are intended for backward compatibility only. They show the same metadata that is available in SQL Server 2000. However, they do not show any of the metadata that is related to features introduced in SQL Server 2005 and later. Therefore, when you use new features, such as Service Broker or partitioning, you must to switch to using the catalog views. This is a good reason to upgrade to catalog views. Another reason for upgrading to the catalog views is that compatibility view columns that store user IDs and type IDs might return NULL or trigger arithmetic overflows. This is because, in SQL Server 2005 and later, you can create more than 32,767 users and data types. For example, if you were to create 32,768 users, and then run this query: SELECT * FROM sys.sysusers; if ARITHABORT is set to ON, the query fails with an arithmetic overflow error. If ARITHABORT is set to OFF, the uid column returns NULL.
To avoid these problems, we recommend that you upgrade to the using the new catalog views that can handle the increased number of user IDs and type IDs.
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 that is 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 (OLE DB).
The Microsoft SQL Server Native Client OLE DB Provider supports an extension to IDBSchemaRowset that reports catalog information for the linked servers used in distributed queries. For more information, see LINKEDSERVERS Rowset (OLE DB).
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 that is supported by different ODBC drivers. The result sets are independent of the structure of the underlying catalog tables.
The SQL Server Native Client 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 (Transact-SQL) and System Stored Procedures (Transact-SQL).