| title | System Information Schema Views (Transact-SQL) | Microsoft Docs | |||||
|---|---|---|---|---|---|---|
| ms.custom | ||||||
| ms.date | 03/14/2017 | |||||
| ms.prod | sql | |||||
| ms.prod_service | database-engine | |||||
| ms.reviewer | ||||||
| ms.suite | sql | |||||
| ms.technology | system-objects | |||||
| ms.tgt_pltfrm | ||||||
| ms.topic | language-reference | |||||
| dev_langs |
|
|||||
| helpviewer_keywords |
|
|||||
| ms.assetid | 7e9f1dfe-27e9-40e7-8fc7-bfc5cae6be10 | |||||
| caps.latest.revision | 29 | |||||
| author | edmacauley | |||||
| ms.author | edmaca | |||||
| manager | craigg |
[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]
An information schema view is one of several methods [!INCLUDEssNoVersion] provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] comply with the ISO standard definition for the INFORMATION_SCHEMA.
Important
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.
[!INCLUDEssNoVersion] supports a three-part naming convention when you refer to the current server. The ISO 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] ISO-compatible 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 AdventureWorks2012.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Product';
GO
System Views (Transact-SQL)
Data Types (Transact-SQL)
System Stored Procedures (Transact-SQL)