選擇數據類型+案件當 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
這篇關于如何使用括號和所有內容獲取 SQL Server 列定義?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
