How can I get SQL Server column definition with parentheses and everything?

Question

I need a smart way to get the data types out of INFORMATION_SCHEMA.COLUMNS in a way that could be used in a CREATE TABLE statement. The problem is the 'extra' fields that need to be understood, such as NUMERIC_PRECISION and NUMERIC_SCALE.

Obviously, I can ignore the columns for INTEGER (precision of 10 and scale of 0), but there are other types I would be interested in, such as NUMERIC. So without writing lots of code to parse the table, any ideas on how to get a sort of field shorthand out of the column definition?

I would like to be able to get something like : int, datetime, money, numeric**(10,2)**

Answer 1

SMO Scripting should take care of the script generations. I believe that this is what MS uses in SQL Management Studio for script generations.

http://msdn.microsoft.com/en-us/library/ms162153.aspx

@YourComment - I need a smart way to get the data types out of INFORMATION_SCHEMA.COLUMNS in a way that could be used in a CREATE TABLE statement

This is what you asked for. Short of that, you will have to parse the info schema view results.

Answer 2

select column_type = data_type + 
    case
     when data_type like '%text' then ''
     when data_type like '%char' and character_maximum_length = -1 then '(max)'
     when character_maximum_length is not null then '(' + convert(varchar(10), character_maximum_length) + ')'
     when data_type = 'numeric' then '(' + convert(varchar(10), isnull(numeric_precision, 18)) + ', ' + 
      convert(varchar(10), isnull(numeric_scale, 0)) + ')'
     else ''
    end
,*
from information_schema.columns

Answer 3

If you're using smo you can get both precision and scale by accessing the Properties colletion of the Column Object

Column.Property["NumericScale"].Value

Column.Property["NumericPrecision"].Value