want to get the column name and its datatype so what's the query

Question

0.00/5 (No votes)

See more:

I used the query as

select COLUMN_NAME,DATA_TYPE from information_schema.columns where table_name = 'Dtype' order by ordinal_position

its output is

DATA_TYPE COLUMN_NAME
id int
type varchar

here the datatype is shown as varchar but i need like as varchar(50)

what i had select in the table creation time i want that so please help me i searched but i don't got.....

and also for decimal it gave as decimal instead of decimal(18,10),,,,....

Posted 18-Dec-12 22:03pm

Member 9644686

Updated 20-Dec-12 18:07pm

v2

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RelicV · Accepted Answer · 2012-12-18T22:14:00

Try this one

SQL

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

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

Aarti Meswania · Accepted Answer · 2012-12-18T22:21:00

Solution 2

use this

SQL

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!
:)

Posted 18-Dec-12 22:21pm

Aarti Meswania

Comments

Member 9644686 19-Dec-12 4:34am

Thanks a lot...

Aarti Meswania 19-Dec-12 4:52am

welcome! :)
Glad to help you! :)

do u have give us stars(vote answer)? :)

Menon Santosh 20-Dec-12 8:03am

+5

Aarti Meswania 20-Dec-12 8:12am

Thank you!
:)

Rahul Rajat Singh · Accepted Answer · 2012-12-18T22:22:00

Here is the working SQl for your requirement

SQL

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

want to get the column name and its datatype so what's the query

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

want to get the column name and its datatype so what's the query

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us