COLUMNS (Transact-SQL)
Returns one row for each column that can be accessed by the current user in the current database.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA.view_name.
Column name |
Data type |
Description |
|---|---|---|
TABLE_CATALOG |
nvarchar(128) |
Table qualifier. |
TABLE_SCHEMA |
nvarchar(128) |
Name of schema that contains the table. .gif "Important note") Important
Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of an object is to query the sys.objects catalog view or use the OBJECT_SCHEMA_NAME function.
|
TABLE_NAME |
nvarchar(128) |
Table name. |
COLUMN_NAME |
nvarchar(128) |
Column name. |
ORDINAL_POSITION |
int |
Column identification number. |
COLUMN_DEFAULT |
nvarchar(4000) |
Default value of the column. SQL Server 2008 differs from SQL Server 2000 in the way it decodes and stores SQL expressions in the catalog metadata. The semantics of the decoded expression are equivalent to the original text; however, there are no syntactic guarantees. For example, white spaces are removed from the decoded expression. For more information, see, Behavior Changes to Database Engine Features in SQL Server 2008. |
IS_NULLABLE |
varchar(3) |
Nullability of the column. If this column allows for NULL, this column returns YES. Otherwise, NO is returned. |
DATA_TYPE |
nvarchar(128) |
System-supplied data type. |
CHARACTER_MAXIMUM_LENGTH |
int |
Maximum length, in characters, for binary data, character data, or text and image data. -1 for xml and large-value type data. Otherwise, NULL is returned. For more information, see Data Types (Transact-SQL). |
CHARACTER_OCTET_LENGTH |
int |
Maximum length, in bytes, for binary data, character data, or text and image data. -1 for xml and large-value type data. Otherwise, NULL is returned. |
NUMERIC_PRECISION |
tinyint |
Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned. |
NUMERIC_PRECISION_RADIX |
smallint |
Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned. |
NUMERIC_SCALE |
int |
Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned. |
DATETIME_PRECISION |
smallint |
Subtype code for datetime and ISO interval data types. For other data types, NULL is returned. |
CHARACTER_SET_CATALOG |
nvarchar(128) |
Returns master. This indicates the database in which the character set is located, if the column is character data or text data type. Otherwise, NULL is returned. |
CHARACTER_SET_SCHEMA |
nvarchar(128) |
Always returns NULL. |
CHARACTER_SET_NAME |
nvarchar(128) |
Returns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned. |
COLLATION_CATALOG |
nvarchar(128) |
Always returns NULL. |
COLLATION_SCHEMA |
nvarchar(128) |
Always returns NULL. |
COLLATION_NAME |
nvarchar(128) |
Returns the unique name for the collation if the column is character data or text data type. Otherwise, NULL is returned. |
DOMAIN_CATALOG |
nvarchar(128) |
If the column is an alias data type, this column is the database name in which the user-defined data type was created. Otherwise, NULL is returned. |
DOMAIN_SCHEMA |
nvarchar(128) |
If the column is a user-defined data type, this column returns the name of the schema of the user-defined data type. Otherwise, NULL is returned. Important
Do not use INFORMATION_SCHEMA views to determine the schema of a data type. The only reliable way to find the schema of a type is to use the TYPEPROPERTY function.
|
DOMAIN_NAME |
nvarchar(128) |
If the column is a user-defined data type, this column is the name of the user-defined data type. Otherwise, NULL is returned. |
Remarks
The ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by the COLUMNS_UPDATED function. To obtain a bit pattern that is compatible with COLUMNS_UPDATED, you must reference the ColumnID property of the COLUMNPROPERTY system function when you query the INFORMATION_SCHEMA.COLUMNS view. For example:
USE AdventureWorks;
GO
SELECT COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employee';
Examples
The following example returns the columns in all tables and views in the database that cannot have a NULL value.
USE AdventureWorks;
GO
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'NO'
ORDER BY DATA_TYPE;
See Also