Click here to Skip to main content
11,480,701 members (47,371 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL-Server
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 23:03pm
Edited 20-Dec-12 19:07pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

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... Smile | :)

Regards,
Vamsi
  Permalink  
Comments
Member 9644686 at 19-Dec-12 4:34am
   
Thanks a lot...
Menon Santosh at 20-Dec-12 8:03am
   
+5
Rate this: bad
good
Please Sign up or sign in to vote.

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!
Smile | :)
  Permalink  
Comments
Member 9644686 at 19-Dec-12 4:34am
   
Thanks a lot...
Aarti Meswania at 19-Dec-12 4:52am
   
welcome! :)
Glad to help you! :)

do u have give us stars(vote answer)? :)
Menon Santosh at 20-Dec-12 8:03am
   
+5
Aarti Meswania at 20-Dec-12 8:12am
   
Thank you!
:)
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Comments
Member 9644686 at 19-Dec-12 4:34am
   
Thanks a lot...
Menon Santosh at 20-Dec-12 8:04am
   
+5

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 294
1 OriginalGriff 145
2 CHill60 130
3 CPallini 125
4 Richard MacCutchan 75
0 Sergey Alexandrovich Kryukov 8,184
1 OriginalGriff 7,586
2 Sascha Lefèvre 3,114
3 Maciej Los 2,491
4 Richard Deeming 2,335


Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 21 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100