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

Question

I need a smart way to get data types from INFORMATION_SCHEMA.COLUMNS in a way that could be used in a CREATE TABLE statement. The problem is the "additional" fields that need to be understood, for example, NUMERIC _ PRECISION and NUMERIC _ SCALE.

Obviously, I can ignore the columns for INTEGER (precision 10 and scale 0), but there are other types that interest me, like NUMERIC. So without writing a lot of code to parse the table, any ideas on how to get the kind of field transcript from the column definition?

I would like to be able to get something like: INT, Date and Time, Money, Numeric ** (10.2) **

Answer 1

Below is a description (ripoff!) Of GalacticCowboy's answer to fix some problems and update all (I think) SQL Server 2008R2 data types:

 select data_type + case when data_type like '%text' or data_type in ('image', 'sql_variant' ,'xml') then '' when data_type in ('float') then '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ')' when data_type in ('datetime2', 'datetimeoffset', 'time') then '(' + cast(coalesce(datetime_precision, 7) as varchar(11)) + ')' when data_type in ('decimal', 'numeric') then '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ',' + cast(coalesce(numeric_scale, 0) as varchar(11)) + ')' when (data_type like '%binary' or data_type like '%char') and character_maximum_length = -1 then '(max)' when character_maximum_length is not null then '(' + cast(character_maximum_length as varchar(11)) + ')' else '' end as CONDENSED_TYPE , * from information_schema.columns order by table_schema, table_name, ordinal_position 

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

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

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. In addition, you will have to analyze the results of viewing the information chart.

Answer 4

If you are using smo, you can get both precision and scale by referring to the collomery of properties of the Column Object

Column.Property ["NumericScale"]. Value

Column.Property ["NumericPrecision"]. Value