What is the SQL command to return the field names of a table?

MySQL 3 and 4 (and 5):

desc tablename

which is an alias for

show fields from tablename

SQL Server (from 2000) and MySQL 5:

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = 'tablename'

Completing the answer: like people below have said, in SQL Server you can also use the stored procedure sp_help

exec sp_help 'tablename'