Get field name, data type and size of database table

Estimated study time: 6 minutes. Inspect a table's structure without opening the designer.

Whenever you're working with an unfamiliar table, it's useful to pull its column names, data types, and sizes directly with a query instead of clicking through the GUI. SQL Server gives you a couple of reliable ways to do this.

Using INFORMATION_SCHEMA

The ANSI-standard INFORMATION_SCHEMA.COLUMNS view works across most relational databases, including SQL Server:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees';

Using System Catalog Views

For SQL Server–specific detail — like exact precision, scale, and identity flags — the catalog views give you more:

SELECT c.name AS ColumnName,
       t.name AS DataType,
       c.max_length AS SizeInBytes,
       c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('Employees');

Using sp_help

SQL Server also ships a quick built-in procedure that dumps a full structural summary, including columns, indexes, and constraints:

EXEC sp_help 'Employees';

When to Use Which

Use INFORMATION_SCHEMA when you want portable queries that could run on other database engines too. Use the sys.* catalog views when you need SQL Server–specific detail that INFORMATION_SCHEMA doesn't expose.