List columns, fields, datatypes in a table

Picture from http://www.geekosystem.com/periodic-meme-table/

Whenever I want to check on the column name and its datatype, I would usually go to each table in the SQL Management Studio then click on the tables folder then columns folder for me to find out what a columns datatype is. It's ok at first, but we need to get something fast.

Then I found this answer by Marlon Ribunal in Stackoverflow.com where he gave two solutions to display the list of columns in the table. The one which worked for me is this one.

 SELECT TABLE_SCHEMA  
      ,TABLE_NAME  
      ,COLUMN_NAME  
      ,ORDINAL_POSITION  
      ,COLUMN_DEFAULT  
      ,DATA_TYPE  
      ,CHARACTER_MAXIMUM_LENGTH  
      ,NUMERIC_PRECISION  
      ,NUMERIC_PRECISION_RADIX  
      ,NUMERIC_SCALE  
      ,DATETIME_PRECISION  
 FROM <Database_Name>.INFORMATION_SCHEMA.COLUMNS  

For me, this is quick. At least, I don't have to go through the tables again and scan for the column name and datatype.

Hope this helps you.

Happy coding!

UPDATE [02/08/2014]
The code above sadly works for SQL Server 2008 and above. For those using SQL Server 2005, here's the code:

 SELECT column_name 'Column Name'  
      ,data_type 'Data Type'  
      ,character_maximum_length 'Maximum Length'  
 FROM information_schema.columns  
 WHERE table_name = 'TableNamel'  

Happy coding!