You have to go further than that, including considerations of datetime, datetime2, etc..
NOTE: The code below does NOT consider any user-defined data types.
-- list column data type, including len(s), if applicable
CASE WHEN c.is_computed = 1 THEN 'AS ' +
(SELECT definition FROM sys.computed_columns cc WHERE cc.object_id = c.object_id AND cc.column_id = c.column_id) +
CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE '' END
ELSE t.name + CASE
WHEN t.name LIKE '%bin%' OR t.name LIKE '%char%' THEN
'(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS varchar(10)) + ')' END
WHEN t.name IN ('datetime2', 'time') THEN
'(' + CAST(c.scale AS varchar(3)) + ')'
WHEN t.name IN ('decimal', 'numeric') THEN
'(' + CAST(c.precision AS varchar(3)) + ', ' + CAST(c.scale AS varchar(3)) + ')'
WHEN t.name IN ('float', 'real') THEN
'(' + CAST(c.precision AS varchar(3)) + ')'
ELSE '' END +
CASE WHEN c.is_nullable = 0 THEN ' NOT' ELSE '' END + ' NULL'
END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.