What is the equivalent of 'describe table' in SQL Server?

I have a SQL Server 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?

Solution 1:

You can use the sp_columns stored procedure:

exec sp_columns MyTable

Solution 2:

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

EXEC 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.

Solution 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

Solution 4:

You can use following: sp_help tablename

Example: sp_help Customer

OR Use Shortcut Keys

Select the desired table and press ALT+F1.

Example: Customer Press ALT+F1.

Solution 5:

Use this Query

Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'TABLENAME'