Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
COLUMNS (Transact-SQL)

COLUMNS (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

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.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Column name

Data type

Description

TABLE_CATALOG 

nvarchar(128)

Table qualifier.

TABLE_SCHEMA 

nvarchar(128)

Name of schema that contains the table.

System_CAPS_importantImportant

Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.

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.

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.

System_CAPS_importantImportant

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.

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 AdventureWorks2012;
GO
SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks2012.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Person';
GO

Community Additions

ADD
Show:
© 2015 Microsoft