13,552,737 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 18-Dec-12 22:03pm
Updated 20-Dec-12 18:07pm
v2

## Solution 1

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

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
User-9606524 19-Dec-12 4:34am

Thanks a lot...
menonsantosh 20-Dec-12 8:03am

+5

## Solution 2

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!
:)
User-9606524 19-Dec-12 4:34am

Thanks a lot...
aarti-meswania 19-Dec-12 4:52am

welcome! :)

do u have give us stars(vote answer)? :)
menonsantosh 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

```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```
User-9606524 19-Dec-12 4:34am

Thanks a lot...
menonsantosh 20-Dec-12 8:04am

+5

Top Experts
Last 24hrsThis month
 Maciej Los 260 Richard Deeming 190 OriginalGriff 189 ppolymorphe 145 RickZeeland 80
 OriginalGriff 5,219 ppolymorphe 2,372 Richard MacCutchan 2,185 Wendelius 1,890 Maciej Los 1,775