The information schema consists of read-only views that provide information about all tables, views, and columns of the Database Engine to which you have access. In contrast to the system catalog that manages the metadata applied to the system as a whole, the information schema primarily manages the environment of a database.
The following sections provide a description of the most important information schema views.
Information_schema.tables
The Information_schema.tables view contains one row for each table in the current database to which the user has access. The view retrieves the information from the system catalog using the sys.objects catalog view. Table 9-4 lists and describes the four columns of this view.
Table 9-4 The Information_schema.tables View
| Column | Description |
|---|---|
| TABLE_CATALOG | The name of the catalog (database) to which the view belongs |
| TABLE_SCHEMA | The name of the schema to which the view belongs |
| TABLE_NAME | The table name |
| TABLE_TYPE | The type of the table (can be BASE TABLE or VIEW) |
Table 9-5 The Information_schema.columns View
| Column | Description |
|---|---|
| TABLE_CATALOG | The name of the catalog (database) to which the column belongs |
| TABLE_SCHEMA | The name of the schema to which the column belongs |
| TABLE_NAME | The name of the table to which the column belongs |
| COLUMN_NAME | The column name |
| ORDINAL_POSITION | The ordinal position of the column |
| DATA_TYPE | The data type of the column |
Information_schema.columns
The Information_schema.columns view contains one row for each column in the current database accessible by the user. The view retrieves the information from the sys.columns and sys.objects catalog views. Table 9-5 lists and describes the six most important columns of this view.
