What is the equivalent of 'describe table' in MSSQL?

Question

I have a Microsoft SQL database and I want to know what columns and types it has. I'd prefer to do this through a query rather than using a GUI like Enterprise Manager. Is there a way to do this?

Answer 1

There are a few methods to get metadata about a table:

sp_help @tablename

Will return several result sets, describing the table, it's columns and constraints.

The INFORMATION_SCHEMA views will give you the information you want, though unfortunately you have to query the views and join them manually.

Answer 2

You can use the sp_columns stored procedure:

exec sp_columns MyTable

Answer 3

Just in case you don't want to use stored proc, here's a simple query version

select * 
  from information_schema.columns 
 where table_name = 'aspnet_Membership'
 order by ordinal_position

Answer 4

The trick given by "Vincent Ramdhanie" works perfectly fine. Thanks Vincent for sharing it.