What SQL command can I use to view the table structure on SQL Server?

Question

I have a table on SQL Server, and I would like to export its structure for sending to a colleague. What SQL command should I execute to get the structure? I do not have access to SQL Server Management Studio.

Answer 1

sp_help '<TableName>' 

will give you the table structure

You can also use information_Schema for more information,

 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' 

Check it out http://msdn.microsoft.com/en-us/library/ms186778.aspx

Answer 2

Brad Schultz has a pretty cool DDL script generation that he just wrote on his blog: (There are a few limitations with him, though)

http://bradsruminations.blogspot.com/2010/06/more-fun-with-hyperlinks-ddl-code.html

Answer 3

Visual Studio has tools for designing and viewing the database schema in the "Data Connections in Server Explorer" section. Some versions also have schema and data comparison tools similar to Red Gate Sql Compare