How to Describe a Table in SQL Server

sp_help returns several result sets at once: columns and types, identity information, the filegroup, then indexes, constraints, and foreign key references. It is the most complete single command, and those multiple result sets are exactly why it is awkward to consume programmatically.

EXEC sp_help 'orders';

Columns only

INFORMATION_SCHEMA.COLUMNS is the one to use when you need results you can filter, join, or export. Note what is missing from this example: add TABLE_SCHEMA = 'dbo' if the same table name exists in more than one schema, otherwise you get the columns of both interleaved.

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'orders'
ORDER BY ORDINAL_POSITION;

ODBC-style

sp_columns returns an ODBC-standard shape, verbose for humans but predictable for tools, so it is mainly useful when something else expects that exact format. For everyday work sp_help or the INFORMATION_SCHEMA query is easier to read.

EXEC sp_columns 'orders';