想要获取列名称及其数据类型，那么查询是什么 [英] want to get the column name and its datatype so what's the query

问题描述

我使用了查询



从information_schema.columns中选择COLUMN_NAME，DATA_TYPE，其中table_name ='''Dtype''按ordinal_position排序



它的输出是



DATA_TYPE COLUMN_NAME

id int

type varchar





这里数据类型显示为varchar但我需要像varchar（50）



我在表创建时选择了我想要的那样请帮助我搜索但我没有.....





以及十进制它给出十进制而不是十进制（18,10）,,,, ....

推荐答案

试试这个

Try this one

SELECT
    COLUMN_NAME,
    (CASE WHEN CAST(DATA_TYPE AS VARCHAR) IN ('int', 'tinyint') THEN DATA_TYPE
         ELSE DATA_TYPE + ' (' + CONVERT(VARCHAR, ISNULL(CHARACTER_MAXIMUM_LENGTH,0)) + ')' END) AS DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Dtype'
ORDER BY ORDINAL_POSITION

你可以继续在case子句中添加不同的类型，甚至你也可以在CASE子句中添加一个新的WHEN。 br $> b $ b

希望它可以帮到你...



快乐编码...... :)



问候，

Vamsi

You can keep on adding different types in the case clause, even you can also add a new WHEN in the CASE clause.

Hope it helps you...

Happy Coding... :)

Regards,
Vamsi

使用这个

use this

select COLUMN_NAME,
    case when DATA_TYPE like '%var%' then Data_Type + '(' + case when character_Maximum_Length=-1 then 'Max' else convert(varchar,character_Maximum_Length) end  + ')'
    else
        case when DATA_TYPE like '%num%' then  Data_Type + '(' + convert(varchar,numeric_Precision) + ',' +  convert(varchar,numeric_Scale)   + ')'
            else Data_type
        end
    end as datatype
from information_schema.columns
where table_name = 'DType' order by ordinal_position

快乐编码！

：）

Happy Coding!
:)

这是符合您要求的工作SQl

Here is the working SQl for your requirement

SELECT column_name
    , CASE
        WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN
           DATA_TYPE + '(' + convert(varchar(MAX), CHARACTER_MAXIMUM_LENGTH) + ')'
        WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN
           DATA_TYPE
       END as DATA_TYPE
FROM
    information_schema.columns
WHERE
    table_name = 'TABLENAME'
ORDER BY
    ordinal_position

这篇关于想要获取列名称及其数据类型，那么查询是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！