Sometimes using the SSMS GUI table designer is a hassle when compared with directly querying the table structure using a simple SQL statement.
For example if you are right in the middle of editing a stored procedure and want to know the data types and sizes of some columns for a table or two, then rather than navigating away from your procedure, you can do a lookup within the code itself. This saves you a lot of tabbing between windows since the table information is right where you need it; where you are writing your code.
1) Query the SQL Server information schema views:
USE MyDataBase GO SELECT TABLE_NAME ,ORDINAL_POSITION ,COLUMN_NAME ,DATA_TYPE ,CHARACTER_MAXIMUM_LENGTH ,IS_NULLABLE ,COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('MyTableName','MySecondTable') ORDER BY TABLE_NAME,COLUMN_NAME ASC
2) Query the System objects/Columns/Types Tables
USE MyDataBase GO SELECT [tblObjects].NAME AS [TABLE NAME] ,[tblColumns].NAME AS [COLUMN NAME] ,[tblTypes].NAME AS [DATA TYPE] ,[tblColumns].MAX_LENGTH AS [SIZE] ,[tblColumns].is_nullable [IS_NULLABLE] FROM .SYS.OBJECTS AS [tblObjects] INNER JOIN SYS.COLUMNS AS [tblColumns] ON [tblObjects].OBJECT_ID=[tblColumns].OBJECT_ID INNER JOIN SYS.TYPES AS [tblTypes] ON [tblColumns].SYSTEM_TYPE_ID=[tblTypes].SYSTEM_TYPE_ID WHERE [tblObjects].TYPE_DESC='USER_TABLE' AND [tblObjects].NAME IN ('MyTableName','MySecondTable') ORDER BY [tblObjects].NAME,[tblTypes].NAME
Both of these methods work very nicely in SQL Server 2005 and SQL Server 2008. The Information_Schema structure complies with the ISO standard definition, so this version of the lookup should be supported by any modern ISO-compliant database.
If you are interested in some more handy t-SQL queries and tips, have a look at this collection of articles.