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
- The filter only uses
TABLE_NAME. If several schemas contain a table with the same name, rows for each of them are returned. AddAND TABLE_SCHEMA = 'schema'to tell them apart.