如何使用括号和所有内容获取 SQL Server 列定义?

我需要一种聪明的方法来以可在 CREATE TABLE 语句中使用的方式从 INFORMATION_SCHEMA.COLUMNS 中获取数据类型.问题是需要理解的额外"字段，例如 NUMERIC_PRECISION 和 NUMERIC_SCALE.

显然，我可以忽略 INTEGER 的列(精度为 10，小数位数为 0)，但还有其他类型我会感兴趣，例如 NUMERIC.因此，无需编写大量代码来解析表，是否有任何关于如何从列定义中获取某种字段速记的想法?

我希望能够得到类似的东西:内部，约会时间，钱，数字**(10,2)**

解决方案

这是 GalacticCowboy 的回答的更新(抄袭！)

a> 修复一些问题并更新所有(我认为)SQL Server 2008R2 数据类型:

选择数据类型+案件当 data_type 像 '%text' 或 data_type in ('image', 'sql_variant' ,'xml')然后 ''当 data_type in ('float')然后 '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ')'当 data_type in ('datetime2', 'datetimeoffset', 'time')然后 '(' + cast(coalesce(datetime_precision, 7) as varchar(11)) + ')'当 data_type in ('decimal', 'numeric')然后 '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ',' + cast(coalesce(numeric_scale, 0) as varchar(11)) + ')'当 (data_type like '%binary' 或 data_type like '%char') 和 character_maximum_length = -1然后'(最大)'当 character_maximum_length 不为空时然后 '(' + cast(character_maximum_length as varchar(11)) + ')'别的 ''以 CONDENSED_TYPE 结尾, *来自 information_schema.columns按 table_schema、table_name、ordinal_position 排序

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)**

解决方案

Here is an update (ripoff!) of GalacticCowboy's answer to fix some issues and update for all (I think) SQL Server 2008R2 datatypes:

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