LLI Wiki — My Digital Knowledge Base

List table columns with INFORMATION_SCHEMA

T-SQL query on INFORMATION_SCHEMA.COLUMNS that returns the type, length, nullability, identity flag and default value of a SQL Server table's columns.

This query lists the columns of a SQL Server table with their main characteristics, in definition order. Replace TableName with the table name.

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE, COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IS_AUTOINCREMENT, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName' ORDER BY ORDINAL_POSITION

Returned columns

Column Content
COLUMN_NAME Column name
DATA_TYPE Data type
CHARACTER_MAXIMUM_LENGTH Maximum length for character types
NUMERIC_PRECISION, NUMERIC_SCALE Precision and scale for numeric types
IS_NULLABLE YES if the column accepts NULL
IS_AUTOINCREMENT 1 if the column is an IDENTITY column
COLUMN_DEFAULT Default value of the column

Notes

Mastodon LLIMastodon SocialFosstodon