Export (0) Print
Expand All

Information Schema Views (Transact-SQL)

An information schema view is one of several methods SQL Server 2005 provides for obtaining metadata.

ms186778.note(en-US,SQL.90).gifImportant:
Some changes have been made to the information schema views that break backward compatibility. These changes are described in the topics for the specific views.

ms186778.note(en-US,SQL.90).gifNote:
Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server 2005 comply with the SQL-92 standard definition for the INFORMATION_SCHEMA.

SQL Server supports a three-part naming convention when you refer to the current server. The SQL-92 standard also supports a three-part naming convention. However, the names used in both naming conventions are different. The information schema views are defined in a special schema named INFORMATION_SCHEMA. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database. The following table shows the relationships between the SQL Server names and the SQL standard names.

SQL Server name Maps to this equivalent SQL standard name

Database

Catalog

Schema

Schema

Object

Object

user-defined data type

Domain

This name-mapping convention applies to the following SQL Server SQL-92-compatible views.

CHECK_CONSTRAINTS

REFERENTIAL_CONSTRAINTS

COLUMN_DOMAIN_USAGE

ROUTINES

COLUMN_PRIVILEGES

ROUTINE_COLUMNS

COLUMNS

SCHEMATA

CONSTRAINT_COLUMN_USAGE

TABLE_CONSTRAINTS

CONSTRAINT_TABLE_USAGE

TABLE_PRIVILEGES

DOMAIN_CONSTRAINTS

TABLES

DOMAINS

VIEW_COLUMN_USAGE

KEY_COLUMN_USAGE

VIEW_TABLE_USAGE

PARAMETERS

VIEWS

Also, some views contain references to different classes of data such as character data or binary data.

When you reference the information schema views, you must use a qualified name that includes the INFORMATION_SCHEMA schema name. For example:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Product';
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft