Justin Cooney

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.