Information schema provides an internal, system table-independent view of the Dataset metadata. The included information schema complies with the SQL-92 standard definition for the INFORMATION_SCHEMA.
Each information schema contains metadata for all data objects stored in a particular Dataset. The following table shows the relationships between DataSet-SQL names and the SQL standard names.
|
Dataset Name
|
Maps to this equivalent SQL standard name
|
|
Dataset
|
Catalog
|
|
Schema
|
N/A - will always be blank
|
|
Object
|
Object
|
|
user-defined data type
|
Domain - not supported
|
The following is an example of using the information schema in a query:
SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Product';
Returns one row for each column of every table in the Dataset.
|
Column name
|
Data type
|
Description
|
|
TABLE_CATALOG
|
String
|
Dataset name
|
|
TABLE_SCHEMA
|
String
|
Always blank
|
|
TABLE_NAME
|
String
|
Table name.
|
|
COLUMN_NAME
|
String
|
Column name.
|
|
ORDINAL_POSITION
|
int
|
Column identification number.
|
|
COLUMN_DEFAULT
|
String
|
Default value of the column, otherwise NULL.
|
|
IS_NULLABLE
|
String
|
Nullability of the column. If this column allows for NULL, this column returns YES. Otherwise, NO is returned.
|
|
DATA_TYPE
|
String
|
System-supplied data type, i.e. varchar, uniqueidentifer
|
|
CHARACTER_MAXIMUM_LENGTH
|
int
|
Maximum length, in characters, for binary data, character data, or text and image data.
|
|
CHARACTER_OCTET_LENGTH
|
int
|
Maximum length, in bytes, for binary data, character data, or text and image data.
|
|
NUMERIC_PRECISION
|
tinyint
|
Not implemented. Always returns NULL.
|
|
NUMERIC_PRECISION_RADIX
|
smallint
|
Not implemented. Always returns NULL.
|
|
NUMERIC_SCALE
|
int
|
Not implemented. Always returns NULL.
|
|
DATETIME_PRECISION
|
smallint
|
Not implemented. Always returns NULL.
|
|
CHARACTER_SET_CATALOG
|
String
|
Not implemented. Always returns NULL.
|
|
CHARACTER_SET_SCHEMA
|
String
|
Not implemented. Always returns NULL.
|
|
CHARACTER_SET_NAME
|
String
|
Not implemented. Always returns NULL.
|
|
COLLATION_CATALOG
|
String
|
Not implemented. Always returns NULL.
|
|
COLLATION_SCHEMA
|
String
|
Not implemented. Always returns NULL.
|
|
COLLATION_NAME
|
String
|
Not implemented. Always returns NULL.
|
|
DOMAIN_CATALOG
|
String
|
Not implemented. Always returns NULL.
|
|
DOMAIN_SCHEMA
|
String
|
Not implemented. Always returns NULL.
|
|
DOMAIN_NAME
|
String
|
Not implemented. Always returns NULL.
|
The following is an example of using the information schema in a query:
SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Product';
Returns one row for each column in the dataset that has a constraint defined on the column.
|
Column name
|
Data type
|
Description
|
|
TABLE_CATALOG
|
String
|
Dataset name
|
|
TABLE_SCHEMA
|
String
|
Always blank
|
|
TABLE_NAME
|
String
|
Table name.
|
|
COLUMN_NAME
|
String
|
Column name.
|
|
CONSTRAINT_CATALOG
|
String
|
Dataset name.
|
|
CONSTRAINT_SCHEMA
|
String
|
Always blank
|
|
CONSTRAINT_NAME
|
String
|
Constraint name.
|
The following is an example of using the information schema in a query:
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = N'Product';
Returns one row for each table in the dataset that has a constraint defined on the table.
|
Column name
|
Data type
|
Description
|
|
TABLE_CATALOG
|
String
|
Dataset name
|
|
TABLE_SCHEMA
|
String
|
Always blank
|
|
TABLE_NAME
|
String
|
Table name.
|
|
CONSTRAINT_CATALOG
|
String
|
Dataset name.
|
|
CONSTRAINT_SCHEMA
|
String
|
Always blank
|
|
CONSTRAINT_NAME
|
String
|
Constraint name.
|
The following is an example of using the information schema in a query:
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE TABLE_NAME = N'Product';
Returns one row for each column that is constrained as a key in the current Dataset.
|
Column name
|
Data type
|
Description
|
|
TABLE_CATALOG
|
String
|
Dataset name
|
|
TABLE_SCHEMA
|
String
|
Always blank
|
|
TABLE_NAME
|
String
|
Table name.
|
|
CONSTRAINT_CATALOG
|
String
|
Dataset name.
|
|
CONSTRAINT_SCHEMA
|
String
|
Always blank
|
|
CONSTRAINT_NAME
|
String
|
Constraint name.
|
|
COLUMN_NAME
|
String
|
Column name.
|
|
ORDINAL_POSITION
|
int
|
Column ordinal position
|
The following is an example of using the information schema in a query:
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, ORDINAL_POSITiON
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = N'Product';
Returns one row for each FOREIGN KEY constraint in the current Dataset.
|
Column name
|
Data type
|
Description
|
|
CONSTRAINT_CATALOG
|
String
|
Dataset name
|
|
CONSTRAINT_SCHEMA
|
String
|
Always blank
|
|
CONSTRAINT_NAME
|
String
|
Constraint name.
|
|
UNIQUE_CONSTRAINT_CATALOG
|
String
|
Dataset name
|
|
UNIQUE_CONSTRAINT_SCHEMA
|
String
|
Always blank
|
|
UNIQUE_CONSTRAINT_NAME
|
String
|
UNIQUE constraint name
|
|
MATCH_OPTION
|
String
|
Always returns SIMPLE.
|
|
UPDATE_RULE
|
String
|
Action taken when a SQL statement violates the referential integrity that is defined by this constraint. Returns one of the following:
- NO ACTION
- CASCADE
- SET NULL
- SET DEFAULT
|
|
DELETE_RULE
|
String
|
Action taken when a SQL statement violates referential integrity defined by this constraint. Returns one of the following:
- NO ACTION
- CASCADE
- SET NULL
- SET DEFAULT
|
The following is an example of using the information schema in a query:
SELECT UNIQUE_CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
Returns one row for the current Dataset.
|
Column name
|
Data type
|
Description
|
|
CATALOG_NAME
|
String
|
Name of Dataset
|
|
SCHEMA_NAME
|
String
|
Always blank
|
|
SCHEMA_OWNER
|
String
|
Always blank
|
|
DEFAULT_CHARACTER_SET_CATALOG
|
String
|
Always returns NULL.
|
|
DEFAULT_CHARACTER_SET_SCHEMA
|
String
|
Always returns NULL.
|
|
DEFAULT_CHARACTER_SET_NAME
|
String
|
Always returns NULL.
|
The following is an example of using the information schema in a query:
SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
Returns one row for each table constraint in the Dataset.
|
Column name
|
Data type
|
Description
|
|
CONSTRAINT_CATALOG
|
String
|
Dataset name
|
|
CONSTRAINT_SCHEMA
|
String
|
Always blank
|
|
CONSTRAINT_NAME
|
String
|
Constraint name.
|
|
TABLE_CATALOG
|
String
|
Dataset name
|
|
TABLE_SCHEMA
|
String
|
Always blank
|
|
TABLE_NAME
|
String
|
Table name.
|
|
CONSTRAINT_TYPE
|
String
|
Type of constraint:
- CHECK
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
|
|
IS_DEFERRABLE
|
String
|
Always returns NO.
|
|
INITIALLY_DEFERRED
|
String
|
Always returns NO.
|
The following is an example of using the information schema in a query:
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = N'Product';
Returns one row for each table in the Dataset.
|
Column name
|
Data type
|
Description
|
|
TABLE_CATALOG
|
String
|
Dataset name
|
|
TABLE_SCHEMA
|
String
|
Always blank
|
|
TABLE_NAME
|
String
|
Table name.
|
|
TABLE_TYPE
|
String
|
Type of table. Only BASE TABLE supported.
|
The following is an example of using the information schema in a query:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;