13,402,670 members (41,036 online)
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 23:03pm
Updated 20-Dec-12 19: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
Member 9644686 19-Dec-12 4:34am

Thanks a lot...
Menon Santosh 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!
:)
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

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

Top Experts
Last 24hrsThis month
 OriginalGriff 265 Richard MacCutchan 110 F-ES Sitecore 80 John Simmons / outlaw programmer 70 phil.o 60
 OriginalGriff 5,344 Maciej Los 2,800 Richard MacCutchan 1,868 CPallini 1,690 RickZeeland 1,099