使用数值中的数据类型和最大长度获取 SQL 表列模式 [英] Get the SQL table column schema with Datatype and Max Length in number value
问题描述
我有一个 Table_A,其列名称和数据类型如下:
I have a Table_A with the column Name and Data Type as below:
QUANTITY int
PO_NO varchar(13)
FLG char(1)
AMOUNT money
我想使查询返回这样的结果:
I want to make the query that return the result like this:
QUANTITY TYPE="3" LENGHT="4"
PO_NO TYPE="200" LENGHT="13"
FLG TYPE="129" LENGHT="1"
AMOUNT TYPE="6" LENGTH="8"
我使用这个查询来获取列模式,但结果与我的预期不一样
I use this query to get column schema, but the result is not same as my expectation
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='Table_A'
结果:
COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
QUANTITY int NULL
PO_NO varchar 13
FLG char 1
AMOUNT money NULL
所以,我的问题是:如何知道数据类型 int 等价于 Type=3 和 length =4 等等.
So , my question is: How to know the data type int is equivalent to Type=3 and length =4 and so on.
推荐答案
做这样一个事实,即 int、mother 和其他非字符串数据类型具有 fidex 定义的长度,你在列 CHARACTER_MAXIMUM_LENGTH 中找不到合适的值如果您需要,您可以使用 CASE WHEN 重新映射
do the fact that int, mother and other not string data type have fidex defined length you cant find a proper value in column CHARACTER_MAXIMUM_LENGTH if you need you could remap ths with a CASE WHEN
select COLUMN_NAME
, DATA_TYPE
, CASE DATA_TYPE
WHEN 'int' THEN 4
WHEN 'money' THEN 8
ELSE CHARACTER_MAXIMUM_LENGTH
END
, CHARACTER_MAXIMUM_LENGTH
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='Table_A'
这篇关于使用数值中的数据类型和最大长度获取 SQL 表列模式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!