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.

TABLE_NAME

nvarchar(128)

Table name.

COLUMN_NAME

nvarchar(128)

Column name.

ORDINAL_POSITION

int

Column identification number.

Note:

In SQL Server 2005, these column IDs are consecutive numbers.

COLUMN_DEFAULT

nvarchar(4000)

Default value of the column.

SQL Server 2005 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 2005.

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 SQL-92 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.

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

In SQL Server 2005, 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 TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Contact';
GO

See Also

Reference

System Views (Transact-SQL)
Information Schema Views (Transact-SQL)
sys.syscharsets (Transact-SQL)
sys.columns (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.configurations (Transact-SQL)
sys.objects (Transact-SQL)
sys.types (Transact-SQL)
COLUMNS_UPDATED (Transact-SQL)

Other Resources

Querying the SQL Server System Catalog

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	New content: In the COLUMN_DEFAULT column, added content about how SQL expressions are stored in SQL Server 2005.