15,965,934 members
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)

and also for decimal it gave as decimal instead of decimal(18,10),,,,....
Posted
Updated 20-Dec-12 18:07pm
v2

## Solution 1

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

Member 9644686 19-Dec-12 4:34am
Thanks a lot...
Menon Santosh 20-Dec-12 8:03am
+5

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

Member 9644686 19-Dec-12 4:34am
Thanks a lot...
Aarti Meswania 19-Dec-12 4:52am
welcome! :)

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

## Solution 3

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```

Member 9644686 19-Dec-12 4:34am
Thanks a lot...
Menon Santosh 20-Dec-12 8:04am
+5