SQL Server query to get data type for all columns in table?

Question

I have this SQL Server 2012 query that I am running in SSMS:

SELECT name, max_length, precision, scale, is_nullable 
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.testtable')

It is returning what I want:

However, I would like to add the data type of the column in this query, so that it looks like what I see in the object explorer. Any thoughts on how to add the data type?

Answer 1

Here are a couple of options:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'YourTable'

or

exec sp_help 'YourTable'

Answer 2

SELECT c.name,
       c.max_length,
       c.precision,
       c.scale,
       c.is_nullable,
       t.name
  FROM sys.columns c
  JOIN sys.types   t
    ON c.user_type_id = t.user_type_id
 WHERE c.object_id    = Object_id('dbo.testtable')